select distinct 姓名 from tb where 姓名 not in (select 姓名 from tb t where a.姓名=b.姓名 exists(select 1 from tb where 分数<=80))
select 姓名 from tb group by 姓名 having (min(分数)>80)
select * from tb where 姓名 not in (select 姓名 from tb where 分数 <= 80)
select * from tb where 姓名 not in (select distinct 姓名 from tb where 分数 <= 80)
select 姓名 from tb group by 姓名 having (min(分数)>80)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-11-04 21:19:27 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([姓名] varchar(1),[课程] varchar(4),[分数] int) insert [tb] select 'a','语文',85 union all select 'a','数学',86 union all select 'a','英语',87 union all select 'b','语文',50 union all select 'b','数学',60 union all select 'b','英语',90 union all select 'c','语文',57 union all select 'c','数学',68 union all select 'c','英语',99 --------------开始查询-------------------------- select 姓名 from tb t where 分数 in (select 分数 from tb where [分数]>=80) group by 姓名 having count(姓名)=(select distinct count(1) from tb group by 姓名)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-11-04 21:19:27 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([姓名] varchar(1),[课程] varchar(4),[分数] int) insert [tb] select 'a','语文',85 union all select 'a','数学',86 union all select 'a','英语',87 union all select 'b','语文',50 union all select 'b','数学',60 union all select 'b','英语',90 union all select 'c','语文',57 union all select 'c','数学',68 union all select 'c','英语',99 --------------开始查询-------------------------- select distinct 姓名 from tb t where not exists(select 1 from tb where 分数 <= 80 and 姓名=t.姓名) ----------------结果---------------------------- /* 姓名 ---- a(1 行受影响) */
from tb
where 姓名 not in
(select 姓名 from tb t
where a.姓名=b.姓名
exists(select 1 from tb where 分数<=80))
from tb
group by 姓名
having (min(分数)>80)
select * from tb where 姓名 not in
(select 姓名 from tb where 分数 <= 80)
(select distinct 姓名 from tb where 分数 <= 80)
from tb
group by 姓名
having (min(分数)>80)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-04 21:19:27
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(1),[课程] varchar(4),[分数] int)
insert [tb]
select 'a','语文',85 union all
select 'a','数学',86 union all
select 'a','英语',87 union all
select 'b','语文',50 union all
select 'b','数学',60 union all
select 'b','英语',90 union all
select 'c','语文',57 union all
select 'c','数学',68 union all
select 'c','英语',99
--------------开始查询--------------------------
select
姓名
from
tb t
where
分数 in (select 分数 from tb where [分数]>=80)
group by
姓名
having count(姓名)=(select distinct count(1) from tb group by 姓名)
----------------结果----------------------------
/* 姓名
----
a(1 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-04 21:19:27
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(1),[课程] varchar(4),[分数] int)
insert [tb]
select 'a','语文',85 union all
select 'a','数学',86 union all
select 'a','英语',87 union all
select 'b','语文',50 union all
select 'b','数学',60 union all
select 'b','英语',90 union all
select 'c','语文',57 union all
select 'c','数学',68 union all
select 'c','英语',99
--------------开始查询--------------------------
select
distinct 姓名
from
tb t
where
not exists(select 1 from tb where 分数 <= 80 and 姓名=t.姓名)
----------------结果----------------------------
/* 姓名
----
a(1 行受影响)
*/