日期:2014-05-18  浏览次数:20489 次

这句动态sql语句应该怎么写?
tableA

Id State Type1 Type2 Type3
1 3 2 4 8
2 1 1 3 5
3 3 1 3 5
4 2 1 3 6
5 1 2 4 8
6 1 2 4 8
7 2 2 4 8


tbState
Id StateName
1 StateA
2 StateB
3 StateC



tbType
Id TypeName ParentId TypeCorresponding
1 TypeA 0 TypeH1
2 TypeB 0 TypeH1
3 TypeAA 1 TypeH2
4 TypeBA 2 TypeH2
5 TypeAAA 3 TypeH3
6 TypeAAB 3 TypeH3
7 TypeBAA 4 TypeH3
8 TypeBAB 4 TypeH3

tbType现在显示的内容相当于3个联动的下拉框,当下拉框选择TypeA时,显示1个选项TypeAA,选择TypeB时,显示1个选项TypeBA,然后当下拉框选择TypeAA时,显示2个选项TypeAAA,TypeAAB,当下拉框选择TypeBA时,显示2个选项TypeBAA,TypeBAB
字段TypeCorresponding显示的内容有2层含义:
1.表示数据的归类 
2.TypeH1对应tableA的Type1,也就是说tableA的Type1字段的数据只能显示1和2,
  TypeH2对应tableA的Type2,也就是说tableA的Type2字段的数据只能显示3和4
  TypeH3对应tableA的Type3,也就是说tableA的Type3字段的数据只能显示5,6,7,8


我想运用动态sql语句显示出这样一个效果:

当在页面上选择tbType的TypeA时,显示

类型 StateA StateB StateC  
TypeAA 1 1 1



当在页面上选择tbType的TypeB时,显示

类型 StateA StateB StateC  
TypeBA 2 1 1


当在页面上选择tbType的TypeAA时,显示

类型 StateA StateB StateC  
TypeAAA 1 0 1
TypeAAB 0 1 0


以此类推,



请问这句动态sql语句应该怎么写?

------解决方案--------------------
SQL code

--> 测试数据:[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(20),[ParentId] INT)
insert INTO [tbType]
select 1,'TypeAAB',3 union all
select 2,'TypeAAA',3 union all
select 3,'TypeAA',0 union all
select 4,'TypeD',0 union all
select 5,'TypeE',0

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
    
    
DECLARE @Type VARCHAR(10)
SET @Type = 'TypeAA'
    
exec('
;WITH List AS(
    SELECT tbType.* FROM tbType WHERE tbType.TypeName='''+@Type+'''
    UNION ALL
    SELECT tbType.* FROM List,tbType WHERE List.Id = tbType.ParentId 
)
select TypeName'+@str+' from ('+'
select 
a.Id,b.StateName,c.TypeName,c.Id as cid
from [tableA] a
left join [tbState] b
on a.State=b.Id
left join [tbType] c
on a.Type=c.Id
'+')t where  t.cid IN(select List.Id from List where List.TypeName<>'''+@Type+''') group by TypeName')


/*

(7 行受影响)

(3 行受影响)

(5 行受影响)
TypeName             StateA      StateB      StateC
-------------------- ----------- ----------- -----------
TypeAAA              2           0           0
TypeAAB              0           1           0

(2 行受影响)

*/