SELECT mc, 0 hj
FROM sbtz_lb
WHERE lx = 'zdh'
AND lb IN ('zz', 'cz', 'gy')
AND mc NOT IN (
SELECT b.mc
FROM sbtz_sb a,
(SELECT ID, mc
FROM sbtz_lb
WHERE lx = 'zdh'
AND lb IN ('zz', 'cz', 'gy')) b,
(SELECT ID, mc
FROM sbtz_cz
WHERE dydj = '500kV'
AND lx IN ('hdc', 'sdc')) c
WHERE a.czid = c.ID AND b.ID = a.lbid
GROUP BY b.mc)
我尝试用下面的字句替换上面的sql
SELECT lb.MC FROM SBTZ_LB LB
WHERE lb.LX = 'zdh'
AND lb.LB IN ('zz', 'cz', 'gy')
AND NOT EXISTS (SELECT lb.mc
FROM SBTZ_SB SB, SBTZ_CZ CZ
WHERE SB.CZID = CZ.ID
AND LB.ID = SB.LBID
AND CZ.DYDJ = '500kV'
AND CZ.LX IN ('hdc', 'sdc')
GROUP BY LB.MC );
但是查询的数据不同,替换后的比原来的多几条数据
由于外层查询的分组条件和in里面的分组条件原因 不知道该如何替换才合适 这样做是为了性能求解
FROM sbtz_lb
WHERE lx = 'zdh'
AND lb IN ('zz', 'cz', 'gy')
AND mc NOT IN (
SELECT b.mc
FROM sbtz_sb a,
(SELECT ID, mc
FROM sbtz_lb
WHERE lx = 'zdh'
AND lb IN ('zz', 'cz', 'gy')) b,
(SELECT ID, mc
FROM sbtz_cz
WHERE dydj = '500kV'
AND lx IN ('hdc', 'sdc')) c
WHERE a.czid = c.ID AND b.ID = a.lbid
GROUP BY b.mc)
我尝试用下面的字句替换上面的sql
SELECT lb.MC FROM SBTZ_LB LB
WHERE lb.LX = 'zdh'
AND lb.LB IN ('zz', 'cz', 'gy')
AND NOT EXISTS (SELECT lb.mc
FROM SBTZ_SB SB, SBTZ_CZ CZ
WHERE SB.CZID = CZ.ID
AND LB.ID = SB.LBID
AND CZ.DYDJ = '500kV'
AND CZ.LX IN ('hdc', 'sdc')
GROUP BY LB.MC );
但是查询的数据不同,替换后的比原来的多几条数据
由于外层查询的分组条件和in里面的分组条件原因 不知道该如何替换才合适 这样做是为了性能求解
FROM SBTZ_LB
WHERE LX = 'zdh'
AND LB IN ('zz', 'cz', 'gy')
AND NOT EXISTS (SELECT 1
FROM SBTZ_SB A, SBTZ_LB B, SBTZ_CZ C
WHERE C.DYDJ = '500kV'
AND C.LX IN ('hdc', 'sdc')
AND A.CZID = C.ID
AND B.ID = A.LBID);
SELECT MC, 0 HJ
FROM SBTZ_LB b
WHERE LX = 'zdh'
AND LB IN ('zz', 'cz', 'gy')
AND NOT EXISTS (SELECT 1
FROM SBTZ_SB A, SBTZ_CZ C
WHERE C.DYDJ = '500kV'
AND C.LX IN ('hdc', 'sdc')
AND A.CZID = C.ID
AND B.ID = A.LBID);
这样不行的 我帖子中就是这样替换数据不对。
To All
请帮忙看的朋友注意下原始sql中的分组条件就是因为分组条件sbtz_lb。mc分组所以没办法在exists中关联mc这个条件,因为exists内 和外面的查询是同一个表sbtz_lb
换句话说就是带有group 条件时exists中如何关联
WHERE lb.LX = 'zdh'
AND lb.LB IN ('zz', 'cz', 'gy')
AND NOT EXISTS (SELECT lb2.mc
FROM SBTZ_SB SB, SBTZ_CZ CZ,SBTZ_LB LB2
WHERE SB.CZID = CZ.ID
AND LB2.ID = SB.LBID
AND CZ.DYDJ = '500kV'
AND CZ.LX IN ('hdc', 'sdc')
AND LB2.MC = LB.MC
GROUP BY LB2.MC );
原句用的是字段MC NOT IN,你写的是用字段ID去进行 NOT EXISTS。结果当然不一样
。我知道是mc not in 问题是现在同一个表sbtz_lb我在exists 内部怎么去跟mc关联,总不能再加一个sbtz_lb在内部吧? 不知道该怎么改才合适求解