求SQL语句…50点数
表A：
ID ITEM1 ITEM2 ITEM3 ITEM4 ITEM5
A 1 3 4 8 9
A 6 7
A 4 6
A 4 6
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 6
结果如下：
表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 xyzx(ID varchar(10), ITEM1 int, ITEM2 int, ITEM3 int,ITEM4 int,ITEM5 int)
insert xyzx select 'A ', 1 , 3, 4, 8, 9
union all select 'A ', 6, 7,null,null,null
union all select 'A ', 4, 6,null,null,null
union all select 'A ', 4, 6,null,null,null
union all select 'B ', 3, 5, 8, 9,null
union all select 'B ', 3, 4,null,null,null
union all select 'C ', 1, 3, 6 , 7, 8
go
create table xyzy (LSA varchar(10))
insert xyzy 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) '
go
create function getFunX(@a varchar(100),@i int)
returns varchar(1000)
as
begin
declare @s varchar(1000)
declare @x table(id int identity(1,1),a int)
declare @y table(a int)
declare @z table(a varchar(10))
insert @x select top 100 1 from syscolumns
insert @y select cast(substring(@a+ ', ',id,charindex( ', ',@a+ ', ',id+1)-id) as int) from @x where substring( ', '+@a,id,1)= ', '
insert @z select lsa from xyzy where len(lsa)-len(replace(lsa, ', ', ' '))=@i-1
if @i=2
select @s=isnull(@s+ ', ', ' ')+a from(
select distinct a.a from @z a,@y b,@y c where
charindex( ', '+ltrim(b.a)+ ', ',replace(replace(a.a, '( ', ', '), ') ', ', '))> 0 and charindex( ', '+ltrim(c.a)+ ', ',replace(replace(a.a, '( ', ', '), ') ', ', '))> 0
and c.a <> b.a) aa
else if @i=3
select @s=isnull(@s+ ', ', ' ')+a from(
select distinct a.a from @z a,@y b,@y c,@y d where
charindex( ', '+ltrim(b.a)+ ', ',replace(replace(a.a, '( ', ', '), ') ', ', '))> 0 and charindex( ', '+ltrim(c.a)+ ', ',replace(replace(a.a, '( ', ', '), ') ', ', '))> 0
and charindex( ', '+ltrim(d.a)+ ', ',replace(replace(a.a, '( ', ', '), ') ', ', '))> 0
and c.a <> b.a and d.a <> c.a and b.a <> d.a) aa
else if @i=4
select @s=isnull(@s+ ', ', ' ')+a from(
select distinct a.a from @z a,@y b,@y c,@y d,@y e where
charindex( ', '+ltrim(b.a)+ ', ',replace(replace(a.a, '( ', ', '), ') ', ', '))> 0 and charindex( ', '+ltrim(c.a)+ ', ',replace(replace(a.a, '( ', ', '), ') ', ', '))> 0
and charindex( ', '+ltrim(d.a)+ ', ',replace(replace(a.a, '( ', ', '), ') ', ', '))> 0 and charindex( ', '+ltrim(e.a)+ ', ',replace(replace(a.a, '( ', ', '), ') ', ', '))> 0
and c.a <> b.a and d.a <> c.a and b.a <> d.a and e.a <> c.a and e.a <> d.a and e.a <> b.a) aa
return @s
end
go
select *,
dbo.getFunX(isnull(ltrim(item1), ' ')+isnull( ', '+ltrim(item2), ' ')+isnull( ', '+ltrim(item3), ' ')+isnull( ', '+ltrim(item4), ' ')+isnull( ', '+ltrim(item5), ' '),2) L2,
dbo.getFunX(isnull(ltrim(item1), ' ')+isnull( ', '+ltrim(item2), ' ')+isnull( ', '+ltrim(item3), ' ')+isnull( ', '+ltrim(item4), ' ')+isnull( ', '+ltrim(item5), ' '),3) L3,
dbo.getFunX(isnull(ltrim(item1), ' ')+isnull( ', '+ltrim(item2), ' ')+isnull( ', '+ltrim(item3), ' ')+isnull( ', '+ltrim(item4), ' ')+isnull( ', '+ltrim(item5), ' '),4) L4
from xyzx
