300分,求一存储过程,时间较紧(本周五以前要),先感谢大家!!
n_player 选手表
nrid (int 自动编号) nrname(选手姓名 nvarchar) nisHeader(int 是否为班长，1为是班长）
1 张三 0
2 李四 0
3 王五 1
4 陈七 1
...
n_score 选手得分表
nsid(int 自动编号) nscore(float 得分) nrid(选手编号)
1 9 2
2 7 1
3 7 3
4 5 4
5 7 1
6 9 2
其中得分只有三种情况（9、7、5),9分优，7为良，5为差
结果打印出两个表，一个班长长的评比，一个不是班长的评比：
表1：班长评比表
名称 优秀率（优秀个数/总个数）% 得良率 差率 总分 排名
王五 33.33% 33.33% 33.33% 23 1
陈七 100% 0 0 20 2
...
表2：个人评比表
名称 优秀率（优秀个数/总个数）% 得良率 差率 总分 排名
张三 33.33% 33.33% 33.33% 23 1
李四 100% 0 0 20 2
...
排名方式如：1，1，3（如果两个并列第一，则直接到第三名）
由于时间紧,请大家帮忙，先感谢SQL版朋友
[解决办法]
--测试数据
CREATE TABLE dbo.n_player
(nrid INT identity(1,1),
nrname NVARCHAR(20) ,
nisHeader INT )
INSERT INTO n_player VALUES (N '张三 ',0)
INSERT INTO n_player VALUES (N '李四 ',0)
INSERT INTO n_player VALUES (N '王五 ',1)
INSERT INTO n_player VALUES (N '陈七 ',1)
CREATE TABLE dbo.n_score
(nsid INT identity(1,1),
nscore float ,
nrid INT )
INSERT INTO n_score VALUES (9,2)
INSERT INTO n_score VALUES (7,1)
INSERT INTO n_score VALUES (7,3)
INSERT INTO n_score VALUES (5,4)
INSERT INTO n_score VALUES (7,1)
INSERT INTO n_score VALUES (9,2)
--临时表
SELECT y.nrname,
STR((n.yrate+0.00)/s.cnt*100,5,2)+ '% ' AS yrate,
STR((n.lrate+0.00)/s.cnt*100,5,2)+ '% ' AS lrate,
STR((n.crate+0.00)/s.cnt*100,5,2)+ '% ' AS crate,
score
INTO dbo.#
FROM
(SELECT nrid,
SUM(CASE nscore WHEN 9 THEN 1 ELSE 0 END ) AS yrate,
SUM(CASE nscore WHEN 7 THEN 1 ELSE 0 END ) AS lrate,
SUM(CASE nscore WHEN 5 THEN 1 ELSE 0 END ) AS crate,
SUM(nscore) AS score
FROM n_score
GROUP BY nrid) n
INNER JOIN
(SELECT nrid, COUNT(*) AS cnt
FROM n_score
GROUP BY nrid) s
ON n.nrid=s.nrid
INNER JOIN n_player y
ON n.nrid=y.nrid
ORDER BY score DESC ,yrate DESC ,lrate DESC ,crate DESC
--最后结果
SELECT a.*,
(SELECT COUNT(*) FROM # b WHERE b.score> =a.score) AS sort
FROM # a
nrname yrate lrate crate score sort
------ ------ ------ ------ ------ ----
李四 100.0% 0.00% 0.00% 18.0 1
张三 0.00% 100.0% 0.00% 14.0 2
王五 0.00% 100.0% 0.00% 7.0 3
陈七 0.00% 0.00% 100.0% 5.0 4
(4 row(s) affected)
--删除临时表
DROP TABLE #
[解决办法]
create table n_player
(
nrid int identity(1,1) primary key,
nrname nvarchar(10),
nisHeader int
)
create table n_score
(
nsid int identity(1,1) primary key,
nscore float,
nrid int
)
insert into n_player select '张三 ', 0
insert into n_player select '李四 ', 0
insert into n_player select '王五 ', 1
insert into n_player select '陈七 ', 1
insert into n_score select 9, 2
insert into n_score select 7, 1
insert into n_score select 7, 3
insert into n_score select 5, 4
insert into n_score select 7, 1
insert into n_score select 9, 2
--班长
select a.nrid,a.nrname,
优秀率 = sum(case nscore when 9 then 1 else 0 end)*1.0/count(1),
得良率 = sum(case nscore when 7 then 1 else 0 end)*1.0/count(1),
差率 = sum(case nscore when 5 then 1 else 0 end)*1.0/count(1),
总分 = sum(nscore),
排名 = (select count(1) from (select b.nrid,sum(b.nscore) as nscore
from n_player a inner join n_score b on a.nrid = b.nrid
where a.nisHeader = 1 group by b.nrid)t
where nscore > sum(b.nscore)) + 1
from n_player a inner join n_score b on a.nrid = b.nrid
where a.nisHeader = 1
group by a.nrid,a.nrname
order by 排名
--结果
3王五.0000000000001.000000000000.0000000000007.01
4陈七.000000000000.0000000000001.0000000000005.02
--非班长
select a.nrid,a.nrname,
优秀率 = sum(case nscore when 9 then 1 else 0 end)*1.0/count(1),
得良率 = sum(case nscore when 7 then 1 else 0 end)*1.0/count(1),
差率 = sum(case nscore when 5 then 1 else 0 end)*1.0/count(1),
总分 = sum(nscore),
排名 = (select count(1) from (select b.nrid,sum(b.nscore) as nscore
from n_player a inner join n_score b on a.nrid = b.nrid
where a.nisHeader = 0 group by b.nrid)t
where nscore > sum(b.nscore)) + 1
from n_player a inner join n_score b on a.nrid = b.nrid
where a.nisHeader = 0
group by a.nrid,a.nrname
order by 排名
--结果
2李四1.000000000000.000000000000.00000000000018.01
1张三.0000000000001.000000000000.00000000000014.02
[解决办法]
楼主只需在存储过程中加标志返回的是班长还是非班长就可以了
[解决办法]
1楼真乃神速也
[解决办法]
mark学习,帮顶!
[解决办法]
--临时表解决方法
CREATE TABLE [dbo].[n_player]
(
[nrid] [int] IDENTITY(1,1) NOT NULL,
[nrname] [nvarchar](50) NULL,
[nisHeader] [int]
)
CREATE TABLE [dbo].[n_score]
(
[nsid] [int] IDENTITY(1,1) NOT NULL,
[nscore] [float],
[nrid] [int]
)
INSERT INTO [dbo].[n_player]([nrname],[nisHeader])
SELECT '张三 ',0 UNION ALL
SELECT '李四 ',0 UNION ALL
SELECT '王五 ',1 UNION ALL
SELECT '陈七 ',1
INSERT INTO [dbo].[n_score] ([nscore],[nrid])
SELECT 9,2 UNION ALL
SELECT 7,1 UNION ALL
SELECT 7,3 UNION ALL
SELECT 5,4 UNION ALL
SELECT 7,1 UNION ALL
SELECT 9,2
--建立班长评比临时表
CREATE TABLE #1
(
[nrname] [nvarchar](50) NULL,
[nscore] [float] NULL,
[yx] [int],
[lh] [int],
[cl] [int]
)
--非班长评比临时表
CREATE TABLE #0
(
[nrname] [nvarchar](50) NULL,
[nscore] [float] NULL,
[yx] [int],
[lh] [int],
[cl] [int]
)
INSERT INTO #1(nrname,nscore) SELECT n_player.nrname,n_score.nscore FROM [dbo].[n_player] INNER JOIN [dbo].[n_score] ON [dbo].[n_player].nrid=[dbo].[n_score].nrid AND nisHeader=1
UPDATE #1 SET
YX=
CASE nscore WHEN 9 THEN 1 ELSE 0 END
,lh=
CASE nscore WHEN 7 THEN 1 ELSE 0 END
,cl=
CASE nscore WHEN 5 THEN 1 ELSE 0 END
INSERT INTO #0(nrname,nscore) SELECT n_player.nrname,n_score.nscore FROM [dbo].[n_player] INNER JOIN [dbo].[n_score] ON [dbo].[n_player].nrid=[dbo].[n_score].nrid AND nisHeader=0
UPDATE #0 SET
YX=
CASE nscore WHEN 9 THEN 1 ELSE 0 END
,lh=
CASE nscore WHEN 7 THEN 1 ELSE 0 END
,cl=
CASE nscore WHEN 5 THEN 1 ELSE 0 END
/*--------------------------------------------------*/
--非班长统计数据
--建立临时表，分组求和，加入自增列，循环计算名次
SELECT identity(int,1,1) IDEN ,nrname 姓名,CAST(CAST((SUM(yx)/count(1))*100 AS NUMERIC (8,2)) AS VARCHAR(20))+ '% ' 优秀,CAST(CAST((SUM(lh)/count(1))*100 AS NUMERIC (8,2)) AS VARCHAR(20))+ '% ' as 良好,CAST(CAST((SUM(cl)/count(1))*100 AS NUMERIC (8,2)) AS VARCHAR(20))+ '% ' as 差率,SUM(nscore) as 总分,0 as 名次 INTO #TEST FROM #0 GROUP BY nrname ORDER BY 总分 DESC
--定义其中所要用到的变量@mc名次,@rwct当前临时表中的行数，@i当前行,@SCORE当前行总分
DECLARE @i INT,@SCORE FLOAT,@mc int,@rwct int
SET @i=1
SELECT @rwct=COUNT(1) FROM #TEST
WHILE @i <=@rwct
BEGIN
SELECT @SCORE = 总分 FROM #TEST WHERE IDEN=@i
SELECT @mc = COUNT(1)+1 FROM #TEST WHERE 总分> @SCORE
UPDATE #TEST SET 名次=@mc WHERE IDEN=@i
SET @i=@i+1
END
SELECT 姓名,优秀,良好,差率,总分,名次 FROM #TEST
/*--------------------------------------------------*/
DROP TABLE #TEST
/*--------------------------------------------------*/
--班长统计数据
--建立临时表，分组求和，加入自增列，循环计算名次
SELECT identity(int,1,1) IDEN ,nrname 姓名,CAST(CAST((SUM(yx)/count(1))*100 AS NUMERIC (8,2)) AS VARCHAR(20))+ '% ' 优秀,CAST(CAST((SUM(lh)/count(1))*100 AS NUMERIC (8,2)) AS VARCHAR(20))+ '% ' as 良好,CAST(CAST((SUM(cl)/count(1))*100 AS NUMERIC (8,2)) AS VARCHAR(20))+ '% ' as 差率,SUM(nscore) as 总分,0 as 名次 INTO #TEST1 FROM #1 GROUP BY nrname ORDER BY 总分 DESC
--定义其中所要用到的变量@mc名次,@rwct当前临时表中的行数，@i当前行,@SCORE当前行总分
--DECLARE @i INT,@SCORE FLOAT,@mc int,@rwct int
SET @i=1
SELECT @rwct=COUNT(1) FROM #TEST1
WHILE @i <=@rwct
BEGIN
SELECT @SCORE = 总分 FROM #TEST1 WHERE IDEN=@i
SELECT @mc = COUNT(1)+1 FROM #TEST1 WHERE 总分> @SCORE
UPDATE #TEST1 SET 名次=@mc WHERE IDEN=@i
SET @i=@i+1
END
SELECT 姓名,优秀,良好,差率,总分,名次 FROM #TEST1
/*--------------------------------------------------*/
DROP TABLE #TEST1
DROP TABLE #1
DROP TABLE #0
DROP TABLE [dbo].[n_player]
DROP TABLE [dbo].[n_score]
--测试已通过结果为:
姓名 优秀 良好 差率 总分 名次
-------------------------------------------------- --------------------- --------------------- --------------------- ---------------------- -----------
李四 100.00% 0.00% 0.00% 18 1
张三 0.00% 100.00% 0.00% 14 2
姓名 优秀 良好 差率 总分 名次
-------------------------------------------------- --------------------- --------------------- --------------------- ---------------------- -----------
王五 0.00% 100.00% 0.00% 7 1
陈七 0.00% 0.00% 100.00% 5 2
