SQL 中标记系统的布尔表达式
为标记系统提供此 SQL 表:
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE INDEX tags_name_idx ON tags(name);
CREATE TABLE tagged_items (
tag_id INT,
item_id INT
);
CREATE INDEX tagged_items_tag_id_idx ON tagged_items(tag_id);
CREATE INDEX tagged_items_item_id_idx ON tagged_items(item_id);
CREATE TABLE items (
id SERIAL PRIMARY KEY,
content VARCHAR(255)
);
SQL 中用户的布尔表达式查询“tag1 AND tag2”是:
SELECT items.* FROM items
INNER JOIN tagged_items AS i1 ON (items.id = i1.item_id) INNER JOIN tags AS t1 ON (i1.tag_id = t1.id)
INNER JOIN tagged_items AS i2 ON (items.id = i2.item_id) INNER JOIN tags AS t2 ON (i2.tag_id = t2.id)
WHERE t1.name = 'tag1' AND t2.name = 'tag2';
如何使用布尔表达式翻译其他查询,例如“tag1 OR tag2 AND” tag3”...或更复杂的查询,例如 SQL 的“tag1 AND (tag2 OR tag3) AND NOT tag4 OR tag5”?
Having this SQL tables for a tagging system:
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE INDEX tags_name_idx ON tags(name);
CREATE TABLE tagged_items (
tag_id INT,
item_id INT
);
CREATE INDEX tagged_items_tag_id_idx ON tagged_items(tag_id);
CREATE INDEX tagged_items_item_id_idx ON tagged_items(item_id);
CREATE TABLE items (
id SERIAL PRIMARY KEY,
content VARCHAR(255)
);
The user's boolean expression query "tag1 AND tag2" in SQL is:
SELECT items.* FROM items
INNER JOIN tagged_items AS i1 ON (items.id = i1.item_id) INNER JOIN tags AS t1 ON (i1.tag_id = t1.id)
INNER JOIN tagged_items AS i2 ON (items.id = i2.item_id) INNER JOIN tags AS t2 ON (i2.tag_id = t2.id)
WHERE t1.name = 'tag1' AND t2.name = 'tag2';
How do you translate other queries with boolean expressions, such as "tag1 OR tag2 AND tag3" ...or even more complex queries such as "tag1 AND (tag2 OR tag3) AND NOT tag4 OR tag5" to SQL?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
假设数据->项目、单词 ->名称和 tagged_item -> tagged_items。
这适用于“tag1 AND (tag2 OR tag3) AND NOT tag4 OR tag5”。我相信你能弄清楚剩下的事情。
编辑:
如果您想避免子查询,您可以这样做:
我不确定您为什么要这样做,因为额外的左连接可能会导致运行速度变慢。
Assuming that data -> items, word -> name and tagged_item -> tagged_items.
This is for "tag1 AND (tag2 OR tag3) AND NOT tag4 OR tag5". I'm sure you can figure out the rest.
Edit:
If you want to avoid subqueries, you could do this:
I'm not sure why you'd want to do it though, as the additional left joins will likely result in a slower run.