表中有列:学生号 ,班级号 ,分数1、如何取得每个班分数排在后面一半的人,比如1班50人,2班60人,3班70人则需取出1班第26-50名学生,2班第31-60名学生,3班36-70名学生
2、求平均分,当然,第1问解决了,平均分就好求了不要告诉我用游标,游标方法我已经写出来了,表数据太大,效率极低
技术、交流第一,分数次之!急用啊,在此谢谢各位大人了!!!
2、求平均分,当然,第1问解决了,平均分就好求了不要告诉我用游标,游标方法我已经写出来了,表数据太大,效率极低
技术、交流第一,分数次之!急用啊,在此谢谢各位大人了!!!
select * from tb t where 分数 in(select top 50 percent 分数 from tb where 班级号=t.班级号 order by 分数 desc)
create table [tb]([班級] int,[姓名] varchar(2)[分數] int)
insert [tb]
select 9901,'a1',50 union all
select 9901,'b1',80 union all
select 9901,'c1',82 union all
select 9901,'d1',85 union all
select 9902,'a2',86 union all
select 9902,'b2',58 union all
select 9902,'c2',68 union all
select 9902,'d2',98 union all
select 9902,'e2',88 union all
select 9903,'a2',17 union all
select 9903,'b2',37 union all
select 9903,'c2',57 union all
select 9903,'d2',87 union all
select 9903,'e2',97 union all
select 9903,'f2',33 union all
select 9903,'g2',45 union all
select 9902,'h2',90
(
学生号 nchar(20),
班级号 nchar(20),
分数 int
)insert into #student select '0001','1班',85
insert into #student select '0002','1班',65
insert into #student select '0003','1班',34
insert into #student select '0004','1班',45
insert into #student select '0005','1班',55
insert into #student select '0006','1班',75
insert into #student select '0007','1班',85
insert into #student select '0008','1班',58
insert into #student select '0011','1班',78
insert into #student select '0012','1班',65
insert into #student select '0014','1班',35
insert into #student select '0014','1班',25
insert into #student select '0008','2班',58
insert into #student select '0011','2班',78
insert into #student select '0012','2班',65
insert into #student select '0014','2班',35
insert into #student select '0014','2班',25declare @Class nvarchar(50)
set @Class='1班'
select top 50 percent 学生号,班级号,分数
from
(
select *,
(select count(*)+1 from #student where 班级号=t.班级号 and 分数<t.分数) row from #student t
) tt
where 班级号=@Class
order by row学生号 班级号 分数
-------------------- -------------------- -----------
0014 1班 25
0003 1班 34
0014 1班 35
0004 1班 45
0005 1班 55
0008 1班 58(6 行受影响)
select * from tb t where 分数
in
(
select top 50 percent 分数 from tb where 班级号=t.班级号 order by 分数 desc
)
create table [tb]([班級] int,[姓名] varchar(2),[分數] int)
insert [tb]
select 9901,'a1',50 union all
select 9901,'b1',80 union all
select 9901,'c1',82 union all
select 9901,'d1',85 union all
select 9902,'a2',86 union all
select 9902,'b2',58 union all
select 9902,'c2',68 union all
select 9902,'d2',98 union all
select 9902,'e2',88 union all
select 9903,'a2',17 union all
select 9903,'b2',37 union all
select 9903,'c2',57 union all
select 9903,'d2',87 union all
select 9903,'e2',97 union all
select 9903,'f2',33 union all
select 9903,'g2',45 union all
select 9902,'h2',90declare @Class int
set @Class=9901
select top 50 percent [班級],[姓名],[分數]
from
(
select *,
(select count(*)+1 from [tb] where [班級]=t.[班級] and [分數]<t.[分數]) row from [tb] t
) tt
where [班級]=@Class
order by row班級 姓名 分數
----------- ---- -----------
9901 a1 50
9901 b1 80(2 行受影响)
-- Author : htl258(Tony)
-- Date : 2010-05-12 10:07:52
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([学生号] [int],[班级号] [nvarchar](10),[分数] [int])
INSERT INTO [tb]
SELECT '1','a','2' UNION ALL
SELECT '2','a','4' UNION ALL
SELECT '3','a','5' UNION ALL
SELECT '4','a','7' UNION ALL
SELECT '5','a','3' UNION ALL
SELECT '6','a','9' UNION ALL
SELECT '7','a','18' UNION ALL
SELECT '8','a','10' UNION ALL
SELECT '9','a','6' UNION ALL
SELECT '10','a','3' UNION ALL
SELECT '1','b','21' UNION ALL
SELECT '2','b','4' UNION ALL
SELECT '3','b','5' UNION ALL
SELECT '4','b','7' UNION ALL
SELECT '5','b','3' UNION ALL
SELECT '6','b','9' UNION ALL
SELECT '7','b','18' UNION ALL
SELECT '8','b','10'--SELECT * FROM [tb]-->SQL查询如下:
select * from tb t where 分数 in(select top 50 percent 分数 from tb where 班级号=t.班级号 order by 分数 desc)
/*
学生号 班级号 分数
----------- ---------- -----------
4 a 7
6 a 9
7 a 18
8 a 10
9 a 6
1 b 21
6 b 9
7 b 18
8 b 10(9 行受影响)
*/请楼主指出错误点。
-- Author : htl258(Tony)
-- Date : 2010-05-12 10:10:13
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([学生号] [int],[班级号] [nvarchar](10),[分数] [int])
INSERT INTO [tb]
SELECT '1','a','1' UNION ALL
SELECT '2','a','2' UNION ALL
SELECT '3','a','3' UNION ALL
SELECT '4','a','4' UNION ALL
SELECT '5','a','5' UNION ALL
SELECT '6','a','6' UNION ALL
SELECT '7','a','7' UNION ALL
SELECT '8','a','8' UNION ALL
SELECT '9','a','9' UNION ALL
SELECT '10','a','10' UNION ALL
SELECT '11','a','11' UNION ALL
SELECT '12','a','12' UNION ALL
SELECT '13','a','13' UNION ALL
SELECT '14','a','14' UNION ALL
SELECT '15','a','15' UNION ALL
SELECT '16','a','16' UNION ALL
SELECT '17','a','17' UNION ALL
SELECT '18','a','18' UNION ALL
SELECT '19','a','19' UNION ALL
SELECT '20','a','20' UNION ALL
SELECT '21','a','21' UNION ALL
SELECT '22','a','22' UNION ALL
SELECT '23','a','23' UNION ALL
SELECT '24','a','24' UNION ALL
SELECT '25','a','25' UNION ALL
SELECT '26','a','26' UNION ALL
SELECT '27','a','27' UNION ALL
SELECT '28','a','28' UNION ALL
SELECT '29','a','29' UNION ALL
SELECT '30','a','30' UNION ALL
SELECT '31','a','31' UNION ALL
SELECT '32','a','32' UNION ALL
SELECT '33','a','33' UNION ALL
SELECT '34','a','34' UNION ALL
SELECT '35','a','35' UNION ALL
SELECT '36','a','36' UNION ALL
SELECT '37','a','37' UNION ALL
SELECT '38','a','38' UNION ALL
SELECT '39','a','39' UNION ALL
SELECT '40','a','40' UNION ALL
SELECT '41','a','41' UNION ALL
SELECT '42','a','42' UNION ALL
SELECT '43','a','43' UNION ALL
SELECT '44','a','44' UNION ALL
SELECT '45','a','45' UNION ALL
SELECT '46','a','46' UNION ALL
SELECT '47','a','47' UNION ALL
SELECT '48','a','48' UNION ALL
SELECT '49','a','49' UNION ALL
SELECT '50','a','50' UNION ALL
SELECT '51','a','51' UNION ALL
SELECT '52','a','52' UNION ALL
SELECT '53','a','53' UNION ALL
SELECT '54','a','54' UNION ALL
SELECT '55','a','55' UNION ALL
SELECT '1','b','21' UNION ALL
SELECT '2','b','4' UNION ALL
SELECT '3','b','5' UNION ALL
SELECT '4','b','7' UNION ALL
SELECT '5','b','3' UNION ALL
SELECT '6','b','9' UNION ALL
SELECT '7','b','18' UNION ALL
SELECT '8','b','10'--SELECT * FROM [tb]-->SQL查询如下:
select * from tb t where 分数 in(select top 50 percent 分数 from tb where 班级号=t.班级号 order by 分数 desc)
/*
学生号 班级号 分数
----------- ---------- -----------
28 a 28
29 a 29
30 a 30
31 a 31
32 a 32
33 a 33
34 a 34
35 a 35
36 a 36
37 a 37
38 a 38
39 a 39
40 a 40
41 a 41
42 a 42
43 a 43
44 a 44
45 a 45
46 a 46
47 a 47
48 a 48
49 a 49
50 a 50
51 a 51
52 a 52
53 a 53
54 a 54
55 a 55
6 b 9
8 b 10
7 b 18
1 b 21(32 行受影响)*/
from tb t
where [学生号] in(
select top 50 percent [学生号]
from tb
where 班级号=t.班级号
order by 分数 desc)
用这个肯定可以
(select a.class,a.id,a.point from #tb a join #tb b on a.class=b.class
where a.point<b.point
group by a.class,a.id,a.point
having COUNT(1)>=(select COUNT(1)/2 from #tb where class=a.class)) a
group by a.class
(select a.class,a.id,a.point from #tb a join #tb b on a.class=b.class
where a.point<b.point
group by a.class,a.id,a.point
having COUNT(1)>=(select COUNT(1)/2 from #tb where class=a.class)) a
group by a.class
select class,AVG(point) from
(select a.class,a.id,a.point from #tb a join #tb b on a.class=b.class
where a.point<=b.point
group by a.class,a.id,a.point
having COUNT(1)>(select COUNT(1)/2 from #tb where class=a.class)) a
group by a.class
id int,
class varchar(10),
point int)
insert into #tb values(21,1,1)
insert into #tb values(21,1,2)
insert into #tb values(21,1,5)
insert into #tb values(21,1,5)
insert into #tb values(22,1,5)
insert into #tb values(23,1,5)
insert into #tb values(10,1,6)
insert into #tb values(10,1,7)
insert into #tb values(10,1,8)
insert into #tb values(3,2,66)
insert into #tb values(4,2,55)
insert into #tb values(5,3,312)
insert into #tb values(6,3,524)
insert into #tb values(7,3,121)
insert into #tb values(8,3,643)這是我的測試數據,測試通過
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([学生号] [int],[班级号] [nvarchar](10),[分数] [int])
INSERT INTO [tb]
SELECT '1','a','28' UNION ALL
SELECT '2','a','28' UNION ALL
SELECT '3','a','3' UNION ALL
SELECT '4','a','4' UNION ALL
SELECT '5','a','5' UNION ALL
SELECT '6','a','6' UNION ALL
SELECT '7','a','7' UNION ALL
SELECT '8','a','8' UNION ALL
SELECT '9','a','9' UNION ALL
SELECT '10','a','10' UNION ALL
SELECT '11','a','11' UNION ALL
SELECT '12','a','12' UNION ALL
SELECT '13','a','13' UNION ALL
SELECT '14','a','14' UNION ALL
SELECT '15','a','15' UNION ALL
SELECT '16','a','16' UNION ALL
SELECT '17','a','17' UNION ALL
SELECT '18','a','18' UNION ALL
SELECT '19','a','19' UNION ALL
SELECT '20','a','20' UNION ALL
SELECT '21','a','21' UNION ALL
SELECT '22','a','22' UNION ALL
SELECT '23','a','23' UNION ALL
SELECT '24','a','24' UNION ALL
SELECT '25','a','28' UNION ALL
SELECT '26','a','26' UNION ALL
SELECT '27','a','27' UNION ALL
SELECT '28','a','28' UNION ALL
SELECT '29','a','29' UNION ALL
SELECT '30','a','30' UNION ALL
SELECT '31','a','31' UNION ALL
SELECT '32','a','32' UNION ALL
SELECT '33','a','33' UNION ALL
SELECT '34','a','34' UNION ALL
SELECT '35','a','35' UNION ALL
SELECT '36','a','36' UNION ALL
SELECT '37','a','37' UNION ALL
SELECT '38','a','38' UNION ALL
SELECT '39','a','39' UNION ALL
SELECT '40','a','40' UNION ALL
SELECT '41','a','41' UNION ALL
SELECT '42','a','42' UNION ALL
SELECT '43','a','43' UNION ALL
SELECT '44','a','44' UNION ALL
SELECT '45','a','45' UNION ALL
SELECT '46','a','46' UNION ALL
SELECT '47','a','47' UNION ALL
SELECT '48','a','48' UNION ALL
SELECT '49','a','49' UNION ALL
SELECT '50','a','50' UNION ALL
SELECT '51','a','51' UNION ALL
SELECT '52','a','52' UNION ALL
SELECT '53','a','53' UNION ALL
SELECT '54','a','54' UNION ALL
SELECT '55','a','55' UNION ALL
SELECT '1','b','21' UNION ALL
SELECT '2','b','4' UNION ALL
SELECT '3','b','5' UNION ALL
SELECT '4','b','7' UNION ALL
SELECT '5','b','3' UNION ALL
SELECT '6','b','9' UNION ALL
SELECT '7','b','18' UNION ALL
SELECT '8','b','10'--这是SQL2005各半的求法
;with t as
(
select rn=ntile(2)over(partition by [班级号] order by [分数] desc),*
from tb
)
select 学生号,班级号,分数
from t
where rn=1
/*
学生号 班级号 分数
----------- ---------- -----------
55 a 55
54 a 54
53 a 53
52 a 52
51 a 51
50 a 50
49 a 49
48 a 48
47 a 47
46 a 46
45 a 45
44 a 44
43 a 43
42 a 42
41 a 41
40 a 40
39 a 39
38 a 38
37 a 37
36 a 36
35 a 35
34 a 34
33 a 33
32 a 32
31 a 31
30 a 30
29 a 29
28 a 28
1 b 21
7 b 18
8 b 10
6 b 9(32 行受影响)
*/--SQL2005各半且包括临界值的求法
;with t as
(
select rn=ntile(2)over(partition by [班级号] order by [分数] desc),*
from tb
)
select b.*
from t a
join tb b
on a.班级号=b.班级号 and a.分数=b.分数
where a.rn=1
/*
学生号 班级号 分数
----------- ---------- -----------
1 a 28
2 a 28
25 a 28
28 a 28
29 a 29
30 a 30
31 a 31
32 a 32
33 a 33
34 a 34
35 a 35
36 a 36
37 a 37
38 a 38
39 a 39
40 a 40
41 a 41
42 a 42
43 a 43
44 a 44
45 a 45
46 a 46
47 a 47
48 a 48
49 a 49
50 a 50
51 a 51
52 a 52
53 a 53
54 a 54
55 a 55
1 b 21
6 b 9
7 b 18
8 b 10(35 行受影响)
*/
select top 50 percent name, class, score
from tb_1
order by score asc
)
select tb_1.* --avg(tb_1.score) as avg
from tb_1,t
where tb_1.name = t.name and tb_1.score = t.score
group by tb_1.class, tb_1.name,tb_1.score