挑战交叉表查询！！---高分求1个关于交叉表查询的SQL语句（低手别进啊~ ：）） 分数不够可以再加~~

[sonymanzzzzzz]

SQL-SERVER中用到如下3张表：

表1
学号 姓名 系别
001 AA 数学系
002 BB 物理系
003 CC 数学系
004 DD 数学系
005 EE 化学系
。
。
。

表2
学号 课程代号 成绩 备注
001 111111 80
001 333333 70
002 222222 60
003 333333 病假
004 111111 60 补考
。
。
。

表3
课程代号 课程名称
111111 邓论
222222 计算机基础
333333 高数1
。
。
。

此查询想实现如下报表：
先选择出系别，然后查询出系成绩单汇总。
即在语句中加如条件：
表1.系别='数学系'

排序要求：首先按课程代号大小排序(降序)；然后是按学号大小把学生排序(升序)，
即在语句中加入条件：
order by 表3.课程代号 desc,表1.学号

执行此SQL语句后应该能生成如下的报表即可：

数学系成绩单汇总

学号 | 姓名 | （课程名称）| 邓论 | 计算机| 高数1 | 。。
001 | aa | ......... | 80 | 70 | | 。
003 | cc | | | | | 。
004 | dd | ........... | 60 | | | 。
。
。
。
。
请大虾们尽量给出详细代码。跪谢了先~~~~~ ：~）
大出血啊！！此问题合计可给300分~~~~~~~~~~~~~~~~~~~~

[sonymanzzzzzz]

其他高手也能参与啊!!!!!!快来抢分吧!!!!!!! :)

[lalakid]

哈哈，老大真有意思
逗死我了
挖哈哈
老大来抢分了
挖哈哈……

[zjcxc]

要求写完没有啊? 写完了就放分

[zjcxc]

--示例

--示例表
create table 表1(学号 varchar(10),姓名 varchar(10),系别 varchar(10))
insert 表1 select '001','AA','数学系'
union all select '002','BB','物理系'
union all select '003','CC','数学系'
union all select '004','DD','数学系'
union all select '005','EE','化学系'

create table 表2(学号 varchar(10),课程代号 varchar(10),成绩 int,备注 varchar(10))
insert 表2 select '001','111111','80',''
union all select '001','333333','70',''
union all select '002','222222','60',''
union all select '003','333333','0' ,'病假'
union all select '004','111111','60','补考'

create table 表3(课程代号 varchar(10),课程名称 varchar(10))
insert 表3 select '111111','邓论'
union all select '222222','计算机基础'
union all select '333333','高数1'
go

