ID A B memo
1 A1 tw donty
2 A2 A1 2121
3 A3 A2 2222
4 A4 A3 2323
5 A5 A4 2424
6 A6 A5 2525
7 B1 B5 sdfcxv
8 B2 B3 sdfgwe5r
9 B3 B2 dfhgwer
10 B4 B1 asger
11 B5 testB test
表test中有A、B两列,A列为品号,B列为变更前品号
打开比方:A1变更前品号为tw,A2变更前品号为A1,A3变更前品号为A2等
现在要输入品号A3时,把相关的数据(A1,A2,A3,A4,A5,A6所在行)都查询出来!
同样,当输入A4时,查询结果也同A3一样的
1 A1 tw donty
2 A2 A1 2121
3 A3 A2 2222
4 A4 A3 2323
5 A5 A4 2424
6 A6 A5 2525
go
--> -->
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[A] nvarchar(2),[B] nvarchar(5),[memo] nvarchar(8))
Insert #T
select 1,N'A1',N'tw',N'donty' union all
select 2,N'A2',N'A1',N'2121' union all
select 3,N'A3',N'A2',N'2222' union all
select 4,N'A4',N'A3',N'2323' union all
select 5,N'A5',N'A4',N'2424' union all
select 6,N'A6',N'A5',N'2525' union all
select 7,N'B1',N'B5',N'sdfcxv' union all
select 8,N'B2',N'B3',N'sdfgwe5r' union all
select 9,N'B3',N'B2',N'dfhgwer' union all
select 10,N'B4',N'B1',N'asger' union all
select 11,N'B5',N'testB',N'test'
Go;with c as
(
Select * from #T where B='A3'
union all
select t2.* from C t inner join #T t2 on t.[A]=t2.[B]
),c2
as
(select * from #T where A='A3'
union all
select t.* from C2 inner join #T t on c2.B=t.A
)
select * from C2
union
select * from C
(11 個資料列受到影響)
ID A B memo
----------- ---- ----- --------
1 A1 tw donty
2 A2 A1 2121
3 A3 A2 2222
4 A4 A3 2323
5 A5 A4 2424
6 A6 A5 2525(6 個資料列受到影響)
谢谢关注!
请问使用Sql Server 2000呢?
go
--> -->
set nocount on ;
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[A] nvarchar(2),[B] nvarchar(5),[memo] nvarchar(8))
Insert #T
select 1,N'A1',N'tw',N'donty' union all
select 2,N'A2',N'A1',N'2121' union all
select 3,N'A3',N'A2',N'2222' union all
select 4,N'A4',N'A3',N'2323' union all
select 5,N'A5',N'A4',N'2424' union all
select 6,N'A6',N'A5',N'2525' union all
select 7,N'B1',N'B5',N'sdfcxv' union all
select 8,N'B2',N'B3',N'sdfgwe5r' union all
select 9,N'B3',N'B2',N'dfhgwer' union all
select 10,N'B4',N'B1',N'asger' union all
select 11,N'B5',N'testB',N'test'
Godeclare @T table([ID] int,[A] nvarchar(2),[B] nvarchar(5),[memo] nvarchar(8))
insert @T select * from #T where B='A3'
while @@rowcount>0
insert @T select t.* from #T t inner join @T t2 on t2.A=t.B where not exists(select 1 from @T where A=t.A)
insert @T select * from #T where A='A3'while @@rowcount>0
insert @T select t.* from #T t inner join @T t2 on t2.B=t.A where not exists(select 1 from @T where A=t.A)select * from @T order by 1/*
ID A B memo
----------- ---- ----- --------
1 A1 tw donty
2 A2 A1 2121
3 A3 A2 2222
4 A4 A3 2323
5 A5 A4 2424
6 A6 A5 2525
*/
[DA007] varchar(50),[DA008] varchar(50),[DA009] varchar(8000),[DA010] varchar(8000),[DA012] varchar(8000),[DA013] varchar(8000),
[DA014] varchar(50),[DA015] varchar(50),[DA016] varchar(50),[DA017] varchar(8000),[DA018] varchar(50),[DA019] datetime,
[DA020] varchar(50),[DA021] varchar(500),[DA022] datetime,[DA023] varchar(50),[DA024] datetime,[DA025] varchar(50),
[DA026] varchar(50),[DA027] varchar(50),[DA028] varchar(50),[DA029] varchar(50),[DA030] varchar(50),[DA031] varchar(50),
[DA032] varchar(50),[DA033] varchar(8000),[DA034] varchar(50),[DA035] varchar(8000),[DA036] varchar(8000),[DA037] varchar(8000),
[DA038] varchar(50),[DA039] varchar(8000))declare @DA026 nvarchar(50)
set @DA026='test'
insert @TW select * from WMISDA where DA026=@DA026while @@rowcount>0
insert @TW select t.* from WMISDA t inner join @TW t2 on t2.DA004=t.DA026 where not exists(select 1 from @TW where DA004=t.DA004)insert @TW select * from WMISDA where DA004=@DA026 and not exists(select 1 from @TW where DA004=WMISDA.DA004)while @@rowcount>0
insert @TW select t.* from WMISDA t inner join @TW t2 on t2.DA026=t.DA004 where not exists(select 1 from @TW where DA004=t.DA004)select * from @TW order by 1
select * from #t where B like 'A%'
select * from #t where B like 'A%'
我的假设是:字段A是没有重复记录的SQL> select *
2 from mt
3 order by id
4 ; ID A B
---------- -- --
1 A1 tw
2 A2 A1
3 A3 A2
4 A4 A3
5 A5 A4
6 A6 A5
7 B1 B5
8 B2 B3
9 B3 B2
10 B4 B1
11 B5 te已选择11行。SQL> select *
2 from mt
3 start with a='A3'
4 connect by nocycle prior a in (a,b) or prior b in (a,b)
5 order by id
6 ; ID A B
---------- -- --
1 A1 tw
2 A2 A1
3 A3 A2
4 A4 A3
5 A5 A4
6 A6 A5已选择6行。
原来表的声明问题!
declare @TW TABLE([DA001] int,[DA004] [varchar] (50) COLLATE Chinese_PRC_BIN NULL ,[DA026] [varchar] (50) COLLATE Chinese_PRC_BIN NULL)
这样就可以了!!!再次谢谢各位!