如何在 sqlite 中构建这个查询？

发布于 2024-09-14 03:27:04 字数 851 浏览 5 评论 0原文

我有带有示例结构和数据的 sqlite3 数据库：

CREATE TABLE person(id INTEGER PRIMARY KEY NOT NULL, name STRING NOT NULL);
INSERT INTO "person" VALUES(1,'Jack');
INSERT INTO "person" VALUES(2,'Daniel');
INSERT INTO "person" VALUES(3,'Sam');
INSERT INTO "person" VALUES(4,'T`lc');
CREATE TABLE vote(person_id INTEGER NOT NULL, article_id NUMBER NOT NULL, FOREIGN KEY(person_id) REFERENCES person(id));
INSERT INTO "vote" VALUES(1,43256);
INSERT INTO "vote" VALUES(1,43436);
INSERT INTO "vote" VALUES(1,67388);
INSERT INTO "vote" VALUES(1,43678);
INSERT INTO "vote" VALUES(2,678);
INSERT INTO "vote" VALUES(2,6788);
INSERT INTO "vote" VALUES(2,67388);
INSERT INTO "vote" VALUES(4,67388);
INSERT INTO "vote" VALUES(4,67658);

现在我想（在一个选择查询中）找到所有人女巫：

根本不投票
不要投票给文章 67388

我不知道如何做到这一点：/

帮助：' （

原文

I have sqlite3 database with example structure and data:

CREATE TABLE person(id INTEGER PRIMARY KEY NOT NULL, name STRING NOT NULL);
INSERT INTO "person" VALUES(1,'Jack');
INSERT INTO "person" VALUES(2,'Daniel');
INSERT INTO "person" VALUES(3,'Sam');
INSERT INTO "person" VALUES(4,'T`lc');
CREATE TABLE vote(person_id INTEGER NOT NULL, article_id NUMBER NOT NULL, FOREIGN KEY(person_id) REFERENCES person(id));
INSERT INTO "vote" VALUES(1,43256);
INSERT INTO "vote" VALUES(1,43436);
INSERT INTO "vote" VALUES(1,67388);
INSERT INTO "vote" VALUES(1,43678);
INSERT INTO "vote" VALUES(2,678);
INSERT INTO "vote" VALUES(2,6788);
INSERT INTO "vote" VALUES(2,67388);
INSERT INTO "vote" VALUES(4,67388);
INSERT INTO "vote" VALUES(4,67658);

Now I would like to (in one select query) find all persons witch:

Do not vote at all
Do not vote for article 67388

I have no ideas how to do this :/

help :'(

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冰雪梦之恋 2024-09-21 03:27:04

使用 OR：

SELECT p.*
   FROM PERSON p
 WHERE NOT EXISTS(SELECT NULL
                                FROM VOTE v
                               WHERE v.person_id = p.id) -- no votes at all
       OR NOT EXISTS(SELECT NULL
                                 FROM VOTE v
                                WHERE v.person_id = p.id
                                     AND v.article_id = 67388)

使用 UNION

SELECT p.*
  FROM PERSON p
 WHERE NOT EXISTS(SELECT NULL
                                FROM VOTE v
                               WHERE v.person_id = p.id)
UNION
SELECT p.*
  FROM PERSON p
 WHERE NOT EXISTS(SELECT NULL
                                FROM VOTE v
                               WHERE v.person_id = p.id
                                    AND v.article_id = 67388)

UNION 比 UNION ALL 慢，因为它会删除重复项。如果您想要重复，只需在“UNION”后面添加“ALL”关键字即可。

Using OR:

SELECT p.*
   FROM PERSON p
 WHERE NOT EXISTS(SELECT NULL
                                FROM VOTE v
                               WHERE v.person_id = p.id) -- no votes at all
       OR NOT EXISTS(SELECT NULL
                                 FROM VOTE v
                                WHERE v.person_id = p.id
                                     AND v.article_id = 67388)

Using UNION

SELECT p.*
  FROM PERSON p
 WHERE NOT EXISTS(SELECT NULL
                                FROM VOTE v
                               WHERE v.person_id = p.id)
UNION
SELECT p.*
  FROM PERSON p
 WHERE NOT EXISTS(SELECT NULL
                                FROM VOTE v
                               WHERE v.person_id = p.id
                                    AND v.article_id = 67388)

UNION is slower than UNION ALL, because it removes duplicates. If you want duplicates, just add the "ALL" keyword after "UNION".

回复收藏 0 原文