关于SQL性能优化问题

[jeffersyan]

现在有下面一段SQL,各位DX有什么好的优化方法,谢谢,

SELECT KOKYAKU_NO,TAKEI_KOJIN_ID FROM T_KOKYAKU_NAYOSE WHERE TAKEI_KOJIN_ID IN(SELECT TAKEI_KOJIN_ID FROM T_KOKYAKU_NAYOSE WHERE KIGOU IS NULL AND TAKEI_KOJIN_ID IS NOT NULL AND KOJIN_CD ='810105' GROUP BY TAKEI_KOJIN_ID HAVING COUNT(*)>1) AND KIGOU IS NULL AND KOJIN_CD ='810105' ORDER BY UPDATE_DAY DESC,KOKYAKU_NO DESC

已建立了TAKEI_KOJIN_ID ,KIGOU,KOJIN_CD 索引,不可以设置主键,谢谢各为了,

[3tzjq]

都是在一张表里就不必 In(...)了!
KOJIN_CD ='810105' 放前面会快些!
TAKEI_KOJIN_ID 是主键,那么也应提前(放KOJIN_CD ='810105' 后面)
Count(这里最好是主键)!

SELECT KOKYAKU_NO,TAKEI_KOJIN_ID FROM T_KOKYAKU_NAYOSE WHERE KOJIN_CD ='810105'AND TAKEI_KOJIN_ID IS NOT NULL AND KIGOU IS NULL GROUP BY TAKEI_KOJIN_ID HAVING COUNT(TAKEI_KOJIN_ID)>1 ORDER BY UPDATE_DAY DESC,KOKYAKU_NO DESC

[hamadou]

SELECT KOKYAKU_NO,TAKEI_KOJIN_ID FROM T_KOKYAKU_NAYOSE WHERE TAKEI_KOJIN_ID IN(SELECT TAKEI_KOJIN_ID FROM T_KOKYAKU_NAYOSE WHERE KIGOU IS NULL AND TAKEI_KOJIN_ID IS NOT NULL AND KOJIN_CD ='810105' GROUP BY TAKEI_KOJIN_ID HAVING COUNT(*)>1) ORDER BY UPDATE_DAY DESC,KOKYAKU_NO DESC

[jeffersyan]

主查询里面的KIGOU IS NULL AND KOJIN_CD ='810105' 不能删掉,虽然子查询里有了限定,但是主查询里删掉的话,结果也是不对的

[jeffersyan]

谢谢DX的回答,不过因为检索的结果里面,并没有UPDATE_DAY,所以无法使用ORDER BY.所以觉的还是要用IN语句.

[kjq_vb]

你的T_KOKYAKU_NAYOSE表中有UPDATE_DAY字段就可以用order by 了,不需要在检索的结果里有UPDATE_DAY字段

[feiyun0112]

SELECT a.KOKYAKU_NO,a.TAKEI_KOJIN_ID FROM T_KOKYAKU_NAYOSE a
inner join (
SELECT TAKEI_KOJIN_ID FROM T_KOKYAKU_NAYOSE WHERE KOJIN_CD ='810105' and KIGOU IS NULL AND TAKEI_KOJIN_ID IS NOT NULL GROUP BY TAKEI_KOJIN_ID HAVING COUNT(*)>1
) b
on a.TAKEI_KOJIN_ID =b.TAKEI_KOJIN_ID
WHERE a.KOJIN_CD ='810105' and a.KIGOU IS NULL ORDER BY a.UPDATE_DAY DESC,a.KOKYAKU_NO DESC

[jeffersyan]

谢谢大家的帮助,但是因为有了GROUP BY TAKEI_KOJIN_ID,所以无法使用ORDER BY UPDATE_DAY DESC了,否则会语法错误的.