100分求帮忙，一个SQL语句，高手帮忙写写。

[zyd_fyl]

create table tbscore
    (
    schoolname varchar(20) ,
    stuname varchar(20),
    coursename varchar(10),
    score int,
    testname varchar(60),
    termname varchar(10)
)

insert into tbscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','数学',30,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','语文',32,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','数学',40,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','语文',42,'2009年6月份婺城区初一年级各学校摸底考试','初一'
/*
----------结果格式是这个样子的，结果乱写的，不正确-------------------
学校     总人数   总分后20%人数    总分比率   数学后20%人数   数学比率   语文后20%人数  语文比率      
金华四中    5         1           20%          2          40%           3             60%
金华五中    5         1           20%          5          100%          4             80%


注：
1.总人数：各学校总人数
2.总分后20%人数：这次考试所有学校的学生，总分最低的后20%，每个学校分别占几个人。（总分，不是单科）
3.总分比率：该学校总分后20%所占人数/该学校总人数。
4.数学后20%人数：这次考试所有学校的学生，数学最低的后20%，每个学校分别占几个人。（单科）
5.数学比率：该学校数学的后20%所占人数/该学校总人数。
5.语文：同上。
求大家帮忙，不明白之处请指出，在线等。

[昵称被占用了]

做SQL之前要先学好语文和数学，呵呵

[昵称被占用了]

[Quote=引用 7 楼 guguda2008 的回复:]
引用 4 楼 haiwer 的回复:

引用 1 楼 guguda2008 的回复:
总分最低的后20%
这个不明白，这个直接人数/5就是了，你这个百分比是怎么算出来的

我估计不是这个意思，是说
金华四中 在全市总分后20%的人数，各科在全市总分后20%的人数，等等

明白意思了你就能写出来，是吧。

表里也没全市总分啊
[/Quote]
自己根据所有数据计算，你可以的

[zyd_fyl]

----------结果格式是这个样子的，结果乱写的，不正确-------------------
学校     总人数   总分后20%人数    总分比率   数学后20%人数   数学比率   语文后20%人数  语文比率      
金华四中    100         10           10%          50          50%           20             20%
金华五中    200         50           25%          10          5%          40             20%

[zyd_fyl]

[Quote=引用 9 楼 haiwer 的回复:]
引用 7 楼 guguda2008 的回复:
引用 4 楼 haiwer 的回复:

引用 1 楼 guguda2008 的回复:
总分最低的后20%
这个不明白，这个直接人数/5就是了，你这个百分比是怎么算出来的

我估计不是这个意思，是说
金华四中 在全市总分后20%的人数，各科在全市总分后20%的人数，等等

明白意思了你就能写出来，是吧。

表里也没全市总分啊

……
[/Quote]
我研究了下，结果不对，所以来求助的，呵呵。

[zyd_fyl]

搞乱了。

[昵称被占用了]

估计鸭子在写中，等下就有结果了

李钢家的苏格兰牧羊鸭，很是V5的

[zyd_fyl]

[Quote=引用 13 楼 haiwer 的回复:]
估计鸭子在写中，等下就有结果了

李钢家的苏格兰牧羊鸭，很是V5的
[/Quote]
呵呵，你也帮下忙，我sql不行，昨天写了一下午，今天上午又写了下，怎么结果都不对才来求助的，晚上要交差了，谢谢帮忙。

[guguda2008]

TOP 20 PERCENT只能算出个大概的，不过原本你也没想多精确是吧

IF OBJECT_ID('TBSCORE') IS NOT NULL DROP TABLE TBSCORE
GO
create table tbscore
    (
    schoolname varchar(20) ,
    stuname varchar(20),
    coursename varchar(10),
    score int,
    testname varchar(60),
    termname varchar(10)
)

insert into tbscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','数学',30,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','语文',32,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','数学',40,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','语文',42,'2009年6月份婺城区初一年级各学校摸底考试','初一'
GO
;WITH MU AS (
SELECT SCHOOLNAME,STUNAME
,SUM(SCORE) AS TOTAL
,SUM(CASE WHEN coursename='数学' THEN SCORE ELSE 0 END) AS STOTAL
,SUM(CASE WHEN coursename='语文' THEN SCORE ELSE 0 END) AS YTOTAL
FROM TBSCORE
GROUP BY SCHOOLNAME,STUNAME
)
SELECT T1.SCHOOLNAME AS [学校],T1.TOTAL AS [总人数]
,ISNULL(T2.TOTAL,0) AS [总分后20%人数],CONVERT(VARCHAR(10),CONVERT(NUMERIC(19,0),ISNULL(T2.TOTAL,0)*100.0/T1.TOTAL))+'%' AS [总分比率]
,ISNULL(T3.TOTAL,0) AS [数学后20%人数],CONVERT(VARCHAR(10),CONVERT(NUMERIC(19,0),ISNULL(T3.TOTAL,0)*100.0/T1.TOTAL))+'%' AS [数学比率]
,ISNULL(T4.TOTAL,0) AS [语文后20%人数],CONVERT(VARCHAR(10),CONVERT(NUMERIC(19,0),ISNULL(T4.TOTAL,0)*100.0/T1.TOTAL))+'%' AS [语文比率]
FROM (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL
FROM TBSCORE
GROUP BY SCHOOLNAME
) T1
LEFT JOIN (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL FROM (
SELECT TOP 20 PERCENT WITH TIES SCHOOLNAME
FROM MU
ORDER BY TOTAL ASC
) T 
GROUP BY SCHOOLNAME
) T2 ON T1.SCHOOLNAME=T2.SCHOOLNAME
LEFT JOIN (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL FROM (
SELECT TOP 20 PERCENT WITH TIES SCHOOLNAME
FROM MU
ORDER BY STOTAL ASC
) T 
GROUP BY SCHOOLNAME
) T3 ON T1.SCHOOLNAME=T3.SCHOOLNAME
LEFT JOIN (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL FROM (
SELECT TOP 20 PERCENT WITH TIES SCHOOLNAME
FROM MU
ORDER BY YTOTAL ASC
) T 
GROUP BY SCHOOLNAME
) T4 ON T1.SCHOOLNAME=T4.SCHOOLNAME
/*
学校                   总人数         总分后20%人数    总分比率        数学后20%人数    数学比率        语文后20%人数    语文比率
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
金华四中                 10          1           10%         1           10%         1           10%
金华五中                 10          1           10%         1           10%         1           10%
*/

