sql - 选择 ValueA 其中 [count(ValueB) > 2 并且计数的行共享 ValueC]

Question

我有一张这样的桌子：

+-------------+-----------------+-----------+
| AggregateId | TransactionDate | EventName |
+-------------+-----------------+-----------+
| 123abc      | 2021-08-21      | EventA    |
| 123abc      | 2021-08-21      | EventA    |
| 123abc      | 2021-08-21      | EventB    |
| 123abc      | 2021-09-15      | EventA    |
| 456xyz      | 2021-08-21      | EventA    |
| 456xyz      | 2021-08-21      | EventA    |
| 456xyz      | 2021-08-21      | EventC    |
| 456xyz      | 2021-08-30      | EventA    |
| 789dfg      | 2021-08-30      | EventA    |
| ....        | ...             | ...       |
+-------------+-----------------+-----------+

我想获取在同一日期具有多个 EventA 的所有 AggregateId 的列表，例如第 1,2 和 5,6 行。

预期输出：

+-------------+
| AggregateId |
+-------------+
| 123abc      |
| 456xyz      |
+-------------+

我尝试过这样的事情，但它根本不起作用。

SELECT 
t.AggregateId
from Table1 t1 inner join Table1 t2 on t1.CreditId = t2.CreditId
where  t1.TransactionDate = t2.TransactionDate
group by t1.creditid
having count(case t.EventName when 'EventA' then 1 else null end) > 1

标签： sqlsql-server

Answer 1

尝试这个

SELECT DISTINCT t.AggregateId, 
from Table1
where t.EventName = 'EventA'
group by t.AggregateId, t.TransactionDate
having count(*) > 1