单一记录表的主从记录查询

/*数据库的需求真是千差万别,今天遇到的要求比较奇怪,客户数据库是2k版本*/

--测试数据,从数据来看是原来设计者没有考虑周全,主从关系最好设置两个字段sid(SelfID)和pid(ParentID)

if not object_id('tempdb..#T') is null
drop table #T
if not object_id('tempdb..#') is null
drop table #
create table #T(id int identity(1,1),name char(3),state char(4),time char(8),value decimal(5,2))
insert into #T
select 'AAA' as name,'PASS' as state,'20081023' as time,null as value union all
select 'FGH','FAIL',null,88.9 union all
select 'GHE','PASS',null,32 union all
select 'ABC','FAIL','20080930',null union all
select 'WRH','FAIL',null,2 union all
select 'BBB','PASS','20080101',null union all
select 'CCC','PASS','20080101',null union all
select 'ERT','PASS',null,33
go

--将初步的数据插入临时表#
select a.id,a.name,a.state,a.time,b.id as subid,b.name as subname,b.state as substate,b.value into #
from #T a left join #T b on a.value is null and b.time is null and a.idtb.id)

--results
AAA PASS 20081023 FGH FAIL 88.90
AAA PASS 20081023 GHE PASS 32.00
ABC FAIL 20080930 WRH FAIL 2.00
CCC PASS 20080101 ERT PASS 33.00

/*上述部分是老需求，现在要把没有子节点的父节点选出来
试验了很久，最终还是只有用cursor，以后有好方法了再更新吧 */

if not object_id('tempdb..#T') is null
drop table #T
go
if not object_id('tempdb..#') is null
drop table #
go
create table #T(id int identity(1,1),name char(3),state char(4),time char(8),value decimal(5,2))
go
create table #(name char(3),state char(4),time char(8),subname char(3),substate char(4),value decimal(5,2))
go
insert into #T
select 'AAA' as name,'PASS' as state,'20081023' as time,null as value union all
select 'FGH','FAIL',null,88.9 union all
select 'GHE','PASS',null,32 union all
select 'ABC','FAIL','20080930',null union all
select 'WRH','FAIL',null,2 union all
select 'BBB','PASS','20080101',null union all
select 'CCC','PASS','20080101',null union all
select 'ERT','PASS',null,33
go
--select * from #T

declare @id int,@time char(8),@name char(3),@state char(4),@value decimal(5,2),@px int;
set @px=1;
declare tb_cursor cursor LOCAL FORWARD_ONLY KEYSET READ_ONLY for
select id,time,name,state,value from #T
OPEN tb_cursor
FETCH tb_cursor into @id,@time,@name,@state,@value

WHILE @@FETCH_STATUS = 0
BEGIN
--是父节点，而且与上一父节点不相连
if(@time is not null and @id-@px<>1)
begin
set @px=@id
end
--是父节点，与上一父节点相连
else if (@time is not null and @id-@px=1)
begin
insert into # select name,state,time,null,null,null from #T where id=@px
set @px=@id
end
--子节点
else
begin
insert into # select a.name,a.state,a.time,subname=@name,substate=@state,value=@value from #T a where id=@px
end

FETCH tb_cursor into @id,@time,@name,@state,@value
END

--关闭并删除cursor
CLOSE tb_cursor
DEALLOCATE tb_cursor

--查询语句
select * from #

--result
AAA PASS 20081023 FGH FAIL 88.90
AAA PASS 20081023 GHE PASS 32.00
ABC FAIL 20080930 WRH FAIL 2.00
BBB PASS 20080101 NULL NULL NULL
CCC PASS 20080101 ERT PASS 33.00

/*终于有了BBB这条记录*/
BBB PASS 20080101 NULL NULL NULL

原创文章若转载请注明：转载自imac的博文@http://imac-macheng.blogspot.com/