--建表
create table b
(
usrid int,
hd varchar(20)
)insert into b select 1, '领取暖壶'
insert into b select 1, '领取暖壶'
insert into b select 2, '领取被褥'
insert into b select 3, '领取被褥'
insert into b select 2, '领取床单'
insert into b select 4, '领取暖壶'
insert into b select 5, '领取暖壶'--语句
select * from b t1
where charindex('暖壶',hd)>0
and not exists
(select 1 from b where usrid = t1.usrid and (charindex('床单',hd)>0 or charindex('被褥',hd)>0 ))--结果
4 领取暖壶
5 领取暖壶
create table b
(
usrid int,
hd varchar(20)
)insert into b select 1, '领取暖壶'
insert into b select 1, '领取暖壶'
insert into b select 2, '领取被褥'
insert into b select 3, '领取被褥'
insert into b select 2, '领取床单'
insert into b select 4, '领取暖壶'
insert into b select 5, '领取暖壶'--语句
select * from b t1
where charindex('暖壶',hd)>0
and not exists
(select 1 from b where usrid = t1.usrid and (charindex('床单',hd)>0 or charindex('被褥',hd)>0 ))--结果
4 领取暖壶
5 领取暖壶
比如 B表里面没有UsrId =4,= 5 2个人的ID 怎么办? A表.usrid=B表.usridB 表
UsrId 活动 1 领取暖壶
2 领取被褥
3 领取被褥
2 领取床单 如:查 领了暖壶的和没领床单的和没领被褥的,那结果应该是 usrid = 1
不知道我说清楚了没有。谢谢您了
INSERT @a SELECT 1,'aaa'
UNION SELECT 2,'bbb'
UNION SELECT 3, 'ccc'
UNION SELECT 4, 'ddd'
UNION SELECT 5,'eee'DECLARE @b TABLE(UserID INT,Method VARCHAR(10))
INSERT @b SELECT 1 , '领取暧壶'
UNION SELECT 1 , '领取被褥'
UNION SELECT 2 , '领取被褥'
UNION SELECT 3 , '领取被褥'
UNION SELECT 2 , '领取床单'DECLARE @s VARCHAR(200),@s1 VARCHAR(200),@s2 VARCHAR(200)
SELECT @s1='',@s2='',@s='领取被褥、暧壶,没有床单' --@s变量更改为你要查询的语句即可。邻取在前,没有在后,二者以“,”分隔,物品之间用"、"分隔
SELECT @s1=CASE WHEN CHARINDEX('领取',@s)>0 THEN
CASE WHEN CHARINDEX(',',@s)>0 THEN
SUBSTRING(@s,3,CHARINDEX(',',@s)-3)
ELSE
@s
END
END,
@s2=CASE WHEN CHARINDEX(',',@s)>0 THEN
SUBSTRING(@s,CHARINDEX(',',@s)+3,LEN(@s))
ELSE '' ENDSELECT DISTINCT UserID FROM
(SELECT a.UserID,'领取' + s1.a xMethod
FROM @a a
INNER JOIN dbo.SPlit(@s1,'、') s1
ON 1=1
) s1
WHERE NOT EXISTS
(
SELECT 1 FROM
(
SELECT a.UserID,'领取' + s1.a xMethod
FROM @a a
INNER JOIN dbo.SPlit(@s1,'、') s1
ON 1=1
) x
WHERE x.UserID=s1.UserID AND xMethod NOT IN(SELECT Method FROM
(
SELECT a.UserID UserID,a.UserName,b.Method Method
FROM @a a
INNER JOIN @b b
ON a.UserID=b.UserID
WHERE a.UserID=s1.UserID
) y
)
)
AND NOT EXISTS
(
SELECT a.UserID UserID,a.UserName,b.Method Method
FROM @a a
INNER JOIN @b b
ON a.UserID=b.UserID
WHERE a.UserID=s1.UserID AND b.Method IN
(
SELECT '领取' + a xMethod FROM dbo.SPlit(@s2,'、') s2
)
)
SELECT * FROM @a a, @b b WHERE a.UserID=b.UserID/*
1 aaa 1 领取暧壶
1 aaa 1 领取被褥
2 bbb 2 领取被褥
2 bbb 2 领取床单
3 ccc 3 领取被褥*/--查询语句
--@s='领取被褥、暧壶,没有床单'
--查询结果
/*
UserID
1
*/--将查询语句改为:
--@s='领取被褥,没有床单'
--查询结果
/*
UserID
1
3
*/附Split函数:
Create Function Split(@Sql varchar(8000),@Splits varchar(10))
returns @temp Table (a varchar(100))
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i >= 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
EndIf @Sql <> ''
Insert @temp Values (@Sql)
Return
End