22,209
社区成员
发帖
与我相关
我的任务
分享
if object_ID('ta') IS NOT NULL DROP TABLE ta
go
create table ta(USID int, 日期 datetime, 类别 int, 分数 int )
go
insert ta select
101, '2009-09-01', 2 , 70 union all select
101, '2009-09-01', 3 , 80 union all select
101, '2009-09-01', 7 , 90
if object_ID('tb') IS NOT NULL DROP TABLE tb
go
create table tb( ID int, 说明 varchar(10) )
go
insert tb select
2, '语文' union all select
3, '数学' union all select
7, '英语' union all select
4, '化学' union all select
1, '政治' union all select
5, '物理'
if object_ID('tc') IS NOT NULL DROP TABLE tc
go
create table tc(USID int, 日期 datetime, 描述 varchar(20) )
go
insert tc select
101, '2009-09-01' , '品德(+50)' union all select
101, '2009-09-01' , '劳动(+20)' union all select
101, '2009-09-01' , '文体(+90)'
------------------------------
declare @s varchar(4000),@str varchar(50)
select @s=isnull(@s+',','') +'max(case when 类别='+ltrim(ID)+' then 分数 else 0 end) ['+说明+']'
from (select distinct b.* from tb b ,ta a where a.类别=b.id)t ---- 变一下
select @str=isnull(@str+',','')+描述 from tc
set @s= 'select a.usid, a.日期,'+@s
+','''+max(@str)+''' as 描述 from ta a ,tc c where a.usid=c.usid and a.日期=c.日期 group by a.usid,a.日期'
exec(@s)
usid 日期 语文 数学 英语 描述
----------- ----------------------- ----------- ----------- ----------- -----------------------------
101 2009-09-01 00:00:00.000 70 80 90 品德(+50),劳动(+20),文体(+90)
(1 行受影响)
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-29 16:15:44
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([USID] int,[日期] datetime,[类别] int,[分数] int)
insert [A]
select 101,'2009-09-01',2,70 union all
select 101,'2009-09-01',3,80 union all
select 101,'2009-09-01',7,90
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[说明] varchar(4))
insert [B]
select 1,'政治' union all
select 2,'语文' union all
select 3,'数学' union all
select 4,'化学' union all
select 5,'物理' union all
select 7,'英语'
--> 测试数据:[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([USID] int,[日期] datetime,[描述] varchar(9))
insert [C]
select 101,'2009-09-01','品德(+50)' union all
select 101,'2009-09-01','劳动(+20)' union all
select 101,'2009-09-01','文体(+90)'
--------------开始查询--------------------------
;with f1 as
(select a.*,b.[说明],c.[描述] from a,b,c where a.[类别]=b.id and a.[USID]=c.[USID]),
f2 as
(
select USID,日期,
max(case 说明 when '语文' then 分数 else 0 end) as 语文,
max(case 说明 when '数学' then 分数 else 0 end) as 数学,
max(case 说明 when '英语' then 分数 else 0 end) as 英语,
描述
from
f1
group by
USID,日期,描述
)
select
USID,日期,语文,数学,英语,描述=stuff((select ','+描述 from f2 t where USID=f2.USID for xml path('')), 1, 1, '')
from
f2
group by
USID,日期,语文,数学,英语
----------------结果----------------------------
/* USID 日期 语文 数学 英语 描述
----------- ----------------------- ----------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101 2009-09-01 00:00:00.000 70 80 90 劳动(+20),品德(+50),文体(+90)
(1 行受影响)
*/