向所有SQL达人请教！

Question

一个贴子在SQL版没得到解答，在此向各位达人请教
先上测试数据表if object_id('Students') is not null 
drop table Students --学生表
create table Students(id int identity(1,1),name varchar(10),Department varchar(30)) 
insert into Students(name,Department) values('张三','计算机系')
insert into Students(name,Department) values('李四','外语系')
insert into Students(name,Department) values('王二','计算机系')
insert into Students(name,Department) values('赵五','数学系')if object_id('Score') is not null 
drop table Score --学生成绩表
create table Score(id int identity(1,1),name varchar(10),Course varchar(30),Score int) 
insert into Score (name,Course,Score) values('张三','数学',90)
insert into Score (name,Course,Score) values('张三','C语言',70)
insert into Score (name,Course,Score) values('张三','英语',60)
insert into Score (name,Course,Score) values('李四','语文',78)
insert into Score (name,Course,Score) values('李四','英语',81)
insert into Score (name,Course,Score) values('李四','政治',49)
insert into Score (name,Course,Score) values('李四','数学',89)
insert into Score (name,Course,Score) values('赵五','数学',100)
insert into Score (name,Course,Score) values('赵五','政治',54)
insert into Score (name,Course,Score) values('王二','数学',39)if object_id('Course') is not null 
drop table Course --必修课程表
create table Course(id int identity(1,1),Department varchar(30),Course varchar(30)) 
insert into Course (Department,Course) values('计算机系','数学')
insert into Course (Department,Course) values('计算机系','C语言')
insert into Course (Department,Course) values('计算机系','英语')
insert into Course (Department,Course) values('外语系','语文')
insert into Course (Department,Course) values('外语系','英语')
insert into Course (Department,Course) values('外语系','政治')
insert into Course (Department,Course) values('数学系','语文')
insert into Course (Department,Course) values('数学系','数学')
insert into Course (Department,Course) values('数学系','政治')要求：要求：从学生表取出学生信息，然后在学生成绩表中查这些学生的成绩记录，把这些记录与专业必考课程中的课程对比（根据学生专业，即系名）如果必考课程里规定的(该专业)课程在该生成绩表里没有记录，表明该生这门课程缺考;如果学生成绩小于60分，表明学生的这门课程不及格。最后的结果集要求每个学生一条记录,显示缺考课程及不及格课程结果要求如下方式显示

Answer 1

呵呵，从sql版跑到这里了。
不知道你要的这个结果集是用来在list页面中显示，还是作为报表导出。如果是list显示的话，那么学生数据应该还是比较多的，所以就需要用到分页了，而分页的话，就只需要组合出某几条、十几条的数据即可。如果是这种情况，可以在存储过程中利用临时表去把数据组合出来。
简单讲就是：
1）建立临时表(StuName, PassCourse, UnpassCourse, Status)
2)插入要组合数据的学生
3）以此表为基准，循环遍历选课信息，然后再找该课程的成绩，然后将具体的结果插入/更新到PassCourse，UnpassCourse, Status字段中
4）返回此表的数据
5）清空/删除该临时表
如果是导出报表的话，面对大量的数据，这个方法也不错。当然非要用纯sql实现，我相信可以做到，但是效率未必就好。有那么句话：解决问题的方法可能只有一种，但是办法却可以有很多。

Answer 2

4楼的朋友说得很对，办法很多，我把数据调到Dataset中做处理也可以实现，我现在希望是这个事情直接就在DB端给做掉了，省得后台去做一大堆的数据处理！至于你说的是用LIST还是用报表，我想如果整理出来的数据能跟我给出的一样，就直接可以用LIST绑定了，我现在用的方式是用JOIN连接表，获取到需要的数据后在后台做处理，然后构建动态Table显示出来！
你做的用存储过程插入临时表，不知这样做的效率怎么样！

Answer 3

SELECT t1.name,t1.Department,t2.Course,t3.Score
,CASE WHEN Score IS NULL THEN 'QK' 
      WHEN Score < 60 THEN 'BJG' END AS Status
FROM Students t1
LEFT JOIN Course t2
  ON t1.Department = t2.Department
