27,579
社区成员
发帖
与我相关
我的任务
分享
最好给出完整的表结构,测试数据,计算方法和正确结果.
---
贴出数据和关联,
从你的述描中可以用 join 再加上函数 charindex应该能做到...........
-- 这个意思???
if object_id('a') is not null
drop table a;
go
create table [a] (LowUserID varchar(max));
go
insert into [a]
select '78ddfd|dfddfk' union all select '*' union all select '*%78ddfd|dfddfk';
go
if object_id('user') is not null
drop table [user];
go
create table [user] (id varchar(50), name varchar(50));
go
insert into [user]
select '78ddfd','AAA' union all select 'dfddfk','BBB' union all
select 'xxxxxx','XXX';
go
select * from a,[user] u
where charindex('*%',a.LowUserID)>0 and charindex(u.id,a.LowUserID)=0
union all
select * from a,[user] u where a.LowUserID='*'
union all
select * from a,[user] u
where charindex(u.id,a.LowUserID)>0 and charindex('*%',a.LowUserID)=0 and a.LowUserID<>'*';
/*
*%78ddfd|dfddfk xxxxxx XXX
* 78ddfd AAA
* dfddfk BBB
* xxxxxx XXX
78ddfd|dfddfk 78ddfd AAA
78ddfd|dfddfk dfddfk BBB
*/
declare @name varchar(50)
set @name='xxx'
select LowUserID from 表A
where LowUserID='*'
or (left(LowUserID,2)<>'*%' and exists(select ID from [User] where name like '%'+@name+'%' and charindex(ID,LowUserID)>0))
or (left(LowUserID,2)='*%' and not exists(select ID from [User] where name like '%'+@name+'%' and charindex(ID,LowUserID)>0))