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

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

--测试数据及表结构
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
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.id<b.id where a.time is not null
--select * from #
--查询语句
select name,state,time,subname,substate,value from # tb
where not exists( select 1 from # where subid=tb.subid and id>tb.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，以后有好方法了在更新吧。其实在05里面也可以用CTE来优化查询

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    PASS    20080101    NULL NULL    NULL*/

原创文章若转载请注明：转载自imac's 博文@http://ilovestg.cnblogs.com