姓名 分数
A 50
A 60
A 70
B 73
B 58
B 90按姓名分组取出分数最大和第二大的数,取出结果为:姓名 分数
A 70
A 60
B 90
B 73 用一句SQL语句写出想要的结果,请问下怎么写?
A 50
A 60
A 70
B 73
B 58
B 90按姓名分组取出分数最大和第二大的数,取出结果为:姓名 分数
A 70
A 60
B 90
B 73 用一句SQL语句写出想要的结果,请问下怎么写?
(
姓名 varchar(20),
分数 int
)
insert into #EE select 'A',50
insert into #EE select 'A',60
insert into #EE select 'A',70
insert into #EE select 'B',73
insert into #EE select 'B',58
insert into #EE select 'B',90select * from #EE where 分数 not in
(
select 分数 from #EE E where not exists(select * from #EE where E.姓名=姓名 and E.分数>分数)
)
姓名 分数
-------------------- -----------
A 60
A 70
B 73
B 90
-- Author: flystone
-- Version:V1.001
-- Date:2009-11-07 23:22:24
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(name nvarchar(1),sp int)
Go
Insert into ta
select 'A',50 union all
select 'A',60 union all
select 'A',70 union all
select 'B',73 union all
select 'B',58 union all
select 'B',90
Go
--Start
select a.name,a.sp from ta a
join ta b
on a.name = b.name
group by a.name,a.sp
having count(case when a.sp <= b.sp then 1 else null end) < = 2 --可动态修改
--Result:
/*
name sp
---- -----------
A 60
A 70
B 73
B 90
*/
--End
*
FROM
TB AS T
WHERE
2>(SELECT COUNT(*) FROM TB WHERE 姓名=T.姓名 AND 分数>T.分数)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-07 23:25:08
-- 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),[分数] int)
insert [tb]
select 'A',50 union all
select 'A',60 union all
select 'A',70 union all
select 'B',73 union all
select 'B',58 union all
select 'B',90
--------------开始查询--------------------------
SELECT
*
FROM
TB AS T
WHERE
2>(SELECT COUNT(*) FROM TB WHERE 姓名=T.姓名 AND 分数>T.分数)
----------------结果----------------------------
/*姓名 分数
---- -----------
A 60
A 70
B 73
B 90(4 行受影响)
*/
if object_id('[TB]') is not null drop table [TB]
create table [TB]([姓名] varchar(1),[分数] int)
insert [TB]
select 'A',50 union all
select 'A',60 union all
select 'A',70 union all
select 'B',73 union all
select 'B',58 union all
select 'B',90select * from [TB] T
where (select count(分数) from TB where t.姓名=姓名 and t.分数>分数)in (1,2)/*
姓名 分数
---- -----------
A 60
A 70
B 73
B 90(所影响的行数为 4 行)*/drop table TB
create table #EE
(
x varchar(20),
y int
)
insert into #EE select 'A',50
insert into #EE select 'A',60
insert into #EE select 'A',70
insert into #EE select 'B',73
insert into #EE select 'B',58
insert into #EE select 'B',90
goselect * from #ee a
where exists(
select 1 from #ee b
where b.x=a.x
and (
b.y<a.y
and
not exists(
select 1 from #ee c
where c.x=a.x
and c.y>a.y
and c.y>b.y
)
or
b.y>a.y
and
not exists(
select 1 from #ee c
where c.x=a.x
and c.y<b.y
and c.y>a.y
)
and not exists(select 1 from #ee where x=a.x and y>b.y)
)
)
godrop table #ee
go
SELECT
*
FROM
TB AS T
WHERE
2>(SELECT COUNT(*) FROM TB WHERE 姓名=T.姓名 AND 分数>T.分数)
能解释一下这个语句么。弄了半个小时,没搞懂 分数>T.分数 的意思。。
2>(SELECT COUNT(*) FROM TB WHERE 姓名=T.姓名 AND 分数>T.分数):
说明大于T.分数的记录最多允许为一条,这样就表明T.分数不是第一就是第二