关于sql统计的一个问题

Question

请教一个关于sql统计的问题。已有表chengji为
id  fenlei    yi     er      san
-----------------------------------
1    游泳    一班   四班     一班
2    跳高    二班   一班     三班
3    跳水    一班   三班     四班要统计为：
banji     yi        er       san
-------------------------------------
一班：    2         1         1
二班：    1         0         0
三班：    0         1         1
...      ...       ...       ...要怎样实现啊？

Answer 1

/*
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb    ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb

Answer 2

select banji,
yi=sum(case when tmp=1 then 1 else 0 end),
er=sum(case when tmp=2 then 1 else 0 end),
san=sum(case when tmp=3 then 1 else 0 end)
from
(select yi as banji,1 as tmp
from Chengji
union all
select er,2 as tmp
union all
select san,3 as tmp) A
group by banji

Answer 3

-------------------------------------------
--  Author : liangCK 小梁
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-07-13 15:36:47
-------------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,fenlei VARCHAR(4),yi VARCHAR(4),er VARCHAR(4),san VARCHAR(4))
INSERT INTO @T
SELECT 1,'游泳','一班','四班','一班' UNION ALL
SELECT 2,'跳高','二班','一班','三班' UNION ALL
SELECT 3,'跳水','一班','三班','四班'--SQL查询如下:SELECT
    banji,
    SUM(CASE WHEN flag='yi' THEN 1 ELSE 0 END) AS yi,
    SUM(CASE WHEN flag='er' THEN 1 ELSE 0 END) AS er,
    SUM(CASE WHEN flag='san' THEN 1 ELSE 0 END) AS san
FROM (
    SELECT yi AS banji,'yi' AS flag FROM @T
    UNION ALL
    SELECT er,'er' FROM @T
    UNION ALL
    SELECT san,'san' FROM @T
) AS A
GROUP BY banji/*
banji yi          er          san
----- ----------- ----------- -----------
二班    1           0           0
三班    0           1           1
四班    0           1           1
一班    2           1           1(4 row(s) affected)*/

Answer 4

select 
banji='一班',
yi=SUM(case when yi='一班' then 1 else 0 end ),
ER=SUM(case when er='一班' then 1 else 0 end ),
san=SUM(case when san='一班' then 1 else 0 end ) 
from chengji union all
select 
banji='二班',
yi=SUM(case when yi='二班' then 1 else 0 end ),
ER=SUM(case when er='二班' then 1 else 0 end ),
san=SUM(case when san='二班' then 1 else 0 end ) 
from chengji union all
select 
banji='三班',
yi=SUM(case when yi='三班' then 1 else 0 end ),
ER=SUM(case when er='三班' then 1 else 0 end ),
san=SUM(case when san='三班' then 1 else 0 end ) 
from chengji union all
select 
banji='四班',
yi=SUM(case when yi='四班' then 1 else 0 end ),
ER=SUM(case when er='四班' then 1 else 0 end ),
san=SUM(case when san='四班' then 1 else 0 end ) 
from chengji 

Answer 5

create table chengji(id int,fenlei varchar(10), yi varchar(10),er varchar(10),san varchar(10))
insert into chengji select 1,'游泳','一班','四班','一班'
insert into chengji select 2,'跳高','二班','一班','三班'
insert into chengji select 3,'跳水','一班','三班','四班'
GO
select banji,
yi=sum(case when tmp=1 then 1 else 0 end),
er=sum(case when tmp=2 then 1 else 0 end),
san=sum(case when tmp=3 then 1 else 0 end)
from
(select yi as banji,1 as tmp
from Chengji
union all
select er,2 as tmp
from Chengji
union all
select san,3 as tmp
from Chengji) A
group by banji
/*
banji      yi          er          san         
---------- ----------- ----------- ----------- 
一班         2           1           1
二班         1           0           0
三班         0           1           1
四班         0           1           1
*/drop table Chengji

Answer 6

DECLARE @TB TABLE([id] INT, [fenlei] NVARCHAR(2), [yi] NVARCHAR(2), [er] NVARCHAR(2), [san] NVARCHAR(2))
INSERT @TB 
SELECT 1, N'游泳', N'一班', N'四班', N'一班' UNION ALL 
SELECT 2, N'跳高', N'二班', N'一班', N'三班' UNION ALL 
SELECT 3, N'跳水', N'一班', N'三班', N'四班'SELECT yi,
SUM(CASE WHEN [id]=1 THEN 1 ELSE 0 END) AS yi,
SUM(CASE WHEN [id]=2 THEN 1 ELSE 0 END) AS er,
SUM(CASE WHEN [id]=3 THEN 1 ELSE 0 END) AS san
FROM (
SELECT id,[fenlei],[yi] FROM @TB 
UNION ALL
SELECT id,[fenlei],[er] FROM @TB 
UNION ALL
SELECT id,[fenlei],[san] FROM @TB 
) T
GROUP BY [yi]
/*
yi   yi          er          san
---- ----------- ----------- -----------
二班   0           1           0
三班   0           1           1
四班   1           0           1
一班   2           1           1
*/

Answer 7

