sql-server - 仅选择 “customer”的所有行具有相同状态的地方吗？

我有一个带有位字段closed的状态表

我想尝试选择所有CustomerNumber，其中该客户的所有行均为closed
以下工作...但是我敢肯定有一种更明智的方法!

SELECT * FROM
    (SELECT
          lcs.CustomerNumber AS CustomerNumber
        , COUNT(lcs.CustomerNumber) AS Total
    FROM Status lcs
    GROUP BY lcs.CustomerNumber) total
LEFT JOIN
    (SELECT
        lcs.CustomerNumber AS CustomerNumber
        , COUNT(lcs.CustomerNumber) AS Closed
    FROM Status lcs
    WHERE lcs.Closed = 1
    GROUP BY lcs.CustomerNumber) closed
ON closed.CustomerNumber = total.CustomerNumber
WHERE closed.Closed = total.Total

每个客户可以有一个或多个行，每行要么关闭= 0，要么关闭= 1

仅在关闭客户的所有行时才需要选择。

样品:

CustomeNumber     Closed
111               0
111               0
112               1
112               0
113               1
113               1
114               1

这应该选择:
113和114
两者的所有状态均为“已关闭”。

最佳答案

SELECT  customerNumber
FROM    TableName
GROUP   BY customerNumber
HAVING  COUNT(*) = SUM(CASE WHEN closed = 1 THEN 1 END)

SQLFiddle Demo

输出值

╔════════════════╗
║ CUSTOMERNUMBER ║
╠════════════════╣
║            113 ║
║            114 ║
╚════════════════╝