SQL> select * from employee; ID SALARY NAME ---------- ---------- ---------- 1 1000 a 1 2000 b 1 2000 c 1 3000 d 2 1000 e 2 1000 f 2 3000 g7 rows selected. SQL> select * 2 from 3 ( 4 select employee.*, 5 count(*) over(partition by id,salary) cnt 6 from employee 7 ) 8 where cnt > 1; ID SALARY NAME CNT ---------- ---------- ---------- ---------- 1 2000 b 2 1 2000 c 2 2 1000 e 2 2 1000 f 2
一,二楼改改也是对的select id,salary,count(*) 人数 from employee group by id,salary having count(*) > 1;通常有个原则是,能用sql的不要用pl/sql; 能用简单语句搞定的不要用函数;
select id,salary,count(*) 人数 from employee group by id,salary having count(*) > 1;
select * from ( select employee.*, count(*) over(partition by id,salary) cnt from employee ) where cnt > 1; 呵呵,有用
select id from employee where id<>32 group by id having count(1)>(select count(1) from employee where id=32)
SQL> select * from employee; ID SALARY NAME ---------- ---------- ---------- 1 1000 a 1 2000 b 1 2000 c 1 3000 d 2 1000 e 2 1000 f 2 3000 g 3 2000 w8 rows selected. SQL> select id 2 from 3 ( 4 select employee.*,count(*) cnt 5 from employee 6 group by id 7 ) 8 where cnt > (select count(*) from employee where id=3); ID ---------- 1 2 SQL> select id 2 from 3 ( 4 select employee.*,count(*) cnt 5 from employee 6 group by id 7 ) 8 where cnt > (select count(*) from employee where id=2); ID ---------- 1
from employee
group by id,salary;
from employee t1
group by t1.id, t1.salary
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
---------- ---------- ----------
1 1000 a
1 2000 b
1 2000 c
1 3000 d
2 1000 e
2 1000 f
2 3000 g7 rows selected.
SQL> select *
2 from
3 (
4 select employee.*,
5 count(*) over(partition by id,salary) cnt
6 from employee
7 )
8 where cnt > 1; ID SALARY NAME CNT
---------- ---------- ---------- ----------
1 2000 b 2
1 2000 c 2
2 1000 e 2
2 1000 f 2
from employee
group by id,salary
having count(*) > 1;通常有个原则是,能用sql的不要用pl/sql;
能用简单语句搞定的不要用函数;
from employee
group by id,salary
having count(*) > 1;
select *
from
(
select employee.*,
count(*) over(partition by id,salary) cnt
from employee
)
where cnt > 1;
呵呵,有用
from employee
where id<>32
group by id
having count(1)>(select count(1) from employee where id=32)
---------- ---------- ----------
1 1000 a
1 2000 b
1 2000 c
1 3000 d
2 1000 e
2 1000 f
2 3000 g
3 2000 w8 rows selected.
SQL> select id
2 from
3 (
4 select employee.*,count(*) cnt
5 from employee
6 group by id
7 )
8 where cnt > (select count(*) from employee where id=3); ID
----------
1
2
SQL> select id
2 from
3 (
4 select employee.*,count(*) cnt
5 from employee
6 group by id
7 )
8 where cnt > (select count(*) from employee where id=2); ID
----------
1