create  TABLE chengji (id INT,fenlei VARCHAR(4),yi VARCHAR(4),er VARCHAR(4),san VARCHAR(4))
INSERT INTO chengji
SELECT 1,'游泳','一班','四班','一班' UNION ALL
SELECT 2,'跳高','二班','一班','三班' UNION ALL
SELECT 3,'跳水','一班','三班','四班'select 
banji='一班',
yi=SUM(case when yi='一班' then 1 else 0 end ),
ER=SUM(case when er='一班' then 1 else 0 end ),
san=SUM(case when san='一班' then 1 else 0 end ) 
from chengji union all
select 
banji='二班',
yi=SUM(case when yi='二班' then 1 else 0 end ),
ER=SUM(case when er='二班' then 1 else 0 end ),
san=SUM(case when san='二班' then 1 else 0 end ) 
from chengji union all
select 
banji='三班',
yi=SUM(case when yi='三班' then 1 else 0 end ),
ER=SUM(case when er='三班' then 1 else 0 end ),
san=SUM(case when san='三班' then 1 else 0 end ) 
from chengji union all
select 
banji='四班',
yi=SUM(case when yi='四班' then 1 else 0 end ),
ER=SUM(case when er='四班' then 1 else 0 end ),
san=SUM(case when san='四班' then 1 else 0 end ) 
from chengji (3 行受影响)
banji yi          ER          san
----- ----------- ----------- -----------
一班    2           1           1
二班    1           0           0
三班    0           1           1
四班    0           1           1

Answer 8

--try:
select a.banji,
yi=sum(case when yi=a.banji then 1 else 0 end),
er=sum(case when er=a.banji then 1 else 0 end),
san=sum(case when san=a.banji then 1 else 0 end)
from (select distinct banji from 班级表) a,chengji b 
group by a.banji
order by a.banji

Answer 9

DECLARE @T TABLE (id INT,fenlei VARCHAR(4),yi VARCHAR(4),er VARCHAR(4),san VARCHAR(4))
INSERT INTO @T
SELECT 1,'游泳','一班','四班','一班' UNION ALL
SELECT 2,'跳高','二班','一班','三班' UNION ALL
SELECT 3,'跳水','一班','三班','四班'select * from 
(
   select  班级,级别 from @t
   unpivot ( 班级 for 级别 in(yi,er,san))u
 )m
pivot
(
  count(级别) for 级别 in( yi,er,san)
) p/*
banji yi          er          san
----- ----------- ----------- -----------
二班    1           0           0
三班    0           1           1
四班    0           1           1
一班    2           1           1(4 row(s) affected)*/

Answer 10

--> 生成测试数据: @T
DECLARE @T TABLE (id INT,fenlei VARCHAR(4),yi VARCHAR(4),er VARCHAR(4),san VARCHAR(4))
INSERT INTO @T
SELECT 1,'游泳','一班','四班','一班' UNION ALL
SELECT 2,'跳高','二班','一班','三班' UNION ALL
SELECT 3,'跳水','一班','三班','四班'--SQL查询如下:SELECT
    banji,
    SUM(CASE WHEN flag='yi' THEN 1 ELSE 0 END) AS yi,
    SUM(CASE WHEN flag='er' THEN 1 ELSE 0 END) AS er,
    SUM(CASE WHEN flag='san' THEN 1 ELSE 0 END) AS san
FROM (
    SELECT yi AS banji,'yi' AS flag FROM @T
    UNION ALL
    SELECT er,'er' FROM @T
    UNION ALL
    SELECT san,'san' FROM @T
) AS A
GROUP BY banji/*
banji yi          er          san
----- ----------- ----------- -----------
二班    1           0           0
三班    0           1           1
四班    0           1           1
一班    2           1           1(4 row(s) affected)*/

Answer 11

select
    b.bj,
    yi= sum(case a.yi  when b.bj then 1 else 0 end),
    er= sum(case a.er  when b.bj then 1 else 0 end),
    san=sum(case a.san when b.bj then 1 else 0 end)
from
    tab a,(select distinct yi  as bj from tab 
           union 
           select distinct er  as bj from tab
           union 
           select distinct san as bj from tab) b
group by
    b.bj

Answer 12

首先谢谢大家帮助，不好意思刚漏掉一个需求，就是名次可能有并列的，原表内容为：
id  fenlei       yi        er        san 
---------------------------------------------- 
1    游泳      一班,二班     四班         一班 
2    跳高         二班     一班,二班      三班 
3    跳水         一班       三班         四班 拜托大家了~~~

Answer 13

-- 一樣的原理，只是先拆分一下列create table chengji(id int,fenlei varchar(10), yi varchar(10),er varchar(10),san varchar(10))
insert into chengji select 1,'游泳','一班,二班','四班','一班'
insert into chengji select 2,'跳高','二班','一班,二班','三班'
insert into chengji select 3,'跳水','一班','三班','四班'
GOselect   banci,
yi=sum(case when tmp=1 then 1 else 0 end),
er=sum(case when tmp=2 then 1 else 0 end),
san=sum(case when tmp=3 then 1 else 0 end)
from
(
SELECT
    SUBSTRING(A.yi,B.number,CHARINDEX(',',A.yi+',',B.number)-B.number) AS [banci],
    A.id,
    1 as tmp
FROM  chengji as  A
    JOIN master.dbo.spt_values AS B
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.yi)
            AND SUBSTRING(','+A.yi,B.number,1)=','UNION ALLSELECT
    SUBSTRING(A.er,B.number,CHARINDEX(',',A.er+',',B.number)-B.number) AS [banci],
    A.id,
    2 as tmp
FROM  chengji as  A
    JOIN master.dbo.spt_values AS B
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.er)
            AND SUBSTRING(','+A.er,B.number,1)=','UNION ALLSELECT
    SUBSTRING(A.san,B.number,CHARINDEX(',',A.san+',',B.number)-B.number) AS [banci],
    A.id,
    3 as tmp
FROM  chengji as  A
    JOIN master.dbo.spt_values AS B
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.san)
            AND SUBSTRING(','+A.san,B.number,1)=','
) XX
group by banci/*
banci      yi          er          san         
---------- ----------- ----------- ----------- 
一班         2           1           1
二班         2           1           0
三班         0           1           1
四班         0           1           1
*/
drop table chengji