求为查询结果增加合计行的sql

Question

select name 
,max(case when classid=1 then classvalue else 0 end)语文 
,max(case when classid=1 then rank else 0 end)排名 
,max(case when classid=2 then classvalue else 0 end)数学 
,max(case when classid=2 then rank else 0 end)排名 
,max(case when classid=3 then classvalue else 0 end)英语 
,max(case when classid=3 then rank else 0 end)排名 
from( 
select *,rank=RANK()over(partition by classname order by classvalue desc) from( 
select a.userid,b.name,a.classid,c.classname,a.classvalue 
from testRecord a,testUser b,testClass c 
where a.userid=b.id and a.classid=c.id)a)b 
group by name,userid order by userid 
union all
select '合计' as name 
,sum(case when classid=1 then classvalue else 0 end)语文 
,sum(case when classid=1 then 1 else 0 end)排名 
,sum(case when classid=2 then classvalue else 0 end)数学 
,sum(case when classid=2 then 1 else 0 end)排名 
,sum(case when classid=3 then classvalue else 0 end)英语 
,sum(case when classid=3 then 1 else 0 end)排名 
from ( 
select a.userid,b.name,a.classid,c.classname,a.classvalue 
from testRecord a,testUser b,testClass c 
where a.userid=b.id and a.classid=c.id
) a

Answer 1

有点小问题select name 
,max(case when classid=1 then classvalue else 0 end)语文 
,max(case when classid=1 then rank else 0 end)排名 
,max(case when classid=2 then classvalue else 0 end)数学 
,max(case when classid=2 then rank else 0 end)排名 
,max(case when classid=3 then classvalue else 0 end)英语 
,max(case when classid=3 then rank else 0 end)排名 
from( 
select *,rank=RANK()over(partition by classname order by classvalue desc) from( 
select a.userid,b.name,a.classid,c.classname,a.classvalue 
from testRecord a,testUser b,testClass c 
where a.userid=b.id and a.classid=c.id)a)b 
group by name,userid 
union all
select '合计' as name 
,sum(case when classid=1 then classvalue else 0 end)语文 
,sum(case when classid=1 then 1 else 0 end)排名 
,sum(case when classid=2 then classvalue else 0 end)数学 
,sum(case when classid=2 then 1 else 0 end)排名 
,sum(case when classid=3 then classvalue else 0 end)英语 
,sum(case when classid=3 then 1 else 0 end)排名 
from ( 
select a.userid,b.name,a.classid,c.classname,a.classvalue 
from testRecord a,testUser b,testClass c 
where a.userid=b.id and a.classid=c.id
) a--结果
name       语文          排名                   数学          排名                   英语          排名
---------- ----------- -------------------- ----------- -------------------- ----------- --------------------
张三         80          1                    50          1                    20          2
李四         70          2                    40          2                    10          3
王五         60          3                    30          3                    90          1
合计         210         3                    120         3                    120         3

Answer 2

我想知道2楼的rollup怎么出这个种效果

Answer 3

select case when grouping(name)=1 then '合计'else name end as name 
,max(case when classid=1 then classvalue else 0 end)语文 
,max(case when classid=1 then rank else 0 end)排名 
,max(case when classid=2 then classvalue else 0 end)数学 
,max(case when classid=2 then rank else 0 end)排名 
,max(case when classid=3 then classvalue else 0 end)英语 
,max(case when classid=3 then rank else 0 end)排名 
from( 
select *,rank=RANK()over(partition by classname order by classvalue desc) from( 
select a.userid,b.name,a.classid,c.classname,a.classvalue 
from testRecord a,testUser b,testClass c 
where a.userid=b.id and a.classid=c.id)a)b 
group by name
with rollup--结果
name       语文          排名                   数学          排名                   英语          排名
---------- ----------- -------------------- ----------- -------------------- ----------- --------------------
李四         70          2                    40          2                    10          3
王五         60          3                    30          3                    90          1
张三         80          1                    50          1                    20          2
合计         80          3                    50          3                    90          3(4 行受影响)

Answer 4

学习一下,原来这是在SQL 2005中,我还以为在2000中,我在2000中执行时,提示不识别RANK().
这个函数我在Oracle中见过是分析函数,想不到2005功能强大了.
我对2005不熟悉,以前只用2000. 

Answer 5

前面的语句我没修改，用你的：(select name
,max(case when classid=1 then classvalue else 0 end)语文
,max(case when classid=1 then rank else 0 end)排名
,max(case when classid=2 then classvalue else 0 end)数学
,max(case when classid=2 then rank else 0 end)排名
,max(case when classid=3 then classvalue else 0 end)英语
,max(case when classid=3 then rank else 0 end)排名
from(
select *,rank=RANK()over(partition by classname order by classvalue desc) from(
select a.userid,b.name,a.classid,c.classname,a.classvalue
from testRecord a,testUser b,testClass c
where a.userid=b.id and a.classid=c.id)a)b
group by name,userid order by userid)
union
(select a.a,b.b,c.c,a.a,d.d,a.a
from
(select count(userid) as a from testuser) a,
select count(classid) as b from testclass where classid=1) b,
select count(classid) as c from testclass where classid=2) c,
select count(classid) as d from testclass where classid=3) d)