使用HAVING条件进行分组

Question

我有一个包含以下字段的表格。

我希望能够获得像“状态”一样的输出，仅在Account_Id组中的Invalid_vod内。

Account_Id  Address_Id  Status
AC001       ADD12345    Invalid_vod
AC003       ADD12348    Invalid_vod
AC003       ADD12349    Invalid_vod

我之前尝试了这样做，但无法获得预期的结果。

select [Account_Id],[Address_Id],[Status]
  from [DBFile]
  group by [Account_Id],[Address_Id],[Status]
  having [Status] = 'Invalid_vod'

- Avijit

Answer 1

您可以使用窗口版本的COUNT来实现此功能：

;with cte as (
   select [Account_Id], [Address_Id], [Status],
          count(case when [Status] <> 'Invalid_vod' then 1 end)
          over (partition by [Account_Id]) AS cnt
   from [DBFile]
)
select [Account_Id], [Address_Id], [Status]
from cte
where cnt = 0

- Giorgos Betsos

Answer 2

你可以像这样查询。

;with cte as (
select Accountid,  count(distinct status) as Distcnt from DBFile
group by accountid
)
select * from DBFile where accountid in (select accountid from cte where Distcnt = 1)
and status = 'Invalid_vod'

- Kannan Kandasamy

Answer 3

WITH CandidateAccounts AS (
     SELECT Account_Id
       FROM DBFile
      WHERE Status = 'Invalid_vod'
     EXCEPT 
     SELECT Account_Id
       FROM DBFile
      WHERE Status <> 'Invalid_vod' )
SELECT Account_Id, Address_Id, Status
  FROM DBFile
 WHERE Status = 'Invalid_vod'  -- not enough data to be
                               -- sure about this
       AND Account_Id IN ( SELECT Account_Id
                             FROM CandidateAccounts );

- onedaywhen