sql - SQL 查询仅获取具有特定类型的记录

Question

我有以下类型的记录

account     amount  code        type        c_date
AAAA        100.00  111         C           2018-01-01
AAAA        200.00  666         C           2018-01-01
AAAA        300.00  777         C           2018-01-01 
BBBB        500.00  111         C           2018-01-01 
BBBB        400.00  222         B           2018-01-01 
BBBB        600.00  111         C           2018-01-01 
DDDD        100.00  111         C           2018-01-01 
DDDD        200.00  777         C           2018-01-01 
EEEE        900.00  333         B           2018-01-01 
EEEE        1000.00 222         B           2018-01-01 
EEEE        1200.00 111         C           2018-01-01
FFFF        123.00  111         C           2018-01-01 
GGGG        1223.00 222         B           2018-01-01 

我只需要检索仅在type = 'C' DESIRED RECORDS 上具有的那些帐户：

AAAA 
DDDD
FFFF

我的查询：

SELECT DISTINCT ACCOUNT,TYPE,COUNT(*) FROM Table_1
WHERE ACCOUNT IN (SELECT account
  FROM Table_1
  group by account
)  
GROUP BY ACCOUNT,TYPE
HAVING TYPE = 'C' 
ORDER BY 1

标签： sqlsql-servertsql

Answer 1

您可以使用NOT EXISTS：

SELECT * -- or DISTINCT Account
FROM t
WHERE NOT EXISTS (
    SELECT 1
    FROM t AS x
    WHERE account = t.account
    AND type <> 'C'
)