--查询处理的存储过程
create proc p_qry
@系别 varchar(10),
@tj varchar(1000)='' --查询条件,不要指定 where
as
set nocount on
declare @s varchar(8000)
select @s=''
,@tj=case when isnull(@tj,'')='' then '' else ' and '+@tj end
select @s=@s+',['+a.课程名称+']=max(case 表2.课程代号 when '''
+a.课程代号+''' then cast(表2.成绩 as varchar(8)) else '''' end)' --没有成绩的显示为空
from 表3 a,(
select bb.课程代号 from 表1 aa,表2 bb
where aa.学号=bb.学号 and aa.系别=@系别
group by bb.课程代号
)b where a.课程代号=b.课程代号
exec('
select 表1.学号,表1.姓名'+@s+'
from 表1,表2
where 表1.学号=表2.学号 and 表1.系别='''+@系别+''''+@tj+'
group by 表1.学号,表1.姓名
')
go

--调用
exec p_qry '数学系','表1.学号 like ''00%'''
go

--删除测试
drop table 表1,表2,表3
drop proc p_qry

/*--测试结果

学号 姓名 邓论 高数1
---------- ---------- -------- --------
001 AA 80 70
003 CC 0
004 DD 60
--*/

[xjp6688]

看来分都得给邹大哥了，厉害，哈哈！

[zjcxc]

--示例

--示例表
create table 表1(学号 varchar(10),姓名 varchar(10),系别 varchar(10))
insert 表1 select '001','AA','数学系'
union all select '002','BB','物理系'
union all select '003','CC','数学系'
union all select '004','DD','数学系'
union all select '005','EE','化学系'

create table 表2(学号 varchar(10),课程代号 varchar(10),成绩 int,备注 varchar(10))
insert 表2 select '001','111111','80',''
union all select '001','333333','70',''
union all select '002','222222','60',''
union all select '003','333333','0' ,'病假'
union all select '004','111111','60','补考'

create table 表3(课程代号 varchar(10),课程名称 varchar(10))
insert 表3 select '111111','邓论'
union all select '222222','计算机基础'
union all select '333333','高数1'
go

--查询处理的存储过程
create proc p_qry
@系别 varchar(10)
as
set nocount on
declare @s varchar(8000)
set @s=''
select @s=@s+',['+a.课程名称+']=max(case 课程代号 when '''+a.课程代号+''' then 成绩 else 0 end)'
from 表3 a,(
select bb.课程代号 from 表1 aa,表2 bb
where aa.学号=bb.学号 and aa.系别=@系别
group by bb.课程代号
)b where a.课程代号=b.课程代号
exec('
select a.学号,a.姓名'+@s+'
from 表1 a,表2 b
where a.学号=b.学号 and a.系别='''+@系别+'''
group by a.学号,a.姓名
')
go

--调用
exec p_qry '数学系'
go

--删除测试
drop table 表1,表2,表3
drop proc p_qry

/*--测试结果

学号 姓名 邓论 高数1
---------- ---------- ----------- -----------
001 AA 80 70
003 CC 0 0
004 DD 60 0
--*/

[zjcxc]

我的300分

[victorycyz]

楼主，论坛是大家的，不是你自己的，你没有权利指定谁进来谁不能进来。

邹建不厚道，1分都不留下。

[Andy__Huang]

呵呵﹐路過望一下...

[dennier]

为什么“002“选的“计算机基础”没有记录？

[tinghuyang]

up

[sonymanzzzzzz]

邹大哥,我这里实际测试还是不行啊！，能否告知你的联系方式：QQ或者什么的，我把数据库发给你，帮我调试1下，我这里现在总是报下面的错误，不知道是什么原因（有钢琴系和作曲系）
服务器: 消息 103，级别 15，状态 7，行 2
以 '钢
from uStudBaseInfo,uSelectClass
where uStudBaseInfo.学号=uSelectClass.学号 and uStudBaseInfo.系别='作曲系'
group by uStudBaseInfo.学号' 开头的 标识符 太长。最大长度为 128。
服务器: 消息 105，级别 15，状态 1，行 2
字符串 '钢
from uStudBaseInfo,uSelectClass
where uStudBaseInfo.学号=uSelectClass.学号 and uStudBaseInfo.系别='作曲系'
group by uStudBaseInfo.学号,' 之前有未闭合的引号。
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: '钢
from uStudBaseInfo,uSelectClass
where uStudBaseInfo.学号=uSelectClass.学号 and uStudBaseInfo.系别='作曲系'
group by uStudBaseInfo.学号' 附近有语法错误。

[fengyun14]

邹建一出来，大家还在这里干嘛呢？

对了，关于您的这个论题，在一本权威书里有专门的一章作了讨论：
《SQL SERVER 7 编程技术内幕》

[zjcxc]

你把数据库发到: [e-mail]
如果数据库超过45M,则发到: [e-mail]

[sonymanzzzzzz]

[e-mail]
附件数据库是5M，没问题吧？

[sonymanzzzzzz]

原来数据库文件用WINRAR有那么高的压缩比啊！！！！！ ：）
发完了，谢谢帮忙啊！！！！！！！

[sonymanzzzzzz]

邹大哥，数据库已经发到你的邮箱里了，帮我看看呀~~~~~

[zjcxc]

但我并没有收到啊.

你再发一次吧,发了后发个信息通知我是发到那个邮箱的

[zjcxc]

--1.你发过来的数据库中的数据本身有问题

update uCoursesInfo set 课程名称=replace(课程名称,char(13)+char(10),'')
where charindex(char(13)+char(10),课程名称)>0
update uCoursesInfo set 课程名称=replace(课程名称,char(13),'')
where charindex(char(13),课程名称)>0


--2.出错是因为生成的SQL语句超长被截断导致,改用下面的存储过程就OK了

[zjcxc]

--查询的存储过程
create proc p_qry
@系别 varchar(10),
@tj varchar(1000)='' --查询条件,不要指定 where
as
set nocount on
declare @sqlhead nvarchar(4000),@sqlend nvarchar(4000)
,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
,@i int,@ic varchar(20)

--生成数据处理临时表
select id=identity(int,0,1),gid=0
,a=',['+rtrim(a.课程名称)+']=max(case uSelectClass.课程代号 when '''
+a.课程代号+''' then cast(uSelectClass.成绩 as varchar(8)) else '''' end)'
into # from uCoursesInfo a,(
select bb.课程代号 from uStudBaseInfo aa,uSelectClass bb
where aa.学号=bb.学号 and aa.系别=@系别
group by bb.课程代号
)b where a.课程代号=b.课程代号


--判断需要多少个变量来处理
select @i=max(len(a)) from #
set @i=3800/@i

--分组临时表
update # set gid=id/@i
select @i=max(gid) from #

--生成数据处理语句
select @tj=case when isnull(@tj,'')='' then '' else ' and '+replace(@tj,'''','''''') end
,@sqlhead='''select uStudBaseInfo.学号,uStudBaseInfo.姓名'''
,@sqlend=''' from uStudBaseInfo,uSelectClass
where uStudBaseInfo.学号=uSelectClass.学号 and uStudBaseInfo.系别='''''+@系别+''''''+@tj+'
group by uStudBaseInfo.学号,uStudBaseInfo.姓名
'''
,@sql1='',@sql2='',@sql3='',@sql4=''

while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
,@sql1=',@'+@ic+' nvarchar(4000)'+@sql1
,@sql2=',@'+@ic+'='''''+@sql2
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic
+char(13)+@sql3
,@sql4='+@'+@ic+@sql4

select @sql1=stuff(@sql1,1,1,'')
,@sql2=stuff(@sql2,1,1,'')
,@sql3=left(@sql3,len(@sql3)-1)
,@sql4=stuff(@sql4,1,1,'')

--执行
exec('
declare '+@sql1+'
select '+@sql2+'
'+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')
')
go

--调用
exec p_qry '作曲系'--,'uStudBaseInfo.学号 like ''11%'''
go

--删除测试
drop proc p_qry