求sql语句，最好是效能高的
表A：
ID ITEM1 ITEM2 ITEM3 ITEM4 ITEM5
A 1 3 4 8 9
A 6 7
A 4 9
A 4 9
B 3 5 8 9
B 3 4
C 1 3 6 7 8
表LS：
LSA
(1,3)
(3,4)
(4,9)
(5,8)
(8,9)
…
(1,3,4)
(3,4,9)
(5,8,9)
…
(1,3,4,9)
…
1.找表A有含表LS的，果如下表A1。
2.表A的 L2,L3,L4 字段是表Ls中的组合,表A有重复的组合，要如何删除第二笔以后的，只留第一笔，如ID A的4 9
结果如下：
表A1：
ID ITEM1 ITEM2 ITEM3 ITEM4 ITEM5 L2 L3 L4
A 1 3 4 8 9 (1,3),(3,4),(4,9) (1,3,4),(3,4,9) (1,3,4,9)
A 6 7
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)
[解决办法]
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.px
drop 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 行）
*/
[解决办法]
--原始数据：@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
/*
IDITEM1ITEM2ITEM3ITEM4ITEM5L2L3L4
A13489(1,3),(3,4),(4,9),(8,9)(1,3,4),(3,4,9)(1,3,4,9)
A67
A49(4,9)
A49(4,9)
B3589(5,8),(8,9)(5,8,9)
B34(3,4)
C13678(1,3)
*/