单一记录表的主从关系查询
数据库的需求真是千差万别,今天遇到的要求比较奇怪,客户数据库是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
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*/
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