select distinct name from table a where not exists(select * from table where name=a.name and fenshu<=80)
select distinct name from table where name not in (select distinct name from table where fenshu <= 80)
--建立测试环境 Create Table TEST (name Nvarchar(10), kecheng Nvarchar(10), fenshu Int) --插入数据 Insert TEST Values(N'张三', '语文', 81) Insert TEST Values(N'张三', '数学', 75) Insert TEST Values(N'李四', '语文', 76) Insert TEST Values(N'李四', '数学', 90) Insert TEST Values(N'王五', '语文', 81) Insert TEST Values(N'王五', '数学', 100) Insert TEST Values(N'王五', '英语', 90) --测试 Select Distinct name from TEST A Where Not Exists(Select * from TEST Where Name=A.Name And fenshu<=80) --删除测试环境 Drop Table TEST --结果 /* Name 王五 */
select distinct name from table where name not in (select distinct name from table where fenshu<=80)
sekect name from test group by name having min(fenshu) > 80
where name not in
(select distinct name from table where fenshu <= 80)
Create Table TEST
(name Nvarchar(10),
kecheng Nvarchar(10),
fenshu Int)
--插入数据
Insert TEST Values(N'张三', '语文', 81)
Insert TEST Values(N'张三', '数学', 75)
Insert TEST Values(N'李四', '语文', 76)
Insert TEST Values(N'李四', '数学', 90)
Insert TEST Values(N'王五', '语文', 81)
Insert TEST Values(N'王五', '数学', 100)
Insert TEST Values(N'王五', '英语', 90)
--测试
Select Distinct name from TEST A Where Not Exists(Select * from TEST Where Name=A.Name And fenshu<=80)
--删除测试环境
Drop Table TEST
--结果
/*
Name
王五
*/
from test
group by name
having min(fenshu) > 80