求sql语句，最好是效能高的

Question

create table A(ID varchar(10),ITEM1 varchar(10),ITEM2 varchar(10),ITEM3 varchar(10),ITEM4 varchar(10),ITEM5 varchar(10))
insert into A values('A','1','3','4','8','9')
insert into A values('A','6','7','' ,'' ,'')
insert into A values('A','4','9','' ,'' ,'')
insert into A values('A','4','9','' ,'' ,'')
insert into A values('B','3','5','8','9','')
insert into A values('B','3','4','' ,'', '')
insert into A values('C','1','3','6','7','8')
create table LS(LSA varchar(10))
insert into LS values('(1,3)')
insert into LS values('(3,4)')
insert into LS values('(4,9)')
insert into LS values('(5,8)')
insert into LS values('(8,9)')
insert into LS values('(1,3,4)')
insert into LS values('(3,4,9)')
insert into LS values('(5,8,9)')
insert into LS values('(1,3,4,9)')
go
select px=identity(int,1,1) , * into temp_A from A
select t.* into temp_B from 
(
  select px , id , num = 2 , item  = '('+item1+','+item2+')' from temp_A
  union all
  select px , id , num = 2 , item  = '('+item1+','+item3+')' from temp_A where item3 is not null AND item3 <> ''
  union all
  select px , id , num = 2 , item  = '('+item1+','+item4+')' from temp_A where item4 is not null AND item4 <> ''
  union all
  select px , id , num = 2 , item  = '('+item1+','+item5+')' from temp_A where item5 is not null AND item5 <> ''
  union all
  select px , id , num = 2 , item  = '('+item2+','+item3+')' from temp_A where item3 is not null AND item3 <> ''
  union all
  select px , id , num = 2 , item  = '('+item2+','+item4+')' from temp_A where item4 is not null AND item4 <> ''
  union all
  select px , id , num = 2 , item  = '('+item2+','+item5+')' from temp_A where item5 is not null AND item5 <> ''
  union all
  select px , id , num = 2 , item  = '('+item3+','+item4+')' from temp_A where item3 is not null AND item3 <> '' AND item4 is not null AND item4 <> ''
  union all
  select px , id , num = 2 , item  = '('+item3+','+item5+')' from temp_A where item3 is not null AND item3 <> '' AND item5 is not null AND item5 <> ''
  union all
  select px , id , num = 2 , item  = '('+item4+','+item5+')' from temp_A where item4 is not null AND item4 <> '' AND item5 is not null AND item5 <> ''
  union all
  select px , id , num = 3 , item  = '('+item1+','+item2+','+item3+')' from temp_A where item3 is not null AND item3 <> ''
  union all
  select px , id , num = 3 , item  = '('+item1+','+item2+','+item4+')' from temp_A where item4 is not null AND item4 <> ''
  union all
  select px , id , num = 3 , item  = '('+item1+','+item2+','+item5+')' from temp_A where item5 is not null AND item5 <> ''
  union all
  select px , id , num = 3 , item  = '('+item2+','+item3+','+item4+')' from temp_A where item3 is not null AND item3 <> '' and item4 is not null AND item4 <> ''
  union all
  select px , id , num = 3 , item  = '('+item2+','+item3+','+item5+')' from temp_A where item3 is not null AND item3 <> '' and item5 is not null AND item5 <> ''
  union all
  select px , id , num = 3 , item  = '('+item3+','+item4+','+item5+')' from temp_A where item3 is not null AND item3 <> '' and item4 is not null AND item4 <> '' and item5 is not null AND item5 <> ''
  union all
  select px , id , num = 4 , item  = '('+item1+','+item2+','+item3+','+item4+')' from temp_A where item3 is not null AND item3 <> '' and item4 is not null AND item4 <> ''
  union all
  select px , id , num = 4 , item  = '('+item1+','+item2+','+item3+','+item5+')' from temp_A where item3 is not null AND item3 <> '' and item5 is not null AND item5 <> ''
  union all
  select px , id , num = 4 , item  = '('+item1+','+item2+','+item4+','+item5+')' from temp_A where item4 is not null AND item4 <> '' and item5 is not null AND item5 <> ''
  union all
  select px , id , num = 4 , item  = '('+item1+','+item3+','+item4+','+item5+')' from temp_A where item3 is not null AND item3 <> '' and item4 is not null AND item4 <> '' and item5 is not null AND item5 <> ''
  union all
  select px , id , num = 4 , item  = '('+item2+','+item3+','+item4+','+item5+')' from temp_A where item3 is not null AND item3 <> '' and item4 is not null AND item4 <> '' and item5 is not null AND item5 <> ''
) t , LS
where t.item = ls.lsa
go
--创建一个合并的函数
create function f_hb(@px int , @id varchar(10) , @num int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(item as varchar) from temp_b where px = @px and id = @id and num = @num
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：select t.id,t.ITEM1,t.ITEM2,t.ITEM3,t.ITEM4,t.ITEM5,isnull(t1.L2,'') L2,isnull(t2.L3,'') L3,isnull(t3.L4,'') L4 from temp_A t
left join (select distinct px,id,num ,dbo.f_hb(px,id,num) as L2 from temp_b where num = 2) t1 on t.px = t1.px
left join (select distinct px,id,num ,dbo.f_hb(px,id,num) as L3 from temp_b where num = 3) t2 on t.px = t2.px
left join (select distinct px,id,num ,dbo.f_hb(px,id,num) as L4 from temp_b where num = 4) t3 on t.px = t3.pxdrop table A,LS,temp_A,temp_B
drop function f_hb/*
id ITEM1 ITEM2 ITEM3 ITEM4 ITEM5 L2                      L3              L4       
-- ----- ----- ----- ----- ----- ----------------------- --------------- ---------
A  1     3     4     8     9     (1,3),(3,4),(4,9),(8,9) (1,3,4),(3,4,9) (1,3,4,9)
A  4     9                       (4,9)
A  4     9                       (4,9)
B  3     5     8     9           (5,8),(8,9)             (5,8,9)
B  3     4                       (3,4)
C  1     3     6     7     8     (1,3)
A  6     7
（所影响的行数为 7 行）
*/

Answer 1

TO:dawugui(潇洒老乌龟)
您的语法有错，跑不出来

Answer 2

我的是SQL2000,我的都跑出来,你的怎么可能不行?
要不你把这段内容拷贝过去试试?

Answer 3

--原始数据：@A
declare @A table (ID varchar(1),ITEM1 varchar(10),ITEM2 varchar(10),ITEM3 varchar(10),ITEM4 varchar(10),ITEM5 varchar(10))
insert @A
select 'A',1,3,4,8,9 union all
select 'A',6,7,'','','' union all
select 'A',4,9,'','','' union all
select 'A',4,9,'','','' union all
select 'B',3,5,8,9,'' union all
select 'B',3,4,'','','' union all
select 'C',1,3,6,7,8
--原始数据：@LS
declare @LS table (LSA varchar(9))
insert @LS
select '(1,3)' union all
select '(3,4)' union all
select '(4,9)' union all
select '(5,8)' union all
select '(8,9)' union all
select '(1,3,4)' union all
select '(3,4,9)' union all
select '(5,8,9)' union all
select '(1,3,4,9)'
--这样似乎更简单，也更直接：
declare @B table (ID varchar(1),ITEM1 varchar(10),ITEM2 varchar(10),ITEM3 varchar(10),ITEM4 varchar(10),ITEM5 varchar(10),L2 varchar(100),L3 varchar(100),L4 varchar(100))
insert @B select *,null,null,null from @A
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM1+','+b.ITEM2+')'=c.LSA
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM1+','+b.ITEM3+')'=c.LSA
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM1+','+b.ITEM4+')'=c.LSA
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM1+','+b.ITEM5+')'=c.LSA
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM2+','+b.ITEM3+')'=c.LSA
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM2+','+b.ITEM4+')'=c.LSA
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM2+','+b.ITEM5+')'=c.LSA
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM3+','+b.ITEM4+')'=c.LSA
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM3+','+b.ITEM5+')'=c.LSA
update b set b.L2=coalesce(b.L2+',','')+c.LSA from @B b join @LS c on '('+b.ITEM4+','+b.ITEM5+')'=c.LSA
update b set b.L3=coalesce(b.L3+',','')+c.LSA from @B b join @LS c on '('+b.ITEM1+','+b.ITEM2+','+b.ITEM3+')'=c.LSA
update b set b.L3=coalesce(b.L3+',','')+c.LSA from @B b join @LS c on '('+b.ITEM1+','+b.ITEM2+','+b.ITEM4+')'=c.LSA
update b set b.L3=coalesce(b.L3+',','')+c.LSA from @B b join @LS c on '('+b.ITEM1+','+b.ITEM2+','+b.ITEM5+')'=c.LSA
update b set b.L3=coalesce(b.L3+',','')+c.LSA from @B b join @LS c on '('+b.ITEM2+','+b.ITEM3+','+b.ITEM4+')'=c.LSA
update b set b.L3=coalesce(b.L3+',','')+c.LSA from @B b join @LS c on '('+b.ITEM2+','+b.ITEM3+','+b.ITEM5+')'=c.LSA
update b set b.L3=coalesce(b.L3+',','')+c.LSA from @B b join @LS c on '('+b.ITEM3+','+b.ITEM4+','+b.ITEM5+')'=c.LSA
update b set b.L4=coalesce(b.L4+',','')+c.LSA from @B b join @LS c on '('+b.ITEM1+','+b.ITEM2+','+b.ITEM3+','+b.ITEM4+')'=c.LSA
update b set b.L4=coalesce(b.L4+',','')+c.LSA from @B b join @LS c on '('+b.ITEM1+','+b.ITEM2+','+b.ITEM3+','+b.ITEM5+')'=c.LSA
update b set b.L4=coalesce(b.L4+',','')+c.LSA from @B b join @LS c on '('+b.ITEM2+','+b.ITEM3+','+b.ITEM4+','+b.ITEM5+')'=c.LSA
update @B set L2=isnull(L2,''),L3=isnull(L3,''),L4=isnull(L4,'')select * from @B
/*
ID ITEM1 ITEM2 ITEM3 ITEM4 ITEM5 L2 L3 L4 
A 1 3 4 8 9 (1,3),(3,4),(4,9),(8,9) (1,3,4),(3,4,9) (1,3,4,9)
A 6 7
A 4 9 (4,9)
A 4 9 (4,9)
B 3 5 8 9 (5,8),(8,9) (5,8,9)
B 3 4 (3,4)
C 1 3 6 7 8 (1,3)
*/

Answer 4

TO:Limpire(昨夜小楼)
关于如何去重复，另一贴有问题, 若我将下面的A1改为B1, 结果就不对了。例如B1的(857)不该删，但删了等。
结果如下：
A1 02/06 (857)
A1 06/11
A1 08/02 (855)           (855,857)
B1 08/05 (851)     (874) (851,857),(851,874),(857,874) (851,857,874)
B1 10/21 (873)        (855,857),(855,873),(857,873) (855,857,873)
B1 10/25

Answer 5

TO:Limpire(昨夜小楼)
关于如何去重复，另一贴有问题, 若我将下面的A1改为B1, 结果就不对了。例如B1的(857)不该删，但删了等。
----------------------------------
是的，理解错误，抱歉！现已修正。