3表查询，第2次问，

Question

Student_KC：
编号 年度 年级 班级 科目 姓名
001 2010 初一 1班 语文 张三
002 2010 初一 1班 数学 李四
003 2010 初一 1班 英语 王五
manage_subject_table：
编号 年度 年级 班级 星期 节次 科目
001 2010 初一 1班 星期一 1 语文
002 2010 初一 1班 星期一 2 数学
003 2010 初一 1班 星期一 3 英语
Student_LKC：
编号 年度 年级 班级 星期 节次 原科目 原教师 新科目 新教师 开始 结束 月份
001 2010 初一 1班 星期一 1    语文  张三    英语   王五    6    7    4
说明：原科目、原教师：是本身安排的科目和教师；新科目、新教师：是临时接受代课的科目和教师；开始、结束：是开始/结束的周次，他们是INT类型，教师一次调代课的课时就应该是：（结束-开始+1）*1
--------------------------------------------------------------------------------
[Student_KC] 和[manage_subject_table] 2表查询，得到了如下的表，教师课时表是没有问题的
---SQL如下，
select @sql=@sql+','+quotename(y.科目)+'=sum(case when y.科目= '+quotename(y.科目,'''')+ ' then 1 end)' from manage_subject_table y where y.年度=@Niandu and y.科目<>'' and y.科目 is not null group by y.科目
exec('select x.姓名'+@sql+',总计=(select count(*) from(select 姓名 from Student_KC a,manage_subject_table b where b.年度='''+@Niandu+''' and a.年度=b.年度 and a.年级=b.年级 and a.班级=b.班级 and a.科目=b.科目)c where x.姓名=c.姓名) from Student_KC x,manage_subject_table y where x.年度='''+@Niandu+''' and x.年度=y.年度 and x.年级=y.年级 and x.班级=y.班级 and x.科目=y.科目 group by x.姓名')
效果：-----------------------
姓名 语文 数学 英语 总计
张三 1    -     -    1
李四 -    1     -    1
王五 -    -     1    1
-------------------------------------------
考虑进Student_LKC表后的，
最后我需要得到的查询是，查询Student_LKC.月份='4'的教师课时表：
姓名 语文 数学 英语 总计 调代课 
张三 1    -     -    1    -2
李四 -    1     -    1    0
王五 -    -     1    1    2为什么调代课是2,因为是7-6+1周

Answer 1

效果应该是这样，上面的正确的但是看不清楚：最后我需要得到的查询是，查询Student_LKC.月份='4'的教师课时表：
姓名 语文 数学 英语 总计 调代课  
张三 1 ------------1 -2
李四 ---- 1 -------1  0
王五 --------- 1-- 1  2说明：
1、虚线是占位置符号，便于预览，没意义的，
2、为什么调代课是2,是因为有7-6+1周

Answer 2

除了能看懂需要个行列转换,其他的确实看不懂你的意思./*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(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 姓名 as 姓名 ,
  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 3

--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
--       QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0    C Language 
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server  2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.Student_KC') is not null drop table Student_KC
-- 创建数据表
create table Student_KC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
科目 char(5),
姓名 char(7)
)
go
--插入测试数据
insert into Student_KC select '001',2010,'初一','1班','语文','张三'
union all select '002',2010,'初一','1班','数学','李四'
union all select '003',2010,'初一','1班','英语','王五'
go
if object_id('test.dbo.manage_subject_table') is not null drop table manage_subject_table
-- 创建数据表
create table manage_subject_table
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
科目 char(5)
)
go
--插入测试数据
insert into manage_subject_table select '001',2010,'初一','1班','星期一',1,'语文'
union all select '002',2010,'初一','1班','星期一',2,'数学'
union all select '003',2010,'初一','1班','星期一',3,'英语'
go
if object_id('test.dbo.Student_LKC') is not null drop table Student_LKC
-- 创建数据表
create table Student_LKC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
原科目 char(5),
原教师 char(7),
新科目 char(5),
新教师 char(7),
开始 int,
结束 int,
月份 char(2)
)
go
--插入测试数据
insert into Student_LKC select '001',2010,'初一','1班','星期一',1,'语文','张三','英语','王五',6,7,'04'
go
--代码实现if object_id('test.dbo._temptb') is not null drop table _temptb
create table _temptb(编号 char(4),姓名 char(7),科目 char(5),原教师 char(7),新教师 char(7),开始 int,结束 int)
go
declare @sql varchar(max),@niandu char(4),@yuefen char(2)
select @niandu='2010',@yuefen='04'
insert into _temptb
select b.编号,b.姓名,b.科目,c.原教师,c.新教师,c.开始,c.结束 from (
select x.* from Student_KC x join manage_subject_table y
on x.年度=@niandu and x.年度=y.年度 and x.年级=y.年级 and x.班级=y.班级 and x.科目=y.科目 )b
left join Student_LKC c
on c.年度=@niandu and b.年度=c.年度 and b.年级=c.年级 and b.班级=c.班级 and c.月份=@yuefen
and (b.姓名=c.原教师 and b.科目=c.原科目 or b.姓名=c.新教师 and b.科目=c.新科目)
select @sql=isnull(@sql+','+rtrim(科目)+'=isnull(sum(case when 科目='''+rtrim(科目)+''' then 1 end),0)',','+rtrim(科目)+'=isnull(sum(case when 科目='''+rtrim(科目)+''' then 1 end),0)') from _temptb
select @sql='select 姓名'+@sql+',总计=count(科目),调代课=isnull((case when 姓名=原教师 then -(结束-开始+1) else 结束-开始+1 end),0) from _temptb group by 姓名,原教师,开始,结束,编号 order by 编号'
exec(@sql)
drop table _temptb/*测试结果姓名     语文     数学     英语     总计     调代课 
-----------------------------------------------------
张三    1 0 0 1 -2
李四    0 1 0 1 0
王五    0 0 1 1 2(3 行受影响)
*/

Answer 4

非常感谢： happycell188
当一个老师任2门以上课程时，显示有些问题：（可以执行下面的代码看下效果）
---------------------------------------------
if object_id('dbo.Student_KC') is not null drop table Student_KC
-- 创建数据表
create table Student_KC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
科目 char(5),
姓名 char(7)
)
go
--插入测试数据
insert into Student_KC select '001',2010,'初一','1班','语文','张三'
union all select '002',2010,'初一','1班','数学','李四'
union all select '003',2010,'初一','1班','英语','王五'
union all select '004',2010,'初一','2班','化学','王五'
go
if object_id('dbo.manage_subject_table') is not null drop table manage_subject_table
-- 创建数据表
create table manage_subject_table
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
科目 char(5)
)
go
--插入测试数据
insert into manage_subject_table select '001',2010,'初一','1班','星期一',1,'语文'
union all select '002',2010,'初一','1班','星期一',2,'数学'
union all select '003',2010,'初一','1班','星期一',3,'英语'
union all select '004',2010,'初一','2班','星期一',4,'化学'
go
if object_id('dbo.Student_LKC') is not null drop table Student_LKC
-- 创建数据表
create table Student_LKC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
原科目 char(5),
原教师 char(7),
新科目 char(5),
新教师 char(7),
开始 int,
结束 int,
月份 char(2)
)
go
--插入测试数据
insert into Student_LKC select '001',2010,'初一','1班','星期一',1,'语文','张三','英语','王五',6,12,'04'
union all select '001',2010,'初一','1班','星期一',2,'数学','李四','英语','王五',6,12,'04'
go
--代码实现if object_id('dbo._temptb') is not null drop table _temptb
create table _temptb(编号 char(4),姓名 char(7),科目 char(5),原教师 char(7),新教师 char(7),开始 int,结束 int)
go
declare @sql varchar(4000),@niandu char(4),@yuefen char(2)
select @niandu='2010',@yuefen='04'
insert into _temptb
select b.编号,b.姓名,b.科目,c.原教师,c.新教师,c.开始,c.结束 from (
select x.* from Student_KC x join manage_subject_table y
on x.年度=@niandu and x.年度=y.年度 and x.年级=y.年级 and x.班级=y.班级 and x.科目=y.科目 )b
left join Student_LKC c
on c.年度=@niandu and b.年度=c.年度 and b.年级=c.年级 and b.班级=c.班级 and c.月份=@yuefen
and (b.姓名=c.原教师 and b.科目=c.原科目 or b.姓名=c.新教师 and b.科目=c.新科目)
select @sql=isnull(@sql+','+rtrim(科目)+'=isnull(sum(case when 科目='''+rtrim(科目)+''' then 1 end),0)',','+rtrim(科目)+'=isnull(sum(case when 科目='''+rtrim(科目)+''' then 1 end),0)') from _temptb
select @sql='select 姓名'+@sql+',总计=count(科目),调代课=isnull((case when 姓名=原教师 then -(结束-开始+1) else 结束-开始+1 end),0) from _temptb group by 姓名,原教师,开始,结束,编号 order by 编号'
exec(@sql)
drop table _temptb 

Answer 5

if object_id('dbo.Student_KC') is not null drop table Student_KC
-- 创建数据表
create table Student_KC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
科目 char(5),
姓名 char(7)
)
go
--插入测试数据
insert into Student_KC select '001',2010,'初一','1班','语文','张三'
union all select '002',2010,'初一','1班','数学','李四'
union all select '003',2010,'初一','1班','英语','王五'
union all select '004',2010,'初一','2班','化学','王五'
go
if object_id('dbo.manage_subject_table') is not null drop table manage_subject_table
-- 创建数据表
create table manage_subject_table
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
科目 char(5)
)
go
--插入测试数据
insert into manage_subject_table select '001',2010,'初一','1班','星期一',1,'语文'
union all select '002',2010,'初一','1班','星期一',2,'数学'
union all select '003',2010,'初一','1班','星期一',3,'英语'
union all select '004',2010,'初一','2班','星期一',4,'化学'
go
if object_id('dbo.Student_LKC') is not null drop table Student_LKC
-- 创建数据表
create table Student_LKC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
原科目 char(5),
原教师 char(7),
新科目 char(5),
新教师 char(7),
开始 int,
结束 int,
月份 char(2)
)
go
--插入测试数据
insert into Student_LKC select '001',2010,'初一','1班','星期一',1,'语文','张三','英语','王五',6,12,'04'
union all select '001',2010,'初一','1班','星期一',2,'数学','李四','英语','王五',6,12,'04'
go
--代码实现if object_id('dbo._temptb') is not null drop table _temptb
create table _temptb(编号 char(4),姓名 char(7),科目 char(5),原教师 char(7),新教师 char(7),开始 int,结束 int)
go
declare @sql1 varchar(4000),@sql2 varchar(4000),@sql3 varchar(4000)
declare @niandu char(4),@yuefen char(2)
select @niandu='2010',@yuefen='04'
insert into _temptb
select b.编号,b.姓名,b.科目,c.原教师,c.新教师,c.开始,c.结束 from (
select x.* from Student_KC x join manage_subject_table y
on x.年度=@niandu and x.年度=y.年度 and x.年级=y.年级 and x.班级=y.班级 and x.科目=y.科目 )b
left join Student_LKC c
on c.年度=@niandu and b.年度=c.年度 and b.年级=c.年级 and b.班级=c.班级 and c.月份=@yuefen
and (b.姓名=c.原教师 and b.科目=c.原科目 or b.姓名=c.新教师 and b.科目=c.新科目)
select @sql1=isnull(@sql1+','+rtrim(科目)+'=case when 科目='''+rtrim(科目)+''' then 1 else 0 end',','+rtrim(科目)+'=case when 科目='''+rtrim(科目)+''' then 1 else 0 end'),
@sql2=isnull(@sql2+','+rtrim(科目)+'=sum('+rtrim(科目)+')',','+rtrim(科目)+'=sum('+rtrim(科目)+')'),
@sql3=isnull(@sql3+','+rtrim(科目),','+rtrim(科目)) from (select distinct 科目 from _temptb)tt
select @sql1='select 姓名'+@sql1+',总计=count(科目),调代课=isnull((case when 姓名=原教师 then -(结束-开始+1) else 结束-开始+1 end),0) from _temptb group by 姓名,科目,原教师,开始,结束'
exec('select 姓名=max(姓名)'+@sql2+',总计=sum(总计),调代课=sum(调代课) from ('+@sql1+')t group by 姓名'+@sql3)
drop table _temptb/*测试结果姓名   化学 数学 英语 语文 总计 调代课
----------------------------------------------
李四    0 1 0 0 1 -7
王五    0 0 2 0 2 14
王五    1 0 0 0 1 0
张三    0 0 0 1 1 -7(4 行受影响)
*/

Answer 6

请你再测试一下这个数据，李四的总计：不应该是6，王五的记录没有分组，谢谢，再给我看下，非常感谢----------------------------
if object_id('dbo.Student_KC') is not null drop table Student_KC
-- 创建数据表
create table Student_KC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
科目 char(5),
姓名 char(7)
)
go
--插入测试数据
insert into Student_KC select '001',2010,'初一','1班','语文','张三'
union all select '002',2010,'初一','1班','数学','李四'
union all select '003',2010,'初一','1班','英语','王五'
union all select '004',2010,'初一','2班','化学','王五'
union all select '004',2010,'初一','1班','生物','王五'
go
if object_id('dbo.manage_subject_table') is not null drop table manage_subject_table
-- 创建数据表
create table manage_subject_table
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
科目 char(5)
)
go
--插入测试数据
insert into manage_subject_table select '001',2010,'初一','1班','星期一',1,'语文'
union all select '002',2010,'初一','1班','星期一',2,'数学'
union all select '002',2010,'初一','1班','星期二',1,'数学'
union all select '002',2010,'初一','1班','星期二',2,'数学'
union all select '003',2010,'初一','1班','星期一',3,'英语'
union all select '004',2010,'初一','2班','星期一',4,'化学'
union all select '005',2010,'初一','1班','星期一',1,'生物'
union all select '006',2010,'初一','1班','星期一',2,'生物'
go
if object_id('dbo.Student_LKC') is not null drop table Student_LKC
-- 创建数据表
create table Student_LKC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
原科目 char(5),
原教师 char(7),
新科目 char(5),
新教师 char(7),
开始 int,
结束 int,
月份 char(2)
)
go
--插入测试数据
insert into Student_LKC select '001',2010,'初一','1班','星期一',1,'语文','张三','英语','王五',6,12,'04'
union all select '001',2010,'初一','1班','星期一',2,'数学','李四','英语','王五',6,12,'04'
union all select '001',2010,'初一','1班','星期一',1,'生物','王五','数学','李四',6,8,'04'
go
--代码实现if object_id('dbo._temptb') is not null drop table _temptb
create table _temptb(编号 char(4),姓名 char(7),科目 char(5),原教师 char(7),新教师 char(7),开始 int,结束 int)
go
declare @sql1 varchar(4000),@sql2 varchar(4000),@sql3 varchar(4000)
declare @niandu char(4),@yuefen char(2)
select @niandu='2010',@yuefen='04'
insert into _temptb
select b.编号,b.姓名,b.科目,c.原教师,c.新教师,c.开始,c.结束 from (
select x.* from Student_KC x join manage_subject_table y
on x.年度=@niandu and x.年度=y.年度 and x.年级=y.年级 and x.班级=y.班级 and x.科目=y.科目 )b
left join Student_LKC c
on c.年度=@niandu and b.年度=c.年度 and b.年级=c.年级 and b.班级=c.班级 and c.月份=@yuefen
and (b.姓名=c.原教师 and b.科目=c.原科目 or b.姓名=c.新教师 and b.科目=c.新科目)
select @sql1=isnull(@sql1+','+rtrim(科目)+'=case when 科目='''+rtrim(科目)+''' then 1 else 0 end',','+rtrim(科目)+'=case when 科目='''+rtrim(科目)+''' then 1 else 0 end'),
@sql2=isnull(@sql2+','+rtrim(科目)+'=sum('+rtrim(科目)+')',','+rtrim(科目)+'=sum('+rtrim(科目)+')'),
@sql3=isnull(@sql3+','+rtrim(科目),','+rtrim(科目)) from (select distinct 科目 from _temptb)tt
select @sql1='select 姓名'+@sql1+',总计=count(科目),调代课=isnull((case when 姓名=原教师 then -(结束-开始+1) else 结束-开始+1 end),0) from _temptb group by 姓名,科目,原教师,开始,结束'
exec('select 姓名=max(姓名)'+@sql2+',总计=sum(总计),调代课=sum(调代课) from ('+@sql1+')t group by 姓名'+@sql3)
drop table _temptb

Answer 7

use test
go
if object_id('dbo.Student_KC') is not null drop table Student_KC
-- 创建数据表
create table Student_KC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
科目 char(5),
姓名 char(7)
)
go
--插入测试数据
insert into Student_KC select '001',2010,'初一','1班','语文','张三'
union all select '002',2010,'初一','1班','数学','李四'
union all select '003',2010,'初一','1班','英语','王五'
union all select '004',2010,'初一','2班','化学','王五'
union all select '004',2010,'初一','1班','生物','王五'
go
if object_id('dbo.manage_subject_table') is not null drop table manage_subject_table
-- 创建数据表
create table manage_subject_table
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
科目 char(5)
)
go
--插入测试数据
insert into manage_subject_table select '001',2010,'初一','1班','星期一',1,'语文'
union all select '002',2010,'初一','1班','星期一',2,'数学'
union all select '002',2010,'初一','1班','星期二',1,'数学'
union all select '002',2010,'初一','1班','星期二',2,'数学'
union all select '003',2010,'初一','1班','星期一',3,'英语'
union all select '004',2010,'初一','2班','星期一',4,'化学'
union all select '005',2010,'初一','1班','星期一',1,'生物'
union all select '006',2010,'初一','1班','星期一',2,'生物'
go
if object_id('dbo.Student_LKC') is not null drop table Student_LKC
-- 创建数据表
create table Student_LKC
(
编号 char(4),
年度 char(4),
年级 char(5),
班级 char(4),
星期 char(7),
节次 int,
原科目 char(5),
原教师 char(7),
新科目 char(5),
新教师 char(7),
开始 int,
结束 int,
月份 char(2)
)
go
--插入测试数据
insert into Student_LKC select '001',2010,'初一','1班','星期一',1,'语文','张三','英语','王五',6,12,'04'
union all select '001',2010,'初一','1班','星期一',2,'数学','李四','英语','王五',6,12,'04'
union all select '001',2010,'初一','1班','星期一',1,'生物','王五','数学','李四',6,8,'04'
go
--代码实现if object_id('dbo._temptb') is not null drop table _temptb
create table _temptb(编号 char(4),姓名 char(7),科目 char(5),原教师 char(7),新教师 char(7),开始 int,结束 int)
go
declare @sql1 varchar(4000),@sql2 varchar(4000),@sql3 varchar(4000)
declare @niandu char(4),@yuefen char(2)
select @niandu='2010',@yuefen='04'
insert into _temptb
select b.编号,b.姓名,b.科目,c.原教师,c.新教师,c.开始,c.结束 from (
select x.* from Student_KC x join manage_subject_table y
on x.年度=@niandu and x.年度=y.年度 and x.年级=y.年级 and x.班级=y.班级 and x.科目=y.科目 )b
left join Student_LKC c
on c.年度=@niandu and b.年度=c.年度 and b.年级=c.年级 and b.班级=c.班级 and c.月份=@yuefen
and (b.姓名=c.原教师 and b.科目=c.原科目 or b.姓名=c.新教师 and b.科目=c.新科目)
select @sql1=isnull(@sql1+','+rtrim(科目)+'=case when 科目='''+rtrim(科目)+''' then 1 else 0 end',','+rtrim(科目)+'=case when 科目='''+rtrim(科目)+''' then 1 else 0 end'),
@sql2=isnull(@sql2+','+rtrim(科目)+'=sum('+rtrim(科目)+')',','+rtrim(科目)+'=sum('+rtrim(科目)+')'),
@sql3=isnull(@sql3+','+rtrim(科目),','+rtrim(科目)) from (select distinct 科目 from _temptb)tt
select @sql1='select 姓名'+@sql1+',总计=count(distinct 科目),调代课=isnull((case when 姓名=原教师 then -(结束-开始+1) else 结束-开始+1 end),0) from _temptb group by 姓名,科目,原教师,开始,结束'
exec('select 姓名=max(姓名)'+@sql2+',总计=sum(总计),调代课=sum(调代课) from ('+@sql1+')t group by 姓名'+@sql3)
drop table _temptb/*测试结果姓名     化学     生物     数学     英语     语文     总计      调代课
-------------------------------------------------------------------
李四    0 0 2 0 0 2 -4
王五    0 0 0 2 0 2 14
王五    0 1 0 0 0 1 -3
王五    1 0 0 0 0 1 0
张三    0 0 0 0 1 1 -7(5 行受影响)
*/