sql - 按两个列值的计数过滤行

我有下表:

Card(
  MembershipNumber,
  EmbossLine,
  status,
  EmbossName
)

带有样本数据

(0009,0321,'E0','Finn')
(0009,0322,'E1','Finn')
(0004,0356,'E0','Mary')
(0004,0398,'E0','Mary')
(0004,0382,'E1','Mary')

我想检索行，以便仅应显示具有count的MembershipNumber > 1和status='E0' > 1的计数的行。

例如查询应返回以下结果

(0004,0356,'E0','Mary')
(0004,0398,'E0','Mary')

我有使用MembershipNumber count对其进行过滤的查询，但无法弄清楚如何按status ='E0'进行过滤。到目前为止的查询

SELECT *
FROM   (SELECT *,
               Count(MembershipNumber)OVER(partition BY EmbossName) AS cnt
        FROM   card) A
WHERE  cnt > 1

最佳答案

您可以在子查询中添加WHERE status = 'E0':

SQL Fiddle(为Riddle Bull提供 fiddle )

SELECT *
FROM (
    SELECT *,
        COUNT(MembershipNumber) OVER(PARTITION BY EmbossName) AS cnt
    FROM card
    WHERE status = 'E0'
)A
WHERE cnt > 1

关于sql - 按两个列值的计数过滤行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31670135/