关于SQL2K交叉表SQL语句，急急急！！！在线等待……

[w3k]

请各位帮帮忙啊，谢谢了，下面是数据结构和示例数据

feepaid表字段：
feepdqty cid pid pfeeid
88 060101 06010101 06-01
77 060101 06010101 06-02
66 060101 06010101 06-03


class表字段

cyear cid
2006届 060101

payer表字段
pname pid
李海 06010101

fee表字段
yid feeid feetype
06年学分制 06-01 数学
06年学分制 06-02 英语
06年学分制 06-03 语文
……

=====================================
要实现的功能：

CYear CID PID PName YID 数学 语文 英语 delphi VC
2006届 060101 0600001 李海 06年学分制 0 88 0 0 99

[SQL77]

SELECT 
T.*,
T1.PID,
T2.PNAME,
T3.YID,
MAX(CASE WHEN T3.feetype='数学' THEN feepdqty ELSE 0 END)AS '数学',
MAX(CASE WHEN T3.feetype='语文' THEN feepdqty ELSE 0 END)AS '语文',
MAX(CASE WHEN T3.feetype='英语' THEN feepdqty ELSE 0 END)AS '英语',
MAX(CASE WHEN T3.feetype='DELPHI' THEN feepdqty ELSE 0 END)AS 'DELPHI',
MAX(CASE WHEN T3.feetype='VC' THEN feepdqty ELSE 0 END)AS 'VC'
FROM
class T JOIN feepaid T1 ON T.CID=T1.CID
JOIN payer T2 ON T2.PID=T1.PID
JOIN fee  T3 ON T3.feeid =T1.pfeeid 

GROUP BY 
T.*,
T1.PID,
T2.PNAME,
T3.YID

试试??

[ks_reny]

Delphi,VC 从哪里来的？

[ks_reny]

[Quote=引用 1 楼 SQL77 的回复:]
SQL code
SELECT
T.*,
T1.PID,
T2.PNAME,
T3.YID,
MAX(CASE WHEN T3.feetype='数学' THEN feepdqty ELSE 0 END)AS '数学',
MAX(CASE WHEN T3.feetype='语文' THEN feepdqty ELSE 0 END)AS '语文',
MAX(CASE WHEN T3.feetype='英语' THEN feepdqty ELSE 0 END)AS '英语',
MAX(CASE WHEN T3.feetype='DELPHI' THEN feepdqty ELSE 0 END)AS 'DELPHI',
MAX(CASE WHEN T3.feetype='VC' THEN feepdqty ELSE 0 END)AS 'VC'
…
[/Quote]
group by 后面不能跟*吧。

[sdhdy]

楼主可以参考一下这个：

/*
问题：假设有张学生成绩表(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 end

drop table tb

[dj3688]

...

这表结构设计的,真是无语

[dj3688]

create table feepaid(feepdqty int,cid varchar(32),pid varchar(32),pfeeid varchar(32))
insert into feepaid(feepdqty,cid,pid,pfeeid) values (88,'060101','06010101','06-01')
insert into feepaid(feepdqty,cid,pid,pfeeid) values (77,'060101','06010101','06-02')
insert into feepaid(feepdqty,cid,pid,pfeeid) values (66,'060101','06010101','06-03')

create table class(cyear varchar(32),cid varchar(32))
insert into class(cyear,cid) values ('2006届','060101')

create table payer(pname varchar(32),pid varchar(32))
insert into payer(pname,pid) values ('李海','06010101')

create table fee(yid varchar(32),feeid varchar(32),feetype varchar(32))
insert into fee(yid,feeid,feetype) values ('06年学分制','06-01','数学')
insert into fee(yid,feeid,feetype) values ('06年学分制','06-02','英语')
insert into fee(yid,feeid,feetype) values ('06年学分制','06-03','语文')

select a.cyear,a.cid,b.pid,b.pname,c.yid,'数学'=
case c.feetype when '数学' then z.feepdqty end ,
'语文'=case c.feetype when '语文' then z.feepdqty end,
'英语'=case c.feetype when '英语' then z.feepdqty end,
'DELPHI'=case c.feetype when 'DELPHI' then z.feepdqty end,
'VC'=case c.feetype when 'VC' then z.feepdqty end
from feepaid  z 
inner join class a
on z.cid=a.cid
inner join payer b
on z.pid=b.pid
inner join fee c
on z.pfeeid=c.feeid
group by a.cyear,a.cid,b.pid,b.pname,c.yid,c.feetype,z.feepdqty

/*
cyear                            cid                              pid                              pname                            yid                              数学          语文          英语          DELPHI      VC          
-------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- ----------- ----------- ----------- ----------- ----------- 
2006届                            060101                           06010101                         李海                               06年学分制                           88          NULL        NULL        NULL        NULL
2006届                            060101                           06010101                         李海                               06年学分制                           NULL        NULL        77          NULL        NULL
2006届                            060101                           06010101                         李海                               06年学分制                           NULL        66          NULL        NULL        NULL

（所影响的行数为 3 行）
*/

[dj3688]

select a.cyear,a.cid,b.pid,b.pname,c.yid,
sum(case c.feetype when '数学' then z.feepdqty else 0 end) as '数学',
sum(case c.feetype when '语文' then z.feepdqty else 0 end) as '语文',
sum(case c.feetype when '英语' then z.feepdqty else 0 end) as '英语',
sum(case c.feetype when 'DELPHI' then z.feepdqty else 0 end) as 'DELPHI',
sum(case c.feetype when 'VC' then z.feepdqty else 0 end) as 'VC'
from feepaid  z 
inner join class a
on z.cid=a.cid
inner join payer b
on z.pid=b.pid
inner join fee c
on z.pfeeid=c.feeid
group by a.cyear,a.cid,b.pid,b.pname,c.yid
/*
cyear                            cid                              pid                              pname                            yid                              数学          语文          英语          DELPHI      VC          
-------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- ----------- ----------- ----------- ----------- ----------- 
2006届                            060101                           06010101                         李海                               06年学分制                           88          66          77          0           0

（所影响的行数为 1 行）
*/

[lbcleo]

up

[无心雨云]

学习

[w3k]

fee表字段
yid feeid feetype
06年学分制 06-01 数学
06年学分制 06-02 英语
06年学分制 06-03 语文
……


===================
忘记补充说明了 ，后面的省略号是说 科目是动态增加的 以 YID来区别每学年的科目

CYear CID PID PName YID 数学 语文 英语 delphi VC
2006届 060101 0600001 李海 06年学分制 0 88 0 0 99

这里的DELPHI VC是假设这个学年选秀的科目动态增加的：）

我所需的结果是动态生成 每个选年的科目分数 不管有没有选这门学科

[sdhdy]

参考一下4楼的，看明白了，自然你的就出来了。

[w3k]

结贴：）

[dj3688]

[Quote=引用 11 楼 sdhdy 的回复:]
参考一下4楼的，看明白了，自然你的就出来了。
[/Quote]