若有表worker
id age ……
1 18
2 19
3 19
4 20
5 18
6 22
7 26
想显示age=19,18的信息如下:
1 18……
5 18
2 19
3 19select *
from worker
group by age
having count(*)=2
怎么不行啊
应该怎么写?
id age ……
1 18
2 19
3 19
4 20
5 18
6 22
7 26
想显示age=19,18的信息如下:
1 18……
5 18
2 19
3 19select *
from worker
group by age
having count(*)=2
怎么不行啊
应该怎么写?
select * from worker where age in (18,19)
where age in(
select age
from worker
group by age
having count(*)=2 )
from worker
group by age
having count(*)>=2
insert @ select 18
insert @ select 19
insert @ select 19
insert @ select 20
insert @ select 18
insert @ select 22
insert @ select 26 select * from @
where age in(
select age
from @
group by age
having count(*)=2 )
order by ageid age
----------- -----------
1 18
5 18
2 19
3 19(4 行受影响)
from worker t
where (select count(*) from worker where age=t.age)>=2
insert @ select 18
insert @ select 19
insert @ select 19
insert @ select 20
insert @ select 18
insert @ select 22
insert @ select 26 select * from @ a where 2=(select count(1) from @ where age=a.age group by age) order by ageid age
----------- -----------
1 18
5 18
2 19
3 19(4 行受影响)
(select age from worker group by age having count(*) > 1)
insert into tb values(1 ,18)
insert into tb values(2 ,19)
insert into tb values(3 ,19)
insert into tb values(4 ,20)
insert into tb values(5 ,18)
insert into tb values(6 ,22)
insert into tb values(7 ,26)
goselect * from tb where age in
(select age from tb group by age having count(*) > 1)drop table tb /*
id age
----------- -----------
1 18
2 19
3 19
5 18(所影响的行数为 4 行)*/