查询产生一个树结果

[sun485916]

Field1	Field2	Field3
AA	B100	1
AA	B100	2
AA	B101	1
AA	B101	2
AB	B100	1
AB	B100	2
AC	B100	1

表查询结果如上,想绑定到树形控件.

Field1	Field2	Field3
 --AA
    --B100
          --1
          --2
    --B101
          --1
          --2
 --AB
    --B100
          --1
          --2
 --AC
    --B100
          --1

[sun485916]

绑定dev的treelist控件

[DataBox-MDX]

http://blog.csdn.net/bin_520_yan/article/details/5998349
LZ参考下这个

[sun485916]

[Quote=引用 2 楼 bin_520_yan 的回复:]
http://blog.csdn.net/bin_520_yan/article/details/5998349
LZ参考下这个
[/Quote]
我最初的想法是在查询时前面添加两列,这样就可以绑定treelist控件了.关键这个查询怎么写

FID    FParentID

[i_num]

帮顶吧 等待解答

[sun485916]

[Quote=引用 5 楼 cd731107 的回复:]
SQL code
create table tb(Field1 varchar(6), Field2 varchar(6), Field3 int)
insert tb
select 'AA','B100', 1 union all
select 'AA','B100', 2 union all
select 'AA','B101', 1 union all
……
[/Quote]

谢谢哦
另外 这样的结果查询语句怎么写哦,

FKey	FParentID	Field1	Field2	Field3
1	0	AA	null	null
2	1	AA	B100	null
3	2	AA	B100	1
4	2	AA	B100	2
5	1	AA	B101	null
6	5	AA	B101	1
7	5	AA	B101	2
8	0	AB	null	null
9	8	AB	B100	null
10	9	AB	B100	1
11	9	AB	B100	2
12	0	AC	null	null
13	12	AC	B100	null
14	13	AC	B100	1

[cd731107]

太晚了，明天早上答复你

[cd731107]

create table tb(Field1 varchar(6),    Field2 varchar(6),   Field3 int)
insert tb
select 'AA','B100',    1 union all
select 'AA','B100',    2 union all
select 'AA','B101',    1 union all
select 'AA','B101',    2 union all
select 'AB','B100',    1 union all
select 'AB','B100',    2 union all
select 'AC','B100',    1

select case when Field2 is  null and Field3 is null then Field1 else '' end as a,
       case when Field1 is not null and Field3 is null then Field2 else '' end as b,
       Field3  as c from
(
select Field1,    Field2 ,   Field3
from tb group by Field1,    Field2 ,   Field3
WITH ROLLUP
)tb where Field1 is not null
order by Field1,Field2 ,   Field3

/*
a      b      c
------ ------ ------
AA     NULL   NULL
       B100   NULL
              1
              2
       B101   NULL
              1
              2
AB     NULL   NULL
       B100   NULL
              1
              2
AC     NULL   NULL
       B100   NULL
              1

(14 行受影响)
*/

[cd731107]

create table tb(Field1 varchar(6),    Field2 varchar(6),   Field3 int)
insert tb
select 'AA','B100',    1 union all
select 'AA','B100',    2 union all
select 'AA','B101',    1 union all
select 'AA','B101',    2 union all
select 'AB','B100',    1 union all
select 'AB','B100',    2 union all
select 'AC','B100',    1

--存放到临时表
select identity(int,1,1)FKey,null as FParentID,Field1,Field2,Field3 into #tb
from tb group by Field1,Field2,Field3
WITH ROLLUP
having Field1 is not null
order by Field1,Field2,Field3

--分层更新临时表 
update #tb set FParentID=0 where Field2 is null and Field3 is null

update a set FParentID=b.FKey
from #tb a,#tb b 
where a.Field1=b.Field1
and b.Field2 is null and b.Field3 is null
and a.Field2 is not null and a.Field3 is null

update a set FParentID=b.FKey
from #tb a,#tb b 
where a.Field1=b.Field1 and a.Field2=b.Field2
and b.Field2 is not null and b.Field3 is null
and a.Field2 is not null and a.Field3 is not null

select * from #tb

/*
FKey        FParentID   Field1 Field2 Field3      
----------- ----------- ------ ------ ----------- 
1           0           AA     NULL   NULL
2           1           AA     B100   NULL
3           2           AA     B100   1
4           2           AA     B100   2
5           1           AA     B101   NULL
6           5           AA     B101   1
7           5           AA     B101   2
8           0           AB     NULL   NULL
9           8           AB     B100   NULL
10          9           AB     B100   1
11          9           AB     B100   2
12          0           AC     NULL   NULL
13          12          AC     B100   NULL
14          13          AC     B100   1

（所影响的行数为 14 行）
*/

[sun485916]

高手哦

非常感谢你