[joyhen]

速度真快一个，基本上是开窗函数的应用了

[guguda2008]

把数据里的语文改了一下，看看最后两列的效果

IF OBJECT_ID('TBSCORE') IS NOT NULL DROP TABLE TBSCORE
GO
create table tbscore
    (
    schoolname varchar(20) ,
    stuname varchar(20),
    coursename varchar(10),
    score int,
    testname varchar(60),
    termname varchar(10)
)

insert into tbscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','数学',30,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','语文',32,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','数学',40,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','语文',19,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',18,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','语文',17,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','语文',16,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','语文',42,'2009年6月份婺城区初一年级各学校摸底考试','初一'
GO
;WITH MU AS (
SELECT SCHOOLNAME,STUNAME
,SUM(SCORE) AS TOTAL
,SUM(CASE WHEN coursename='数学' THEN SCORE ELSE 0 END) AS STOTAL
,SUM(CASE WHEN coursename='语文' THEN SCORE ELSE 0 END) AS YTOTAL
FROM TBSCORE
GROUP BY SCHOOLNAME,STUNAME
)
SELECT T1.SCHOOLNAME AS [学校],T1.TOTAL AS [总人数]
,ISNULL(T2.TOTAL,0) AS [总分后20%人数],CONVERT(VARCHAR(10),CONVERT(NUMERIC(19,0),ISNULL(T2.TOTAL,0)*100.0/T1.TOTAL))+'%' AS [总分比率]
,ISNULL(T3.TOTAL,0) AS [数学后20%人数],CONVERT(VARCHAR(10),CONVERT(NUMERIC(19,0),ISNULL(T3.TOTAL,0)*100.0/T1.TOTAL))+'%' AS [数学比率]
,ISNULL(T4.TOTAL,0) AS [语文后20%人数],CONVERT(VARCHAR(10),CONVERT(NUMERIC(19,0),ISNULL(T4.TOTAL,0)*100.0/T1.TOTAL))+'%' AS [语文比率]
FROM (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL
FROM TBSCORE
GROUP BY SCHOOLNAME
) T1
LEFT JOIN (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL FROM (
SELECT TOP 20 PERCENT WITH TIES SCHOOLNAME
FROM MU
ORDER BY TOTAL ASC
) T 
GROUP BY SCHOOLNAME
) T2 ON T1.SCHOOLNAME=T2.SCHOOLNAME
LEFT JOIN (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL FROM (
SELECT TOP 20 PERCENT WITH TIES SCHOOLNAME
FROM MU
ORDER BY STOTAL ASC
) T 
GROUP BY SCHOOLNAME
) T3 ON T1.SCHOOLNAME=T3.SCHOOLNAME
LEFT JOIN (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL FROM (
SELECT TOP 20 PERCENT WITH TIES SCHOOLNAME
FROM MU
ORDER BY YTOTAL ASC
) T 
GROUP BY SCHOOLNAME
) T4 ON T1.SCHOOLNAME=T4.SCHOOLNAME
/*
学校                   总人数         总分后20%人数    总分比率        数学后20%人数    数学比率        语文后20%人数    语文比率
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
金华四中                 10          1           10%         1           10%         0           0%
金华五中                 10          1           10%         1           10%         2           20%
*/

