2.7w+
社区成员
挑战交叉表查询！！---高分求1个关于交叉表查询的SQL语句（低手别进啊~ ：）） 分数不够可以再加~~
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分~~~~~~~~~~~~~~~~~~~~
表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分~~~~~~~~~~~~~~~~~~~~
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
zjcxc 2004-10-22
--查询的存储过程
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
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
zjcxc 2004-10-22
--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了
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 2004-10-21
但我并没有收到啊.
你再发一次吧,发了后发个信息通知我是发到那个邮箱的
你再发一次吧,发了后发个信息通知我是发到那个邮箱的
sonymanzzzzzz 2004-10-21
邹大哥，数据库已经发到你的邮箱里了，帮我看看呀~~~~~
sonymanzzzzzz 2004-10-21
原来数据库文件用WINRAR有那么高的压缩比啊！！！！！ ：）
发完了，谢谢帮忙啊！！！！！！！
发完了，谢谢帮忙啊！！！！！！！
sonymanzzzzzz 2004-10-21
zjcxc@21cn.net
附件数据库是5M，没问题吧？
附件数据库是5M，没问题吧？
zjcxc 2004-10-18
你把数据库发到: zjcxc@21cn.net
如果数据库超过45M,则发到: zjcxc@163.com
如果数据库超过45M,则发到: zjcxc@163.com
fengyun14 2004-10-18
邹建一出来，大家还在这里干嘛呢？
对了，关于您的这个论题，在一本权威书里有专门的一章作了讨论：
《SQL SERVER 7 编程技术内幕》
对了，关于您的这个论题，在一本权威书里有专门的一章作了讨论：
《SQL SERVER 7 编程技术内幕》
sonymanzzzzzz 2004-10-18
邹大哥,我这里实际测试还是不行啊！，能否告知你的联系方式：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.学号' 附近有语法错误。
服务器: 消息 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.学号' 附近有语法错误。
tinghuyang 2004-10-18
up
dennier 2004-10-18
为什么“002“选的“计算机基础”没有记录？
Andy__Huang 2004-10-17
呵呵﹐路過望一下...
victorycyz 2004-10-17
楼主，论坛是大家的，不是你自己的，你没有权利指定谁进来谁不能进来。
邹建不厚道，1分都不留下。
zjcxc 2004-10-17
我的300分
zjcxc 2004-10-17
--示例
--示例表
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
--*/
--示例表
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
--*/
xjp6688 2004-10-17
看来分都得给邹大哥了，厉害，哈哈！
zjcxc 2004-10-17
--示例
--示例表
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
--*/
--示例表
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
--*/
zjcxc 2004-10-17
要求写完没有啊? 写完了就放分
lalakid 2004-10-17
哈哈，老大真有意思
逗死我了
挖哈哈
老大来抢分了
挖哈哈……
逗死我了
挖哈哈
老大来抢分了
挖哈哈……
sonymanzzzzzz 2004-10-17
其他高手也能参与啊!!!!!!快来抢分吧!!!!!!! :)
加载更多回复
WPF开发教程 1. WPF基础之体系结构... 3 2. WPF基础之XAML. 9 3. WPF基础之基元素... 23 4. WPF基础之属性系统... 26 5. WPF基础之路由事件... 33 6. WPF基础之布局系统... 46 7. WPF基础之样式设置和模板化... ...
常用SQL 语句的练习，包括查询，聚合的一些用法 ** 本文练习的数据是创建了四张表，Student,Score，Course,Teacher,方便做表的交叉查询，表间通过主外键链接，也希望对入门SQL的同学有一定参考价值 ** 本文在题目的筛选上也参考了知乎用户：猴子聊数据分析，感谢...
SQL数据库-------专业术语学习 用”菱形框“表示实体型之间的联系成因，在菱形框内写明联系名，并用”实心线段“分别与有关实体型连接起来，同时在”实心线段“旁标上联系的类型（1:1,1:n或m:n）。 DFD ----- 数据流图 数据流图（Data...
MySQL------DQL查询数据（Data Query LANGUAGE） 1.DQL (Data Query LANGUAGE : 数据查询语言) 所有的查询操作都用它 Select 简单的查询，复杂的查询它都能做~ 数据库中最核心的语言，最重要的语句 使用频率最高的语句 2.查询指定字段 语法： SELECT 字段,... ...
sqlserver 命令一览表 sqlserver 命令一览表 1.创建数据库： create database Student on ( name=Student_Data, filename='f:\data\student_Data.mdf', size=10, maxsize=20, ...
基础类 - SQL语句
基础类 - SQL语句
<br />转载请标明出处： ...
已知：当前时间 ( 截取 yyyy-mm 目标 1 查询进销存表当中距离当前时间连续 3 个月内进货数量 =0...
MS-SQL Server 基础类 - SQL语句 已知：当前时间(截取yyyy-mm) 目标1：查询进销存表当中距离当前时间连续3个月内进货数量=0... table结构如下 id,num,unit 如何实现报表如下 id num unit 1 10 u1 2 ... 菜鸟问题：这段批处理是什么意思？ insert ...
关于SQL SERVER 2000的一些基本知识（1）——简单的SQL语句 一.数据库 1.创建数据库： create database Student on ( name=Student_Data, filename=f:/data/student_Data.mdf, size=10, maxsize=20, filegrowth=5 ) log on ( name=Student
Andorid-15k+的面试题。 andorid开发也做了3年有余了，也面试很多加企业，借此机会分享一下，我们中遇到过的问题以及解决方案吧，希望...1.listView的优化方式 重用convertView viewHolder static class viewHolder
ym——Andorid-15k+的面试题。 最近才开的博客，希望大家多多关注，andorid开发也做了3年有余了，也面试很多加企业，借此机会分享一下，我们中遇到过的问题以及解决方案吧，希望能够对正在找工作的andoird...1.listView的优化方式 重用conve
CPP-练习 HW: 1、局部变量能否和全局变量重名...对于有些编译器而言，在同一个函数内可以定义多个同名的局部变量，比如在两个循环体内都定义一个同名的局部变量，而那个局部变量的作用域就在那个循环体内。 2、如...
C/C++面试题-1 1、局部变量能否和全局变量重名？ 答：能，局部会屏蔽全局。要用全局变量，需要使用"::" ;局部变量可以与全局变量同名，在函数内引用这个变量时，会用到同名的局部变量，而不会用到全局变量。对于有些编译器而言，在...
1024程序员节，一份精华合辑送给你 点击蓝色“有关SQL”关注我哟加个“星标”，天天与10000人一起快乐成长写在前面今天，10月24日。对于程序员来说，这天意义非凡。我的公众号，提供了一份1024的礼物，相信每个关注的朋友...
SQL Server 编程入门 一、T—SQL 的组成 1、DML(数据操作语言 Data Manipulation Language) 查询、插入、删除和修改数据库中的数据。SELECT、INSERT、UPDATE、DELETE 等； 2、DCL(数据控制语言 Data Control Language) 用来控制存取许可...
Andorid-15k+的面试题 andorid开发也做了3年有余了，也面试很多加企业，借此机会分享一下，我们中遇到过的问题以及解决方案吧，希望能够对正在找工作...1.listView的优化方式 重用convertView viewHolder static class viewHolder
2016面试整理--来源牛客网 马上国庆节了，首先祝大家最后都能有个满意的offer，国庆几天好好养精蓄锐。 我经常在牛客群里水群，应该不少人看我眼熟的。 本人是一个杭州渣硕，本科也很一般，和211 985半点都粘不到，没有参加...
