2,209
社区成员




sqlite> select * from MessageInfo;
id|msg|parent_id|level
1|父节点1|0|1
2|父节点2|0|1
3|父节点3|0|1
4|子节点1|1|2
5|子节点2|1|2
6|子节点1|2|2
7|子节点3|2|2
8|子节点5|3|2
9|子节点6|3|2
sqlite> select *
...> from MessageInfo a
...> where exists (select 1 from MessageInfo where parent_id=1 and msg=a.msg)
...> and exists (select 1 from MessageInfo where parent_id=2 and msg=a.msg);
id|msg|parent_id|level
4|子节点1|1|2
6|子节点1|2|2
sqlite> select *
...> from MessageInfo a
...> where exists (select 1 from MessageInfo where parent_id=1 and msg=a.msg)
...> and exists (select 1 from MessageInfo where parent_id=2 and msg=a.msg)
...> and exists (select 1 from MessageInfo where parent_id=3 and msg=a.msg);
sqlite>
CREATE TABLE [MessageInfo] (id integer NOT NULL PRIMARY KEY AUTOINCREMENT,msg text NOT NULL,parent_id integer DEFAULT -1,level integer)
插入数据:
INSERT INTO MessageInfo VALUES (1, '父节点1', 0, 1)
INSERT INTO MessageInfo VALUES (2, '父节点2', 0, 1)
INSERT INTO MessageInfo VALUES (3, '父节点3', 0, 1)
INSERT INTO MessageInfo VALUES (4, '子节点1', 1, 2)
INSERT INTO MessageInfo VALUES (5, '子节点2', 1, 2)
INSERT INTO MessageInfo VALUES (6, '子节点1', 2, 2)
INSERT INTO MessageInfo VALUES (7, '子节点3', 2, 2)
INSERT INTO MessageInfo VALUES (8, '子节点5', 3, 2)
INSERT INTO MessageInfo VALUES (9, '子节点6', 3, 2)
需求:查找拥有相同子节点msg文本数据的交集。例如:查找节点id为1,2(即:“父节点1”、“父节点2”)的相同子节点交集=“子节点1”,查找节点id为1,2,3(即:“父节点1”、“父节点2”、“父节点3”)的相同子节点交集为空集。
尝试使用查询语句:
SELECT DISTINCT fi1.* FROM MessageInfo fi1, MessageInfo fi2 where fi1.id <> fi2.id and fi1.msg = fi2.msg and fi1.parent_id in(1,2)
此时结果是正确的:'子节点1'
然而:SELECT DISTINCT fi1.* FROM MessageInfo fi1, MessageInfo fi2 where fi1.id <> fi2.id and fi1.msg = fi2.msg and fi1.parent_id in(1,2,3)
结果与上述查询一样,预期应该为空。
使用的数据库:Sqlite 3
thanks a lot!