[zyd_fyl]

结果不对，每个学校只有5个人。呵呵。

[guguda2008]

[Quote=引用 18 楼 zyd_fyl 的回复:]

结果不对，每个学校只有5个人。呵呵。
[/Quote]

IF OBJECT_ID('TBSCORE') IS NOT NULL DROP TABLE TBSCORE
GO
create table tbscore
    (
    schoolname varchar(20) ,
    stuname varchar(20),
    coursename varchar(10),
    score int,
    testname varchar(60),
    termname varchar(10)
)

insert into tbscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','数学',30,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华四中','s5','语文',32,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','数学',40,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w1','语文',19,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',18,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w3','语文',17,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w4','语文',16,'2009年6月份婺城区初一年级各学校摸底考试','初一'    union
select '金华五中','w5','语文',42,'2009年6月份婺城区初一年级各学校摸底考试','初一'
GO
;WITH MU AS (
SELECT SCHOOLNAME,STUNAME
,SUM(SCORE) AS TOTAL
,SUM(CASE WHEN coursename='数学' THEN SCORE ELSE 0 END) AS STOTAL
,SUM(CASE WHEN coursename='语文' THEN SCORE ELSE 0 END) AS YTOTAL
FROM TBSCORE
GROUP BY SCHOOLNAME,STUNAME
)
SELECT T1.SCHOOLNAME AS [学校],T1.TOTAL AS [总人数]
,ISNULL(T2.TOTAL,0) AS [总分后20%人数],CONVERT(VARCHAR(10),CONVERT(NUMERIC(19,0),ISNULL(T2.TOTAL,0)*100.0/T1.TOTAL))+'%' AS [总分比率]
,ISNULL(T3.TOTAL,0) AS [数学后20%人数],CONVERT(VARCHAR(10),CONVERT(NUMERIC(19,0),ISNULL(T3.TOTAL,0)*100.0/T1.TOTAL))+'%' AS [数学比率]
,ISNULL(T4.TOTAL,0) AS [语文后20%人数],CONVERT(VARCHAR(10),CONVERT(NUMERIC(19,0),ISNULL(T4.TOTAL,0)*100.0/T1.TOTAL))+'%' AS [语文比率]
FROM (
SELECT SCHOOLNAME,COUNT(DISTINCT stuname) AS TOTAL
FROM TBSCORE
GROUP BY SCHOOLNAME
) T1
LEFT JOIN (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL FROM (
SELECT TOP 20 PERCENT WITH TIES SCHOOLNAME
FROM MU
ORDER BY TOTAL ASC
) T 
GROUP BY SCHOOLNAME
) T2 ON T1.SCHOOLNAME=T2.SCHOOLNAME
LEFT JOIN (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL FROM (
SELECT TOP 20 PERCENT WITH TIES SCHOOLNAME
FROM MU
ORDER BY STOTAL ASC
) T 
GROUP BY SCHOOLNAME
) T3 ON T1.SCHOOLNAME=T3.SCHOOLNAME
LEFT JOIN (
SELECT SCHOOLNAME,COUNT(1) AS TOTAL FROM (
SELECT TOP 20 PERCENT WITH TIES SCHOOLNAME
FROM MU
ORDER BY YTOTAL ASC
) T 
GROUP BY SCHOOLNAME
) T4 ON T1.SCHOOLNAME=T4.SCHOOLNAME
/*
学校                   总人数         总分后20%人数    总分比率        数学后20%人数    数学比率        语文后20%人数    语文比率
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
金华四中                 5           1           20%         1           20%         0           0%
金华五中                 5           1           20%         1           20%         2           40%
*/

[zyd_fyl]

呵呵，我在用我的数据库笔算结果。

[zyd_fyl]

苏格兰牧羊鸭

哥，你太神了，结果貌似是对的，我正在一个一个的用sql算。。。

[昵称被占用了]

呵呵。

[xiaoxiangqing]

可以分开几个语句来计算

[zyd_fyl]

[Quote=引用 24 楼 xiaoxiangqing 的回复:]
可以分开几个语句来计算
[/Quote]

嗯，我自己就是按照你的意思做的，分开算的，然后拼接起来，结果不对，改来改去就迷糊了。呵呵。

[zyd_fyl]

苏格兰牧羊鸭

帮忙把比率保留俩位小数，OK了，结果正确。非常感谢。

[zyd_fyl]

自己搞定了，感谢，结贴。

[zhongzhengli]

显示的结果是什么 要解决什么问题