tableAId State Type
1 3 4
2 2 5
3 1 2
4 2 3
5 3 3
6 1 2
7 2 1
tbState
Id StateName
1 StateA
2 StateB
3 StateCtbType
Id TypeName
1 TypeA
2 TypeB
3 TypeC
4 TypeD
5 TypeE
我想运用动态sql语句显示出这样一个效果:类型 StateA StateB StateC
TypeA 1
TypeB 2
TypeC 1 1
TypeD 1
TypeE 1
这个显示出来的table里的各个数字和tableA里的数据是一一对应的
请问这句动态sql语句应该怎么写?
1 3 4
2 2 5
3 1 2
4 2 3
5 3 3
6 1 2
7 2 1
tbState
Id StateName
1 StateA
2 StateB
3 StateCtbType
Id TypeName
1 TypeA
2 TypeB
3 TypeC
4 TypeD
5 TypeE
我想运用动态sql语句显示出这样一个效果:类型 StateA StateB StateC
TypeA 1
TypeB 2
TypeC 1 1
TypeD 1
TypeE 1
这个显示出来的table里的各个数字和tableA里的数据是一一对应的
请问这句动态sql语句应该怎么写?
TypeA 0 1 0
TypeB 2 0 0
TypeC 0 1 1
TypeD 0 0 1
TypeE 0 1 0
;with tableA as (
select 1 as ID,3 as State,4 as Type union all
select 2 as ID,2 as State,5 as Type union all
select 3 as ID,1 as State,2 as Type union all
select 4 as ID,2 as State,3 as Type union all
select 5 as ID,3 as State,3 as Type union all
select 6 as ID,1 as State,2 as Type union all
select 7 as ID,2 as State,1 as Type
),tbState as
(
select 1 as ID,'StateA' as StateName union all
select 2 as ID,'StateB' as StateName union all
select 3 as ID,'StateC' as StateName
),tbType as
(
select 1 as ID,'TypeA' as TypeName union all
select 2 as ID,'TypeB' as TypeName union all
select 3 as ID,'TypeC' as TypeName union all
select 4 as ID,'TypeD' as TypeName union all
select 5 as ID,'TypeE' as TypeName
),T as
(
select a.*,b.StateName,c.TypeName from tableA a
inner join tbState b on a.State = b.id
inner join tbType c on a.Type = c.ID
)
select a.TypeName,
sum(case when b.State = 1 then 1 else null end) as [StateA],
sum(case when b.State = 2 then 1 else null end) as [StateB],
sum(case when b.State = 3 then 1 else null end) as [StateC]
from tbType a
left join T b on a.id = b.Type
group by a.TypeName
----------------------------
TypeName StateA StateB StateC
-------- ----------- ----------- -----------
TypeA NULL 1 NULL
TypeB 2 NULL NULL
TypeC NULL 1 1
TypeD NULL NULL 1
TypeE NULL 1 NULL
--> 测试数据:[tableA]
if object_id('[tableA]') is not null drop table [tableA]
create table [tableA]([Id] int,[State] int,[Type] int)
insert [tableA]
select 1,3,4 union all
select 2,2,5 union all
select 3,1,2 union all
select 4,2,3 union all
select 5,3,3 union all
select 6,1,2 union all
select 7,2,1
--> 测试数据:[tbState]
if object_id('[tbState]') is not null drop table [tbState]
create table [tbState]([Id] int,[StateName] varchar(6))
insert [tbState]
select 1,'StateA' union all
select 2,'StateB' union all
select 3,'StateC'
--> 测试数据:[tbType]
if object_id('[tbType]') is not null drop table [tbType]
create table [tbType]([Id] int,[TypeName] varchar(5))
insert [tbType]
select 1,'TypeA' union all
select 2,'TypeB' union all
select 3,'TypeC' union all
select 4,'TypeD' union all
select 5,'TypeE'
select
a.Id,b.StateName,c.TypeName into #tb
from [tableA] a
left join [tbState] b
on a.State=b.Id
left join [tbType] c
on a.Type=c.Idselect * from #tb
declare @str varchar(max)
set @str=''
select
@str=@str+','+StateName+'=sum(case when StateName='
+QUOTENAME(StateName,'''')+' then 1 else 0 end)'
from
#tb
group by
StateName
exec('select TypeName'+@str+' from #tb group by TypeName')
/*
TypeName StateA StateB StateC
---------------------------------------------------
TypeA 0 1 0
TypeB 2 0 0
TypeC 0 1 1
TypeD 0 0 1
TypeE 0 1 0
*/
sum(case when b.State = 1 then 1 else 0 end) as [StateA],
sum(case when b.State = 2 then 1 else 0 end) as [StateB],
sum(case when b.State = 3 then 1 else 0 end) as [StateC]
from tbType a
left join T b on a.id = b.Type
group by a.TypeName
用了你的语句后报错,说有sql语句中有几个错误
小小地修正了下,效果出来了,但是显示了2个table,我只想显示出1个我想要的table
--> 测试数据:[tableA]
if object_id('[tableA]') is not null drop table [tableA]
create table [tableA]([Id] int,[State] int,[Type] int)
insert [tableA]
select 1,3,4 union all
select 2,2,5 union all
select 3,1,2 union all
select 4,2,3 union all
select 5,3,3 union all
select 6,1,2 union all
select 7,2,1
--> 测试数据:[tbState]
if object_id('[tbState]') is not null drop table [tbState]
create table [tbState]([Id] int,[StateName] varchar(6))
insert [tbState]
select 1,'StateA' union all
select 2,'StateB' union all
select 3,'StateC'
--> 测试数据:[tbType]
if object_id('[tbType]') is not null drop table [tbType]
create table [tbType]([Id] int,[TypeName] varchar(5))
insert [tbType]
select 1,'TypeA' union all
select 2,'TypeB' union all
select 3,'TypeC' union all
select 4,'TypeD' union all
select 5,'TypeE'declare @str varchar(max)
set @str=''
select
@str=@str+','+StateName+'=sum(case when StateName='
+QUOTENAME(StateName,'''')+' then 1 else 0 end)'
from
(
select
a.Id,b.StateName,c.TypeName
from [tableA] a
left join [tbState] b
on a.State=b.Id
left join [tbType] c
on a.Type=c.Id
)t
group by
StateName
exec('select TypeName'+@str+' from ('+'
select
a.Id,b.StateName,c.TypeName
from [tableA] a
left join [tbState] b
on a.State=b.Id
left join [tbType] c
on a.Type=c.Id
'+')t group by TypeName')
/*
TypeName StateA StateB StateC
---------------------------------------------------
TypeA 0 1 0
TypeB 2 0 0
TypeC 0 1 1
TypeD 0 0 1
TypeE 0 1 0
*/