MySQL - 对记录分组执行检查

Question

我有这样的结果集:

"id","reference_id","type"
"1","aaa","A"
"2","aab","M"
"3","aac","A"
"4","aaa","M"
"5","aaa","E"
"6","aab","M"
"7","aac","M"
"8","aab","D"
"9","aac","M"

我想使用 sql 对其进行一些验证。

• 每个reference_id生命周期只能包含一个“A”；
• 每个 reference_id 生命周期必须包含至少 1 个“E”和/或 1 个“D”；

我如何遍历结果集并对每组记录 (reference_id) 执行检查。

Answer 1

(更新)尝试:

select r.* 
from my_result_set r
join (select reference_id
      from my_result_set
      group by reference_id
      having sum(case type when 'A' then 1 end) = 1 and
             sum(case when type in ('D','E') then 1 end) >= 1) s
  on r.reference_id = s.reference_id

SQLFiddle here .