关于SQL的统计问题

Question

create table A
(
stuName varchar(10),
stuClass varchar(10),
stuScroe float,
)insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)--假设姓名[stuName]是唯一的，就是说相同名字的就是同一个人
--学科[stuClass]也是唯一的，就是说相同学科的就是同一个学科select stuName,stuClass,sum(stuScroe) from A group by stuClass,stuName根据最后一条SQL语句可以得到每个学生每个科目的成绩，但是我想要的结果是通过一条SQL，查出一个学生的多学科的成绩在一条记录里面，例如：
stuName    数学    语文    英语    总计
张三       80      70      60      210
李四       90      80      null    170
王五       95      null    85      170

Answer 1

http://blog.csdn.net/lihan6415151528/archive/2009/02/01/3856341.aspx

Answer 2

declare @sql varchar(1000)
set @sql='select stuname'
select @sql=@sqle+',['+stuclass+']=max(case stuclass when '''+stuclass+''' then stuscore else null end)'
from (select distinct stuclass from a)a
set @sql=@sql+',sum(stuscore) from a group by stuname'
exec(@sql)

Answer 3

create table A
(
    stuName varchar(10),
    stuClass varchar(10),
    stuScroe float,
)insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)--假设姓名[stuName]是唯一的，就是说相同名字的就是同一个人
--学科[stuClass]也是唯一的，就是说相同学科的就是同一个学科
;with f as
(select stuName,stuClass,sum(stuScroe) as stuScroe  from A group by stuClass,stuName)
select stuName as 姓名 ,
  max(case stuClass when '语文' then stuScroe else 0 end) 语文,
  max(case stuClass when '数学' then stuScroe else 0 end) 数学,
  max(case stuClass when '物理' then stuScroe else 0 end) 物理
from f
group by stuName
drop table A
/*姓名         语文                     数学                     物理
---------- ---------------------- ---------------------- ----------------------
李四         80                     90                     0
王五         0                      95                     0
张三         70                     80                     0(3 行受影响)
*/

Answer 4

if object_id('a') is not null
  drop table a 
go
create table A
(
    stuName varchar(10),
    stuClass varchar(10),
    stuScroe float,
)insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)select stuName,
max(case stuClass when '数学' then stuScroe end) '数学' ,
max(case stuClass when '语文' then stuScroe end) '语文' ,
max(case stuClass when '英语' then stuScroe end) '英语' ,
sum(stuScroe) 统计
from A
group by stuName
/
stuName    数学                     语文                     英语                     统计
---------- ---------------------- ---------------------- ---------------------- ----------------------
李四         90                     80                     NULL                   170
王五         95                     NULL                   85                     180
张三         80                     70                     60                     210(3 行受影响)
*/

Answer 5

http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

Answer 6

if object_id('a') is not null drop table a
go 
create table A
(
    stuName varchar(10),
    stuClass varchar(10),
    stuScroe float
)insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)
declare @str varchar(400)
set @str=''select @str=@str+','+stuClass+'=max(case when stuClass='''+stuClass+''' then stuScroe else 0 end)'
from (select distinct stuClass from a)texec('select stuName ' +@str+' from a group by  stuName ')stuName    数学                     英语                     语文
---------- ---------------------- ---------------------- ----------------------
李四         90                     0                      80
王五         95                     85                     0
张三         80                     60                     70(3 行受影响)

Answer 7

--05select stuName,数学,语文,英语,总计
from (select *,sum(stuScroe)over(partition by stuName) 总计 from a) a
pivot(max(stuScroe) for stuClass in(数学,语文,英语))b
/*
stuName    数学                     语文                     英语                     统计
---------- ---------------------- ---------------------- ---------------------- ----------------------
李四         90                     80                     NULL                   170
王五         95                     NULL                   85                     180
张三         80                     70                     60                     210(3 行受影响)
*/

Answer 8

如果我现在想得到所有学科的总和，SQL又该怎么写呢

Answer 9

不像五楼的，结果集是在最后添加一行，就像这样的
stuName    数学    语文    英语    总计
张三        80     70     60     210 
李四        90     80     null   170 
王五        95     null   85     170 
总计        265    150    145    265

Answer 10

if object_id('a') is not null
  drop table a 
go
create table A
(
    stuName varchar(10),
    stuClass varchar(10),
    stuScroe float,
)insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)select isnull(stuName,'总计') stuName,
    sum(case stuClass when '数学' then stuScroe end) '数学' ,
    sum(case stuClass when '语文' then stuScroe end) '语文' ,
    sum(case stuClass when '英语' then stuScroe end) '英语' ,
    sum(stuScroe) '总计'
from A
group by stuName
with rollup/*
stuName    数学                     语文                     英语                     总计
---------- ---------------------- ---------------------- ---------------------- ----------------------
李四         90                     80                     NULL                   170
王五         95                     NULL                   85                     180
张三         80                     70                     60                     210
总计         265                    150                    145                    560(4 行受影响)
*/

Answer 11

create table A
(
    stuName varchar(10),
    stuClass varchar(10),
    stuScroe float,
)insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)declare @sql varchar(2000),@sql1 varchar(1000)
set @sql='select stuname'
set @sql1='select ''总计'''
select @sql=@sql+',['+stuclass+']=max(case stuclass when '''+stuclass+''' then stuScroe else null end)',
@sql1=@sql1+',['+stuclass+']=sum(case stuclass when '''+stuclass+''' then stuScroe else null end)'
from (select distinct stuclass from a)a
set @sql=@sql+',总计=sum(stuScroe) from a group by stuname'
set @sql1=@sql1+',总计=sum(stuScroe) from a'
exec(@sql+' union all '+@sql1)

Answer 12

if object_id('a') is not null
  drop table a 
go
create table A
(
    stuName varchar(10),
    stuClass varchar(10),
    stuScroe float,
)insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)
;with f as
(select stuName,
    max(case stuClass when '数学' then stuScroe end) '数学' ,
    max(case stuClass when '语文' then stuScroe end) '语文' ,
    max(case stuClass when '英语' then stuScroe end) '英语' ,
    sum(stuScroe) 统计
from A
group by stuName)--select sum(数学),sum(语文),sum(英语),sum(统计) from f 
select 
  case when grouping(stuName)=1 then '合计' else cast(stuName as varchar) end stuName, sum(数学),sum(语文),sum(英语),sum(统计)
from 
  f 
group by 
  stuName 
with rollup 
/*stuName                                                                                             
------------------------------ ---------------------- ---------------------- ---------------------- ----------------------
李四                             90                     80                     NULL                   170
王五                             95                     NULL                   85                     180
张三                             80                     70                     60                     210
合计                             265                    150                    145                    560
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)*/

Answer 13

--or
declare @sql varchar(2000)
set @sql='select isnull(stuname,''合计'')'
select @sql=@sql+',['+stuclass+']=max(case stuclass when '''+stuclass+''' then stuScroe else null end)'
from (select distinct stuclass from a)a
set @sql=@sql+',总计=sum(stuScroe) from a group by stuname with rollup'
exec(@sql)

Answer 14

if object_id('a') is not null drop table a
go 
create table A
(
    stuName varchar(10),
    stuClass varchar(10),
    stuScroe float
)insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)
declare @str varchar(400)
set @str=''select @str=@str+','+stuClass+'=max(case when stuClass='''+stuClass+''' then stuScroe else 0 end)'
from (select distinct stuClass from a)t
set @str='select isnull(stuName,''合计'') ' +@str+',总分=sum(stuScroe) from a group by  stuName with rollup'exec(@str)
           数学                     英语                     语文                     总分
---------- ---------------------- ---------------------- ---------------------- ----------------------
李四         90                     0                      80                     170
王五         95                     85                     0                      180
张三         80                     60                     70                     210
合计         95                     85                     80                     560(4 行受影响)