关于sqlite结果集交集查询

[DerryZhang]

大家好，一起讨论个sql查询问题？
表结构：
-------------------------------------
id integer 主键
msg varchar(100) 内容
parent_id integer 父节点id
level integer 级别
-------------------------------------
数据：
父节点1
子节点1
子节点2
父节点2
子节点1
子节点3
父节点3
子节点5
子节点6

需求：查找拥有相同子节点数据的交集。例如：查找节点id为1,2（即：“父节点1”、“父节点2”）的相同子节点交集=“子节点1”，查找节点id为1,2,3（即：“父节点1”、“父节点2”、“父节点3”）的相同子节点交集为空集。
那么这个sql语句应该怎么写？谢谢！

[ACMAIN_CHM]

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

[DerryZhang]

OK，创建表：

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!

[ACMAIN_CHM]

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>

[DerryZhang]

使用该查询语句查询还是有问题。SQL也不是万能的，我考虑采用应用层处理了。 多谢！