SQL Server-仅当同一列和表中的用户ID存在值B时，才排除值为A的所有行

Question

如果可能的话，我需要以下内容的示例查询。

我似乎什么都做不到(我试过了，如果存在的话，没有，等等(。我现在只有一个基本的select语句，我只是被卡住了，我的SQL很糟糕：

Select
UserID,
Date,
Type,
Step
from TableA

如果一个用户ID具有带有"的行；值A"；与行"AND"；值B"；(值A的至少一行(；步骤"；列，则仅显示具有"0"的行；值B"；(排除包含"值A"的所有行(。

For example:
Original Table Source:
UserID | Date      | Type | Step
-------+-----------+------+---------
24261  | 12/4/2021 | Doc  | Value B
24261  | 12/4/2021 | Sig  | Value A
24315  | 12/4/2021 | Sig  | Value A
24331  | 12/4/2021 | Doc  | Value B
24331  | 12/4/2021 | Sig  | Value A
24328  | 12/4/2021 | Text | Value C
24341  | 12/4/2021 | Doc  | Value B
24341  | 12/4/2021 | Sig  | Value A
24341  | 12/4/2021 | Doc  | Value B
24341  | 12/4/2021 | Sig  | Value A
24357  | 12/4/2021 | Text | Value C
24357  | 12/4/2021 | Sig  | Value A
24357  | 12/4/2021 | text | Value C

预期输出：

UserID | Date      | Type | Step
-------+-----------+------+---------
24261  | 12/4/2021 | Doc  | Value B
24315  | 12/4/2021 | Sig  | Value A
24331  | 12/4/2021 | Doc  | Value B
24328  | 12/4/2021 | Text | Value C
24341  | 12/4/2021 | Doc  | Value B
24341  | 12/4/2021 | Doc  | Value B
24357  | 12/4/2021 | Text | Value C
24357  | 12/4/2021 | Sig  | Value A
24357  | 12/4/2021 | Text | Value C

如有任何帮助，我们将不胜感激。

Answer 1

此选项可能有效-它将值B与所有具有值B的用户ID/Date连接起来，然后将所有没有值a和值B的记录保留在a.步骤和B.步骤列中。

Select
A.UserID,
A.Date,
A.Type,
A.Step
from t as A
LEFT JOIN (
SELECT DISTINCT
UserID,
Date,
Type,
Step
FROM t
WHERE Step = 'Value B') as B
ON B.UserID = A.UserID AND B.Date = A.Date
WHERE NOT (A.Step = 'Value A' AND B.Step IS NOT NULL)

包括我对Stu-小提琴的查询

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=11115c225929e9bd0a46ff3071c00948

Answer 2

您可以从同一个表中执行UNION ALL操作SELECT第一行的值为B，然后userID不存在于用户id中

SELECT 
UserID, 
Date, 
Type, 
Step 
FROM Table WHERE Step = 'Value B'
UNION ALL
SELECT 
UserID, 
Date, 
Type, 
Step 
FROM Table t1 
WHERE Step <> 'Value B' 
AND NOT EXISTS (
SELECT 1 
FROM Table t2 
WHERE t2.Step = 'Value B' AND t2.UserId = t1.UserId)

Answer 3

您可以尝试使用以下查询

with stepB AS(
select * from tableA where userID in( 
select distinct userID from tableA where  step ='Value A'
intersect
select distinct userID from tableA where  step ='Value B')
and step ='Value B')
select * from stepB b
union
select * from tableA  a where not exists(select * from stepB where a.step=b.step)

Answer 4

我们可以使用CTE来确定哪些记录同时具有值a和值B，并且只选择它们的非值a记录，否则提取所有内容：

with cte as 
(
select t.UserID
from TableA t
inner join TableA t2
on t.UserID = t2.UserID
and t.Step = 'Value B'
and t2.Step = 'Value A'
)
select *
from TableA
where (TableA.UserID not in (select UserID from cte))
or (TableA.UserID in (select UserID from cte) and TableA.Step <> 'Value A')

Answer 5

我最初尝试过分析函数，但意识到使用existing:更可靠

with x as (
select * ,
case when exists (select * from t t2 where t.userid=t2.userid and t2.step='Value A') then 1 end +
case when exists (select * from t t2 where t.userid=t2.userid and t2.step='Value B') then 1 end Both
from t
)
select UserId, date, type, Step
from x
where (Both>0 and step='value b') or both is null
order by UserId

演示Fiddle

Answer 6

…小提琴…

create table t(userid int, step varchar(10));
insert into t(userid, step)
values
(24261, 'Value B'),
(24261, 'Value A'),
(24315, 'Value A'),
(24331, 'Value B'),
(24331, 'Value A'),
(24328, 'Value C'),
(24341, 'Value B'),
(24341, 'Value A'),
(24341, 'Value B'),
(24341, 'Value A'),
(24357, 'Value C'),
(24357, 'Value A'),
(24357, 'Value C');
select *
from t as a
where step <> 'value A'
or (step = 'value A' and not exists(select * from t as b where b.userid=a.userid and b.step='value B'))
order by userid,step;
select *
from
(
select *, max(case when step='value B' then 1 else 0 end) over(partition by userid) as userhasB
from t
) as d
where step <> 'value A' or userhasB = 0;

Answer 7

TESTED：演示：dbfiddle

STEP总结：

• 获取一组在"a"中同时具有a和B步骤的不同用户；CTE">
• 取表A主数据集并左联接到此CTE
• 受到两种可能性之一的限制
• CTE用户值为NULL，在这种情况下，他们没有两个A/B步骤
• 只返回CTE中的用户，不包括阶跃值为A的用户

。。布局

With TableA As (
SELECT '24261' USERID,'12/4/2021' DateA,'Doc' Type,'Value B' Step UNION ALL
SELECT '24261','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24315','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24331','12/4/2021','Doc ','Value B' UNION ALL
SELECT '24331','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24328','12/4/2021','Text','Value C' UNION ALL
SELECT '24341','12/4/2021','Doc ','Value B' UNION ALL
SELECT '24341','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24341','12/4/2021','Doc ','Value B' UNION ALL
SELECT '24341','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24357','12/4/2021','Text','Value C' UNION ALL
SELECT '24357','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24357','12/4/2021','text','Value C'),
CTE AS (SELECT USERID, count(*) cnt       
FROM (SELECT distinct userID, Step from TableA WHERE Step in ('Value A', 'Value B')) a
GROUP BY USERID
HAVING  Count(*) > 1)
SELECT A.* 
FROM TableA A
LEFT JOIN CTE 
on A.UserID = CTE.USERID
WHERE (CTE.USERID is not null AND Step <> 'Value A')
OR (CTE.USERID is null)

给我们：

+--------+-----------+------+---------+
| USERID |   DateA   | Type |  Step   |
+--------+-----------+------+---------+
|  24261 | 12/4/2021 | Doc  | Value B |
|  24315 | 12/4/2021 | Sig  | Value A |
|  24331 | 12/4/2021 | Doc  | Value B |
|  24328 | 12/4/2021 | Text | Value C |
|  24341 | 12/4/2021 | Doc  | Value B |
|  24341 | 12/4/2021 | Doc  | Value B |
|  24357 | 12/4/2021 | Text | Value C |
|  24357 | 12/4/2021 | Sig  | Value A |
|  24357 | 12/4/2021 | text | Value C |
+--------+-----------+------+---------+