SELECT x.n2, x.count11, y.count22 FROM (SELECT n2, COUNT (*) count11 FROM a WHERE n1 = 11 GROUP BY n1, n2) x, (SELECT n2, COUNT (*) count22 FROM a WHERE n1 = 22 GROUP BY n2) y WHERE x.n2 = y.n2 AND x.count11 != y.count22
如果你的n1的值只有两种情况的话,可以这样: SELECT DISTINCT DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n1,tt2.n1) n1, DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n2,tt2.n2) n2, DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n3,tt2.n3) n3 FROM (SELECT t1.n1 n1,t1.n2 n2,COUNT(*) cnt1,MIN(t1.n3) n3 FROM yourtable t1 GROUP BY t1.n1,t1.n2 ) tt1, (SELECT t2.n1 n1,t2.n2 n2,COUNT(*) cnt2,MIN(t2.n3) n3 FROM yourtable t2 GROUP BY t2.n1,t2.n2 ) tt2 WHERE (tt1.n2 = tt2.n2 AND tt1.n1 <> tt2.n1 AND tt1.cnt1 <> tt2.cnt2) UNION ALL --前面是两种值都有但不相等的记录,后面是只有一种值的记录 SELECT MIN(n1) n1,n2,MIN(n3) n3 FROM (SELECT n1,n2,MIN(n3) n3 FROM yourtable GROUP BY n2,n1) tt3 GROUP BY n2 HAVING COUNT(*) = 1 如果你知道这两种情况的确切值的话,可以把union all前面的换成弱水兄的方法,这样可以简化一些
谢谢~~~~~~~但是这段代码我看不明: SELECT DISTINCT DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n1,tt2.n1) n1, DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n2,tt2.n2) n2, DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n3,tt2.n3) n3 FROM (SELECT t1.n1 n1,t1.n2 n2,COUNT(*) cnt1,MIN(t1.n3) n3 FROM yourtable t1 GROUP BY t1.n1,t1.n2 ) tt1, (SELECT t2.n1 n1,t2.n2 n2,COUNT(*) cnt2,MIN(t2.n3) n3 FROM yourtable t2 GROUP BY t2.n1,t2.n2 ) tt2 WHERE (tt1.n2 = tt2.n2 AND tt1.n1 <> tt2.n1 AND tt1.cnt1 <> tt2.cnt2)哪位能够解释一下谢谢 感谢cyberflying(雁南飞) 。
思想和弱水兄那段是一样的。 你可以不用管decode(),这只是为了选出n2相同时,相对应的n1多的记录。(SELECT t1.n1 n1,t1.n2 n2,COUNT(*) cnt1,MIN(t1.n3) n3 FROM yourtable t1 GROUP BY t1.n1,t1.n2 ) tt1, (SELECT t2.n1 n1,t2.n2 n2,COUNT(*) cnt2,MIN(t2.n3) n3 FROM yourtable t2 GROUP BY t2.n1,t2.n2 ) tt2 这两个分组是一样的,因为没有限定条件,所以会有重复,才使用了distinct可以简化一下:SELECT DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n1,tt2.n1) n1, DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n2,tt2.n2) n2, DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n3,tt2.n3) n3 FROM (SELECT t1.n1 n1,t1.n2 n2,COUNT(*) cnt1,MIN(t1.n3) n3 FROM yourtable t1 where t1.n1 = 11 GROUP BY t1.n1,t1.n2 ) tt1, (SELECT t2.n1 n1,t2.n2 n2,COUNT(*) cnt2,MIN(t2.n3) n3 FROM yourtable t2 where t1.n1 = 22 GROUP BY t2.n1,t2.n2 ) tt2 WHERE (tt1.n2 = tt2.n2 AND tt1.cnt1 <> tt2.cnt2)
其实如果你只是要获得count(*)不同的n2的值的话,直接用minus无法最简单,如下: SELECT n2, COUNT (*) count11 FROM a WHERE n1 = 11 GROUP BY n2 MINUS SELECT n2, COUNT (*) count22 FROM a WHERE n1 = 22 GROUP BY n2; 而且这种写法能获得所有n1=22不存在情况下n1=11的纪录
select * from (select * from test where n2 in (select n2 from (select n1,n2 from test group by n1,n2) group by n2 having count(*)=1) union select 11,n2 from test where n2 in (select n2 from (select n1,n2 from test group by n1,n2) group by n2 having count(*)>1));
感谢楼上的几位 MINUS 这个命令操作符,我试了一下,在oracle上有。^_^ 有sqlserver上没有 我很想知道在sybase上有没有??如果有的话是不是相近的命令。。
FROM (SELECT n2, COUNT (*) count11
FROM a
WHERE n1 = 11
GROUP BY n1,
n2) x,
(SELECT n2, COUNT (*) count22
FROM a
WHERE n1 = 22
GROUP BY n2) y
WHERE x.n2 = y.n2
AND x.count11 != y.count22
不过,还是有点问题没有解决。。哪就是
有一个11,a,但无22,a这样的数据没有选出来。。
如果在这一条语句里边能够一起体现那就更好啦
SELECT DISTINCT
DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n1,tt2.n1) n1,
DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n2,tt2.n2) n2,
DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n3,tt2.n3) n3
FROM (SELECT t1.n1 n1,t1.n2 n2,COUNT(*) cnt1,MIN(t1.n3) n3
FROM yourtable t1
GROUP BY t1.n1,t1.n2
) tt1,
(SELECT t2.n1 n1,t2.n2 n2,COUNT(*) cnt2,MIN(t2.n3) n3
FROM yourtable t2
GROUP BY t2.n1,t2.n2
) tt2
WHERE (tt1.n2 = tt2.n2
AND tt1.n1 <> tt2.n1
AND tt1.cnt1 <> tt2.cnt2)
UNION ALL --前面是两种值都有但不相等的记录,后面是只有一种值的记录
SELECT MIN(n1) n1,n2,MIN(n3) n3
FROM (SELECT n1,n2,MIN(n3) n3 FROM yourtable GROUP BY n2,n1) tt3
GROUP BY n2
HAVING COUNT(*) = 1
如果你知道这两种情况的确切值的话,可以把union all前面的换成弱水兄的方法,这样可以简化一些
SELECT DISTINCT
DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n1,tt2.n1) n1,
DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n2,tt2.n2) n2,
DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n3,tt2.n3) n3
FROM (SELECT t1.n1 n1,t1.n2 n2,COUNT(*) cnt1,MIN(t1.n3) n3
FROM yourtable t1
GROUP BY t1.n1,t1.n2
) tt1,
(SELECT t2.n1 n1,t2.n2 n2,COUNT(*) cnt2,MIN(t2.n3) n3
FROM yourtable t2
GROUP BY t2.n1,t2.n2
) tt2
WHERE (tt1.n2 = tt2.n2
AND tt1.n1 <> tt2.n1
AND tt1.cnt1 <> tt2.cnt2)哪位能够解释一下谢谢
感谢cyberflying(雁南飞) 。
你可以不用管decode(),这只是为了选出n2相同时,相对应的n1多的记录。(SELECT t1.n1 n1,t1.n2 n2,COUNT(*) cnt1,MIN(t1.n3) n3
FROM yourtable t1
GROUP BY t1.n1,t1.n2
) tt1,
(SELECT t2.n1 n1,t2.n2 n2,COUNT(*) cnt2,MIN(t2.n3) n3
FROM yourtable t2
GROUP BY t2.n1,t2.n2
) tt2
这两个分组是一样的,因为没有限定条件,所以会有重复,才使用了distinct可以简化一下:SELECT DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n1,tt2.n1) n1,
DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n2,tt2.n2) n2,
DECODE(SIGN(tt1.cnt1 - tt2.cnt2),1,tt1.n3,tt2.n3) n3
FROM (SELECT t1.n1 n1,t1.n2 n2,COUNT(*) cnt1,MIN(t1.n3) n3
FROM yourtable t1
where t1.n1 = 11
GROUP BY t1.n1,t1.n2
) tt1,
(SELECT t2.n1 n1,t2.n2 n2,COUNT(*) cnt2,MIN(t2.n3) n3
FROM yourtable t2
where t1.n1 = 22
GROUP BY t2.n1,t2.n2
) tt2
WHERE (tt1.n2 = tt2.n2
AND tt1.cnt1 <> tt2.cnt2)
SELECT n2, COUNT (*) count11
FROM a
WHERE n1 = 11
GROUP BY n2
MINUS
SELECT n2, COUNT (*) count22
FROM a
WHERE n1 = 22
GROUP BY n2;
而且这种写法能获得所有n1=22不存在情况下n1=11的纪录
group by n2 having count(*)=1) union select 11,n2 from test where n2 in (select n2 from (select n1,n2 from test group by n1,n2)
group by n2 having count(*)>1));
MINUS
这个命令操作符,我试了一下,在oracle上有。^_^
有sqlserver上没有
我很想知道在sybase上有没有??如果有的话是不是相近的命令。。