这句动态语句应该如何写?(小F救救小弟我)

这句动态语句应该怎么写?(小F救救我)
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


以此类推,

补充:TypeAAA和TypeAA只是举个例子,真实的parent级别的TypeName不是只差一位的关系



假如我想显示当在页面上选择tbType的TypeAA时,显示

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


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

[解决办法]
下面代码给你参考吧, 记得下回把Create Table和Insert 先写好再发问题

SQL code

/*create table tableA(id int, state int, type1 int, type2 int, type3 int)create table tbState(id int, StateName varchar(100))create table tbType(id int, TypeName varchar(100), parentId int, TypeCorresponding varchar(100))*/GO/*insert into tableA values(1, 3, 2, 4, 8)insert into tableA values(2, 1, 1, 3, 5)insert into tableA values(3, 3, 1, 3, 5)insert into tableA values(4, 2, 1, 3, 6)insert into tableA values(5, 1, 2, 4, 8)insert into tableA values(6, 1, 2, 4, 8)insert into tableA values(7, 2, 2, 4, 8)insert into tbState values(1, 'StateA')insert into tbState values(2, 'StateB')insert into tbState values(3, 'StateC')insert into tbType values(1, 'TypeA', 0, 'TypeH1')insert into tbType values(2, 'TypeB', 0, 'TypeH1')insert into tbType values(3, 'TypeAA', 1, 'TypeH2')insert into tbType values(4, 'TypeBA', 2, 'TypeH2')insert into tbType values(5, 'TypeAAA', 3, 'TypeH3')insert into tbType values(6, 'TypeAAB', 3, 'TypeH3')insert into tbType values(7, 'TypeBAA', 4, 'TypeH3')insert into tbType values(8, 'TypeBAB', 4, 'TypeH3')*/--传入参数@p, 值取 tyType.id, 例如想传入TypeA, 则@p = 1declare @p intset @p = 1declare @s varchar(4000), @s1 varchar(4000)set @s = ''set @s1 = 'select ts.StateName, tt.typeName, Count(ta.id) cntinto #afrom tbState ts     full join (select * from tbType tt where tt.ParentId = ' + cast(@p as varchar) + ') tt on 1=1    left join tableA ta on ta.state = ts.id         and (ta.Type1 = tt.id or ta.Type2 = tt.id or ta.Type3 = tt.id)group by ts.StateName, tt.TypeName'select @s = @s + 'Sum(case when StateName = ' + char(39) + StateName + char(39) + ' then cnt else 0 end) ' + StateName + ','from tbStateif len(@s) <> 0 set @s = left(@s, len(@s) - 1)Set @s = @s1 + '' + 'select typeName, ' + @s + ' from #a group by typeName order by typeName'print @sexec(@s)
[解决办法]
数据库是2000的话，那就用函数代替
SQL code
CREATE FUNCTION f_Cid(@TypeName VARCHAR(50)) RETURNS @t TABLE(ID INT,Level int) AS BEGIN     DECLARE @ID INT    DECLARE @Level int     SET @Level=1     INSERT @t SELECT Id,@Level  FROM tbType WHERE tbType.TypeName=@TypeName    WHILE @@ROWCOUNT>0     BEGIN         SET @Level=@Level+1         INSERT @t SELECT a.Id,@Level FROM tbType a,@t b WHERE a.parentId=b.ID AND b.Level=@Level-1     END    DELETE @t WHERE ID=(SELECT Id FROM tbType WHERE tbType.TypeName=@TypeName)     RETURN END----DECLARE @Type VARCHAR(10)SET @Type = 'TypeAA'    exec('select TypeName'+@str+' from ('+'select a.Id,b.StateName,c.TypeName,c.Id as cidfrom [tableA] aleft join [tbState] b on a.State=b.Idleft join [tbType] c on a.Type=c.IdINNER JOIN f_Cid('''+@Type+''') T ON T.Id=c.Id'+')t  group by TypeName')