我想按typeid分组获取每个类别中score>60的数据怎么获取。也就是每个typeID中scroe大于60的人员数据集?
如下表数据所示:name age typeID score
王某 22 2 40
孙某 23 1 60
黄某 18 2 70
张某 17 1 60
杜某 16 4 70
黄某 15 3 70
高某 14 1 60
宋某 13 2 70
如下表数据所示:name age typeID score
王某 22 2 40
孙某 23 1 60
黄某 18 2 70
张某 17 1 60
杜某 16 4 70
黄某 15 3 70
高某 14 1 60
宋某 13 2 70
from tb t
where not exists(select 1 from tb where typeid=t.typeid and score<=60)
if object_id('tempdb.dbo.#') is not null drop table #
create table #(name varchar(8), age int, typeID int, score int)
insert into #
select '王某', 22, 2, 40 union all
select '孙某', 23, 1, 60 union all
select '黄某', 18, 2, 70 union all
select '张某', 17, 1, 60 union all
select '杜某', 16, 4, 70 union all
select '黄某', 15, 3, 70 union all
select '高某', 14, 1, 60 union all
select '宋某', 13, 2, 70select * from # where score > 60/*
name age typeID score
-------- ----------- ----------- -----------
黄某 18 2 70
杜某 16 4 70
黄某 15 3 70
宋某 13 2 70
*/
select *
from tb t
where (select count(1) from tb where typeid=t.typeid and score>=t.score)<=10