我有一表,内容如下 字段1 字段2
1 A
1 B
2 B
2 C
1 D
2 D
1 E 现在写一SQL语句,以字段1为主键,只要在1,和2中都出现了的字段2,就显示,最后结果为
字段1 字段2
1 B
2 B
1 D
2 D
我这样写的SQL
select 字段1,字段2
from tablename
group by 字段1,字段2
having COUNT(字段2)>2
可结果怎么不对那?那位指点一下
1 A
1 B
2 B
2 C
1 D
2 D
1 E 现在写一SQL语句,以字段1为主键,只要在1,和2中都出现了的字段2,就显示,最后结果为
字段1 字段2
1 B
2 B
1 D
2 D
我这样写的SQL
select 字段1,字段2
from tablename
group by 字段1,字段2
having COUNT(字段2)>2
可结果怎么不对那?那位指点一下
不是>2
要想得到樓主的結果
就是having COUNT(字段2)>1
或這having COUNT(字段2)=2
from tablename
group by 字段1,字段2,count(字段2) as num
having num>2
按你的写法group by 字段1,字段2 having count(col2)>1 只能取出重复的行而不是你想要的结果按你的意思应该用这样的语句
SQL> select * from justtest;
COL1 COL2
---------- ----------
1 A
1 B
2 B
2 C
1 D
2 D
1 E
7 rows selected
SQL> select a.*
2 from justtest a, justtest b
3 where a.col2 = b.col2
4 and ((a.col1 = '1' and b.col1 = '2')
5 or (a.col1 = 2 and b.col1 = '1'));
COL1 COL2
---------- ----------
2 B
1 B
2 D
1 D
FROM justtest
WHERE col2 IN (SELECT col2 FROM justtest
GROUP BY col2
HAVING BY COUNT(1) = 2)
select *
from temp_learn a
where exists (select 1
from temp_learn b
where a.column2 = b.column2
group by b.column2
having count(1) = 2);