---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-03-11 00:39:18 -- Verstion: -- 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]([name] varchar(1),[type] int,[score] int) insert [tb] select 'a',1,90 union all select 'a',2,91 union all select 'a',3,92 union all select 'a',4,94 union all select 'a',5,80 union all select 'a',6,67 union all select 'a',7,77 union all select 'b',1,77 union all select 'b',2,99 union all select 'b',3,66 union all select 'b',4,88 union all select 'b',5,80 union all select 'b',6,87 union all select 'b',7,79 --------------开始查询-------------------------- select * from tb t where name in ( select name from (select name,count(1) as num from tb where score>=90 group by name having count(1)>=3)t) ----------------结果---------------------------- /* name type score ---- ----------- ----------- a 1 90 a 2 91 a 3 92 a 4 94 a 5 80 a 6 67 a 7 77(7 行受影响)*/
select a.* from student a inner join (select name from student where score>90 group by name having count(*)>=3) b on a.name = b.name
select * from student表 where name in ( select name from student表 where score>90 group by name having count(name)>=3 )
select * from student where name in (select name from student where score>90 group by name having count(score)>3);
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-11 00:39:18
-- Verstion:
-- 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]([name] varchar(1),[type] int,[score] int)
insert [tb]
select 'a',1,90 union all
select 'a',2,91 union all
select 'a',3,92 union all
select 'a',4,94 union all
select 'a',5,80 union all
select 'a',6,67 union all
select 'a',7,77 union all
select 'b',1,77 union all
select 'b',2,99 union all
select 'b',3,66 union all
select 'b',4,88 union all
select 'b',5,80 union all
select 'b',6,87 union all
select 'b',7,79
--------------开始查询--------------------------
select
*
from
tb t
where
name in
(
select name from
(select name,count(1) as num from tb where score>=90 group by name having count(1)>=3)t)
----------------结果----------------------------
/* name type score
---- ----------- -----------
a 1 90
a 2 91
a 3 92
a 4 94
a 5 80
a 6 67
a 7 77(7 行受影响)*/
inner join
(select name from student where score>90 group by name having count(*)>=3) b
on a.name = b.name
select name from student表 where score>90 group by name having count(name)>=3
)