LEFT JOIN Score t3
  ON t1.name = t3.name
 AND t2.Course = t3.Coursename       Department                     Course                         Score       Status
---------- ------------------------------ ------------------------------ ----------- ------
张三         计算机系                           数学                             90          NULL
张三         计算机系                           C语言                            70          NULL
张三         计算机系                           英语                             60          NULL
李四         外语系                            语文                             78          NULL
李四         外语系                            英语                             81          NULL
李四         外语系                            政治                             49          BJG
王二         计算机系                           数学                             39          BJG
王二         计算机系                           C语言                            NULL        QK
王二         计算机系                           英语                             NULL        QK
赵五         数学系                            语文                             NULL        QK
赵五         数学系                            数学                             100         NULL
赵五         数学系                            政治                             54          BJG(12 row(s) affected)
至于做成什么格式报表，怎么显示，还是别把这样的工作交给SQL做
物尽其用，格式问题在前台很好解决，SQL只提供原始数据比较好

Answer 4

--生成测试数据
if object_id('Students') is not null 
drop table Students --学生表
create table Students(id int identity(1,1),name varchar(10),Department varchar(30)) 
insert into Students(name,Department) values('张三','计算机系')
insert into Students(name,Department) values('李四','外语系')
insert into Students(name,Department) values('王二','计算机系')
insert into Students(name,Department) values('赵五','数学系')if object_id('Score') is not null 
drop table Score --学生成绩表
create table Score(id int identity(1,1),name varchar(10),Course varchar(30),Score int) 
insert into Score (name,Course,Score) values('张三','数学',90)
insert into Score (name,Course,Score) values('张三','C语言',70)
insert into Score (name,Course,Score) values('张三','英语',60)
insert into Score (name,Course,Score) values('李四','语文',78)
insert into Score (name,Course,Score) values('李四','英语',81)
insert into Score (name,Course,Score) values('李四','政治',49)
insert into Score (name,Course,Score) values('李四','数学',89)
insert into Score (name,Course,Score) values('赵五','数学',100)
insert into Score (name,Course,Score) values('赵五','政治',54)
insert into Score (name,Course,Score) values('王二','数学',39)if object_id('Course') is not null 
drop table Course --必修课程表
create table Course(id int identity(1,1),Department varchar(30),Course varchar(30)) 
insert into Course (Department,Course) values('计算机系','数学')
insert into Course (Department,Course) values('计算机系','C语言')
insert into Course (Department,Course) values('计算机系','英语')
insert into Course (Department,Course) values('外语系','语文')
insert into Course (Department,Course) values('外语系','英语')
insert into Course (Department,Course) values('外语系','政治')
insert into Course (Department,Course) values('数学系','语文')
insert into Course (Department,Course) values('数学系','数学')
insert into Course (Department,Course) values('数学系','政治')--查询课程状态并插入临时表
if object_id('[tb]') is not null drop table [tb]
go
SELECT t1.Name,t1.Department,t2.Course,SUM(t3.Score) Score
,CASE WHEN sum(t3.Score) IS NULL THEN '缺考' 
      WHEN sum(t3.Score) < 60 THEN '不及格' 
      END AS Status into tb
FROM Students t1
LEFT JOIN Course t2
  ON t1.Department = t2.Department
LEFT JOIN Score t3
  ON t1.name = t3.name
 AND t2.Course = t3.Course
GROUP BY t1.name,t1.Department,t2.Courseselect * from tb--创建条件行拼接函数
if object_id('[f_str]') is not null drop FUNCTION [f_str]
go
CREATE FUNCTION dbo.f_str(@name varchar(10),@flag varchar(10) = '缺考')
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @s varchar(8000)
    if (@flag = '缺考')
    begin
    SELECT @s = isnull(@s + ',','') + Course  FROM tb WHERE name=@name and Score is null
    RETURN isnull(@s ,'')
    end
    else if (@flag = '不及格')
    begin
    SELECT @s = isnull(@s + ',','') + Course FROM tb WHERE name=@name and Score < 60
    RETURN isnull(@s ,'')
    end
    else if (@flag = '正常')
    begin
    SELECT @s = isnull(@s + ',','') + Course FROM tb WHERE name=@name and Score >= 60 
    RETURN isnull(@s ,'')
    end
    else if (@flag = '状态')
    begin
    SELECT @s = isnull(@s + ' ','') + Status FROM tb WHERE name=@name group by Status-- having(sum(case when Status='正常' then 0 else 1 end) > 0)
    RETURN @s
    end
    else 
    select @s = NULL
    RETURN @s    
