SystemTopicTable1
TTID1 10,11,12
SystemTopicTable2
TTID2 21,22,23
SystemTopicTable3
TTID3 31,32,33
UserTopicTable
UTTID 10,11,12,13,21,22,23,24,25,31,32,33,34,35,36
任务是找出
如果UserTopicTable的ID在前面三个表中不存在,就返回这些ID.即得到的结果是
13,24,25,34,35,36我的方法是得到UserTopicTable表中的每一个ID,然后再查找上面三个表,可惜速度非常慢.请问最优的sql查询算法该怎么写呢?
TTID1 10,11,12
SystemTopicTable2
TTID2 21,22,23
SystemTopicTable3
TTID3 31,32,33
UserTopicTable
UTTID 10,11,12,13,21,22,23,24,25,31,32,33,34,35,36
任务是找出
如果UserTopicTable的ID在前面三个表中不存在,就返回这些ID.即得到的结果是
13,24,25,34,35,36我的方法是得到UserTopicTable表中的每一个ID,然后再查找上面三个表,可惜速度非常慢.请问最优的sql查询算法该怎么写呢?
select * from UserTopicTable
where UTTID not in(
select TTID1 from SystemTopicTable1
union all
select TTID2 from SystemTopicTable2
union all
select TTID3 from SystemTopicTable3)
另外, 各表的uttid列上建立索引
(
select * from
(
select * from usertopictable where uttid not in (select TTID1 from SystemTopicTable1)
) t1
where uttid not in (select TTID2 from SystemTopicTable2)
) t2
where uttid not in (select TTID3 from SystemTopicTable3)
他的方法效率最高