日期:2014-05-18  浏览次数:20475 次

求助 SQL查询
字段 A 字段B 字段C
L|P|K|2J 3|3|3|0 3
2J|L|P|K|F 5|5|5|5 5
F|2J|L 12|0|0 12
L|F|K 0|0|0 7

字段A用 "|" 分割 得到的数据和 字段B分割得到的数据对应
如第一行
字段A得到 "L","P","K","2J" 字段B分割得到 "3","3","3","0"
L对应的是3
我现在想要查询到的结果是 
字段A like '%L%' and 字段B分割后的对应L的数字=字段C
另外还要查询 
字段A like '%2J%' and 字段B分割后的对应2J的数字=字段C 

请大家指点一下谢谢

------解决方案--------------------
SQL code

create table imx
(colA varchar(10), 
 colB varchar(10), 
 colC varchar(10)
)

insert into imx
select 'L|P|K|2J', '3|3|3|0', '3' union all
select '2J|L|P|K|F', '5|5|5|5', '5' union all
select 'F|2J|L', '12|0|0', '12' union all
select 'L|F|K', '0|0|0', '7'


create function dbo.fun_imx
(@colA varchar(10),
 @colB varchar(10),
 @colC varchar(10),
 @x varchar(10)
) returns char(1)
begin
 declare @tca table(id int identity(1,1),colA varchar(10))
 declare @tcb table(id int identity(1,1),colB varchar(10))
 
 insert into @tca(colA)
 select substring(a.col,b.number,
                  charindex('|',a.col+'|',b.number)-b.number) col
 from (select @colA col) a
 inner join master.dbo.spt_values b
 on b.type='P' and substring('|'+a.col,b.number,1)='|'
 
 insert into @tcb(colB)
 select substring(a.col,b.number,
                  charindex('|',a.col+'|',b.number)-b.number) col
 from (select @colB col) a
 inner join master.dbo.spt_values b
 on b.type='P' and substring('|'+a.col,b.number,1)='|'
 
 declare @r char(1)
 if (exists(select 1 from @tca where colA=@x) 
     and exists(select 1 from @tcb b,@tca a where b.id=a.id and a.colA=@x)
     and (select b.colB from @tcb b,@tca a where b.id=a.id and a.colA=@x)=@colC)
    select @r='Y'
 else
    select @r='N'  
   
 return @r
end


declare @x varchar(10)
select @x='L'

select * from imx
where dbo.fun_imx(colA,colB,colC,@x)='Y'


colA        colB        colC
----------  ---------- ----------
L|P|K|2J    3|3|3|0     3
2J|L|P|K|F  5|5|5|5     5

(2 row(s) affected)