END
GO
--查询最终结果
select name AS 姓名
     , dbo.f_str(name,'缺考') AS 缺考课程
     , dbo.f_str(name,'不及格') AS 不及格课程
     , dbo.f_str(name,'正常') AS 正常课程
     , isnull(dbo.f_str(name,'状态'),'正常') AS 状态
 from tb group by name
 
 /*
姓名 缺考课程 不及格课程  正常课程      状态
李四 政治 英语,语文                  不及格 
王二 C语言,英语 数学                  不及格 缺考
张三                C语言,数学,英语  正常
赵五 语文     政治        数学          不及格 缺考 
 */

Answer 5

楼上的牛下午其实我也做了一半,但是工作上有事,就搁下了
if object_id('Students') is not null 
drop table Students --学生表
create table Students(id int identity(1,1),name varchar(10),Department varchar(30)) 
insert into Students(name,Department) values('张三','计算机系')
insert into Students(name,Department) values('李四','外语系')
insert into Students(name,Department) values('王二','计算机系')
insert into Students(name,Department) values('赵五','数学系')if object_id('Score') is not null 
drop table Score --学生成绩表
create table Score(id int identity(1,1),name varchar(10),Course varchar(30),Score int) 
insert into Score (name,Course,Score) values('张三','数学',90)
insert into Score (name,Course,Score) values('张三','C语言',70)
insert into Score (name,Course,Score) values('张三','英语',60)
insert into Score (name,Course,Score) values('李四','语文',78)
insert into Score (name,Course,Score) values('李四','英语',81)
insert into Score (name,Course,Score) values('李四','政治',49)
insert into Score (name,Course,Score) values('李四','数学',89)
insert into Score (name,Course,Score) values('赵五','数学',100)
insert into Score (name,Course,Score) values('赵五','政治',54)
insert into Score (name,Course,Score) values('王二','数学',39)if object_id('Course') is not null 
drop table Course --必修课程表
create table Course(id int identity(1,1),Department varchar(30),Course varchar(30)) 
insert into Course (Department,Course) values('计算机系','数学')
insert into Course (Department,Course) values('计算机系','C语言')
insert into Course (Department,Course) values('计算机系','英语')
insert into Course (Department,Course) values('外语系','语文')
insert into Course (Department,Course) values('外语系','英语')
insert into Course (Department,Course) values('外语系','政治')
insert into Course (Department,Course) values('数学系','语文')
insert into Course (Department,Course) values('数学系','数学')
insert into Course (Department,Course) values('数学系','政治')--select a.name,a.Department,b.Course,b.Score from Students a
--left join Score b on a.name=b.name;select c.name,c.Course,case when d.Score is null then '缺考' when d.Score<60 then '不及格' else '' end Score from
(
select a.name,a.Department,b.Course from Students a
left join Course b on a.Department=b.Department
)c
left join Score d on c.name=d.name and c.Course=d.Course--select distinct name
--from 
--(
-- select c.name,c.Course,case when d.Score is null then '缺考' when d.Score<60 then '不及格' else '' end Score from
-- (
-- select a.name,a.Department,b.Course from Students a
-- left join Course b on a.Department=b.Department
-- )c
-- left join Score d on c.name=d.name and c.Course=d.Course
--)aselect * from Students
left join 
(
select c.name,c.Course,case when d.Score is null then '缺考' when d.Score<60 then '不及格' else '' end Score from
(
select a.name,a.Department,b.Course from Students a
left join Course b on a.Department=b.Department
)c
left join Score d on c.name=d.name and c.Course=d.Course
)a