如下语句:
SELECT * FROM Db_Temp
WHERE (Db_Temp.D='D0') AND
(Db_Temp.a in (SELECT Db_Temp.b FROM Db_Temp WHERE Db_Temp.c='c0'))
貌似从语句上看针对每行的Db_Temp.a都要遍历一下Db_Temp,效率很不高。要怎么写比较好呢。
SELECT * FROM Db_Temp
WHERE (Db_Temp.D='D0') AND
(Db_Temp.a in (SELECT Db_Temp.b FROM Db_Temp WHERE Db_Temp.c='c0'))
貌似从语句上看针对每行的Db_Temp.a都要遍历一下Db_Temp,效率很不高。要怎么写比较好呢。
SELECT * FROM Db_Temp M
WHERE M.D='D0'
AND M.a =M.b
AND M.c='c0'
WHERE (Db_Temp.D='D0') AND
exists(select 1 from FROM Db_Temp t WHERE t.c='c0' and Db_Temp.a=t.b)
--如果自身连接,也要遍历的。
SELECT t1.* FROM Db_Temp t1 inner join Db_Temp t2 on t1.a=t2.b where t1.D='DO' and t2.c='c0'
a1 c0 D0
a2 c0 D0
a1 c0 D0
a1 c0 D0
a2 c0 D0
需要把1、2行找出
目前用的语句是:
SELECT * FROM Db_Temp
WHERE (Db_Temp.D='D0') AND
(Db_Temp.a in (SELECT Db_Temp.b FROM Db_Temp WHERE Db_Temp.c='c0'))
感觉效率不高,有办法优化吗
a b c d
a1 c0 D0
a2 c0 D0
a1 c0 D0
a1 c0 D0
a2 c0 D0
a b c D
a1 c0 D0
a2 c0 D0
a1 c0 D0
a1 c0 D0
a2 c0 D0