例如有一个表TableA 字段 Key,A,B,C 我要查出一个变量X like '%C%' 并且 如果 字段A 和字段B 同时相同的话我只要其中的任意一天记录就可以,我应该怎么写????就是如果变量X like '%C%' 查出了7条记录,其中存在 字段A 和字段B 完全相同的记录有3条(可以看成A 、B是联合主键) 我想要的就是 3条中任意1条记录和7-4条记录 就是我想要这5记录(7-4+(3-1))
调试欢乐多
from ta where X like '%'+C+'%' select *
from ta where charindex(X ,C)>0
key A B C
402880a51f056d3a011f0595d55b000d ff09010710151301 YU-009 ,boss,
402880a51f056d3a011f0595d55b000e ff09010710151301 YU-009 ,boss,
402880a51f056d3a011f0595d55b000f ff09010710151301 YU-009 ,boss,ttt,
402880a51f056d3a011f0595d55b0010 ff09010710151301 YU-009 ,boss,
402880a51f056d3a011f0595d55b0010 ff09010710222222 YU-009 ,boss,
402880a51f056d3a011f059111111111 ff09010711153456 YU-123 ,abcd,
402880a51f056d3a011f059111111111 ff09010711111111 YU-001 ,boss,结果402880a51f056d3a011f0595d55b000d ff09010710151301 YU-009 ,boss,(数据中前4条记录的任意一条均可)
402880a51f056d3a011f0595d55b0010 ff09010710222222 YU-009 ,boss,
402880a51f056d3a011f059111111111 ff09010711111111 YU-001 ,boss,
if object_id('tempdb..#')is not null drop table #
go
create table #([key] varchar(40), A varchar(20), B varchar(20),C varchar(20))
insert # select '402880a51f056d3a011f0595d55b000d' ,'ff09010710151301', 'YU-009' ,'boss'
insert # select '402880a51f056d3a011f0595d55b000e' ,'ff09010710151301' ,'YU-009' ,'boss'
insert # select '402880a51f056d3a011f0595d55b000f' ,'ff09010710151301' ,'YU-009' ,'boss,ttt'
insert # select '402880a51f056d3a011f0595d55b0010', 'ff09010710151301' ,'YU-009' ,'boss'
insert # select '402880a51f056d3a011f0595d55b0010', 'ff09010710222222' ,'YU-009' ,'boss'
insert # select '402880a51f056d3a011f059111111111', 'ff09010711153456' ,'YU-123' ,'abcd'
insert # select '402880a51f056d3a011f059111111111', 'ff09010711111111' ,'YU-001' ,'boss'
select * from # t where not exists(select 1 from # where a=t.a and b=t.b and [key]>t.[key]) and charindex(',boss,',','+C+',')>0
/*key A B C
---------------------------------------- -------------------- -------------------- --------------------
402880a51f056d3a011f0595d55b0010 ff09010710151301 YU-009 boss
402880a51f056d3a011f0595d55b0010 ff09010710222222 YU-009 boss
402880a51f056d3a011f059111111111 ff09010711111111 YU-001 boss
*/
如果我把boss改成ttt应该可以查出
数据
402880a51f056d3a011f0595d55b000f ff09010710151301 YU-009 ,boss,ttt,
引用 3 楼 hebin0819 的回复:
数据
key A B C
402880a51f056d3a011f0595d55b000d ff09010710151301 YU-009 ,boss,
402880a51f056d3a011f0595d55b000e ff09010710151301 YU-009 ,boss,
402880a51f056d3a011f0595d55b000f ff09010710151301 YU-009 ,boss,ttt,
402880a51f056d3a011f0595d55b0010 ff09010710151301 YU-009 ,boss,
402880a51f056d…
select * from # t where not exists(select 1 from # where a=t.a and b=t.b and [key]>t.[key]) and charindex(',ttt,',','+C+',')>0
如果我把boss改成ttt应该可以查出
数据
402880a51f056d3a011f0595d55b000f ff09010710151301 YU-009 ,boss,ttt,
可是现在不可以因该怎么改?
from # t
where not exists(select 1 from # where a=t.a and b=t.b and [key]>t.[key]) and charindex(',ttt,',','+C+case when right(c,1) = ',' then '' else ',' end )>0