解决方案 »
- 帮忙看看这个存储过程的问题
- 两个数据哭查询用什么??
- 自定义函数中怎么实现表记录循环...
- 半天了管理器还打不开,急用啊!!!!
- 如何在程序里,取得Transact-SQL内的变量
- 随机取一些记录
- 请对触发器有研究的高手看看!
- 如何查询SQLSERVER数据库DB1中所有表的记录数?
- 能否将SQL-SERVER2000数据库中的表转换为VFP的DBF格式呢?
- PB 中一个窗口能不能打开两个以上的实例
- 循环除10之后,结果不正确,sql2000错误,sql2005正常,大侠来解决一下?
- 创建存储过程 名称:s_stack 要求:(1)带有两个参数,其中一个为要查询的物品编号,另一个参数为要返回的库存数量 (2)在存储过
SET @SQL='SELECT T1.stu_id,T2.stu_name'
SELECT @SQL=@SQL+',ISNULL(SUM(CASE WHEN Stype=1 AND [subject]='''+subject+'''THEN[score]END),0)['+subject+']'
FROM 成绩表 GROUP BY subject
SET @SQL=@SQL+',ISNULL(SUM([score]),0)[scores(总成绩)]'
SELECT @SQL=@SQL+',ISNULL(SUM(CASE WHEN Stype=2 AND [subject]='''+subject+'''THEN[score]END),0)['+subject+'补考成绩]'
FROM 补考成绩表 GROUP BY subject
SET @SQL=@SQL+'FROM(SELECT *,1 Stype FROM 成绩表 UNION ALL SELECT *,2 FROM 补考成绩表)T1
JOIN 学生表 T2 ON T1.stu_id=T2.stu_id
GROUP BY T1.stu_id,T2.stu_name
ORDER BY T1.stu_id'
EXEC(@SQL)
if object_id('学生表') is not null
drop table 学生表
go
create table 学生表(stu_id int,stu_name varchar(100),class_id varchar(100))
insert into 学生表
select 1 stu_id,'张三' stu_name ,'1-1' class_id
union all
select 2 stu_id,'李四' stu_name ,'1-1' class_id
union all
select 3 stu_id,'王五' stu_name ,'1-2' class_id
-------------------------------
if object_id('成绩表') is not null
drop table 成绩表
go
create table 成绩表(stu_id int,subject varchar(100),score int)
insert into 成绩表
select 1 stu_id, 'java' subject, 77 score
union all
select 1 stu_id, 'c++' subject, 80 score
union all
select 2 stu_id, 'java' subject, 67 score
union all
select 2 stu_id, 'c++' subject, 58 score
union all
select 3 stu_id, 'java' subject, 56 score
union all
select 3 stu_id, 'c++' subject, 85 score
-------------------------------------
if object_id('补考成绩表') is not null
drop table 补考成绩表
go
create table 补考成绩表(stu_id int,subject varchar(100),score int)
insert into 补考成绩表
select 2 stu_id, 'c++' subject, 78 score
union all
select 3 stu_id, 'java' subject, 82 score
--取成绩表中的科目
declare @subject varchar(100)
declare @subject_Renname varchar(100)
declare @subject_Renname1 varchar(100)
select @subject = ISNULL(@subject+',','') + tb1.subject
from
(
select distinct QUOTENAME(subject) subject from 成绩表
)tb1--字段重命名
select @subject_Renname = ISNULL(@subject_Renname+',','') + tb1.subject
from
(
select distinct QUOTENAME(subject) +' ['+subject+'_score]' subject from 成绩表
)tb1
print @subject_Renname--字段重命名后字段名
select @subject_Renname1 = ISNULL(@subject_Renname1+',','') + tb1.subject
from
(
select distinct QUOTENAME(subject+'_score') subject from 成绩表
)tb1
print @subject_Renname1--取补考成绩表中的科目名
declare @BKsubject varchar(100)
declare @BKsubject_Renname varchar(100)
declare @BKsubject_Renname1 varchar(100)
select @BKsubject = ISNULL(@BKsubject+',','') + tb1.subject
from
(
select distinct QUOTENAME(subject) subject from 补考成绩表
)tb1
--字段重命名
select @BKsubject_Renname = ISNULL(@BKsubject_Renname+',','') + tb1.subject
from
(
select distinct QUOTENAME(subject) +' ['+subject+'补考成绩]' subject from 补考成绩表
)tb1
print @BKsubject_Renname--字段重命名后字段名
select @BKsubject_Renname1 = ISNULL(@BKsubject_Renname1+',','') + tb1.subject
from
(
select distinct QUOTENAME(subject+'补考成绩') subject from 补考成绩表
)tb1
print @BKsubject_Renname1
declare @sql nvarchar(max)
set @sql = ' select
tb4.stu_name
,'+@subject_Renname1+',tb2.scores总成绩
,'+@BKsubject_Renname1+' from
(
select stU_ID,'+@subject_Renname+'
from 成绩表
pivot
(max(score) for subject in ('+@subject+')) as pvt
) tb1
left join
(
select stu_id
,SUM(score) scores总成绩
from 成绩表
group by stu_id
)tb2 on tb1.stu_id = tb2.stu_id
left join
(
select stU_ID,'+@BKsubject_Renname+'
from 补考成绩表
pivot
(max(score) for subject in ('+@BKsubject+') )as pvt
)tb3 on tb1.stu_id = tb3.stu_id
left join 学生表 tb4 on tb1.stu_id = tb4.stu_id
'
print @sql
exec(@sql)
----------- ----------- ----------- ----------- ----------- -----------
张三 80 77 157 NULL NULL
李四 58 67 125 78 NULL
王五 85 56 141 NULL 82(3 行受影响)