declare @sql varchar(8000) set @sql ='' select @sql = @sql + ',max(case when 类别 = ' + cast(id as varchar) + ' then 分数 end) as [' + 说明 + ']' from Bexec (' select a.USID,a,日期' + @sql + ',c.描述 from a left join c on a.USID=c.USID and a.日期=c.日期 group by a.USID,a,日期,c.描述 ')
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-09-28 13:38:49 -- 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 2,'语文' union all select 3,'数学' union all select 7,'英语' --> 测试数据:[C] if object_id('[C]') is not null drop table [C] go create table [C]([USID] int,[日期] datetime,[描述] varchar(12)) insert [C] select 101,'2009-09-01','数学考试作弊' --------------开始查询-------------------------- ;with f as (select a.*,b.[说明],c.[描述] from a,b,c where a.[类别]=b.id and a.[USID]=c.[USID]) 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 f group by USID,日期,描述 ----------------结果---------------------------- /* USID 日期 语文 数学 英语 描述 ----------- ----------------------- ----------- ----------- ----------- ------------ 101 2009-09-01 00:00:00.000 70 80 90 数学考试作弊(1 行受影响) */
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, '英语' 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', '数学考试作弊'declare @s varchar(4000)select @s=isnull(@s+',','') +'max(case when 类别='+ltrim(ID)+' then 分数 else 0 end) ['+说明+']' from tbset @s= 'select a.usid, a.日期,'+@s +',max(描述) 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 数学考试作弊(1 行受影响)
在sql server 2000里提示with附近有语法错误。在网上查了with as的用法没有详细的说明。
若是 B 表中有很多项 没有分数的 不要读取怎么办B表 id 说明 2 语文 3 数学 7 英语 4 化学 5 政治
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 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', '数学考试作弊'------------------------------declare @s varchar(4000)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 ---- 变一下set @s= 'select a.usid, a.日期,'+@s +',max(描述) 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 数学考试作弊(1 行受影响)
set @sql =''
select @sql = @sql + ',max(case when 类别 = ' + cast(id as varchar) + ' then 分数 end) as [' + 说明 + ']'
from Bexec (' select a.USID,a,日期' + @sql + ',c.描述 from a left join c
on a.USID=c.USID and a.日期=c.日期
group by a.USID,a,日期,c.描述
')
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-28 13:38:49
-- 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 2,'语文' union all
select 3,'数学' union all
select 7,'英语'
--> 测试数据:[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([USID] int,[日期] datetime,[描述] varchar(12))
insert [C]
select 101,'2009-09-01','数学考试作弊'
--------------开始查询--------------------------
;with f as
(select a.*,b.[说明],c.[描述] from a,b,c where a.[类别]=b.id and a.[USID]=c.[USID])
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 f
group by
USID,日期,描述
----------------结果----------------------------
/* USID 日期 语文 数学 英语 描述
----------- ----------------------- ----------- ----------- ----------- ------------
101 2009-09-01 00:00:00.000 70 80 90 数学考试作弊(1 行受影响)
*/
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, '英语' 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', '数学考试作弊'declare @s varchar(4000)select @s=isnull(@s+',','') +'max(case when 类别='+ltrim(ID)+' then 分数 else 0 end) ['+说明+']'
from tbset @s= 'select a.usid, a.日期,'+@s
+',max(描述) 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 数学考试作弊(1 行受影响)
id 说明
2 语文
3 数学
7 英语
4 化学
5 政治
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
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', '数学考试作弊'------------------------------declare @s varchar(4000)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 ---- 变一下set @s= 'select a.usid, a.日期,'+@s
+',max(描述) 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 数学考试作弊(1 行受影响)