在where子句中过滤右连接数据

Question

我有一个简单的表结构，其中每个项目行都有一个状态。我正在寻找的是查询每个状态并计算有多少项目使用该状态。可能并非所有状态都会被使用，因此我需要考虑空值和零计数。

我的表结构如下：

Table: Items
Id, State_id, Text, Status

Table: States
State_id, Name

这就是我所拥有的：

SELECT   statealias1_.State_id as y0_,
         count(this_.Id)       as y1_
FROM     Items this_
         right join States statealias1_
           on this_.State_id = statealias1_.State_id
WHERE    (not (statealias1_.State_id in (5, 6, 7, 8)) 
            or statealias1_.State_id is null)
         and (this_.Status = 'InProgress' 
            or this_.Status is null)
GROUP BY statealias1_.State_id;

该查询在包含所有状态时有效（有 8 个状态，我不包括后半部分）。我不确定为什么这没有返回所有带有计数的状态，无论空值如何，因为我似乎包含空值。

Answer 1

select i.State_id as y0_, count(i.Id) as y1_
from Items i
left join States s on i.State_id = s.State_Id and i.Status = 'InProgress'
group by i.State_id

Answer 2

我会反转它......从您的“状态”条目开始，然后左连接到您想要包含的内容的预聚合结果集

select
      S.State_ID,
      COALESCE( PreAgg.ICount, 0 ) as StateCount
   from
      States S
         LEFT JOIN ( select I.State_ID, count(*) as ICount
                        from Items I
                        where NOT I.State_ID IN( 5, 6, 7, 8 )
                          AND I.Status = 'InProgress'
                        group by I.State_ID ) PreAgg
            on S.State_ID = PreAgg.State_ID

Answer 3

你已经很接近了，但是你的做法与我通常看到的方式相反。

SELECT s.State_id, COUNT(i.Id)
FROM States AS s
LEFT OUTER JOIN Items AS i ON i.State_id = s.State_id
WHERE ...
GROUP BY s.State_id

Answer 4

我认为你的SQL是正确的，但是你的where条件可能有逻辑错误。

您有：

WHERE    (
             NOT (statealias1_.State_id in (5, 6, 7, 8)) 
             OR  statealias1_.State_id is null
)
AND      (this_.Status = 'InProgress' or this_.Status is null)

您的意思是：

WHERE    NOT (
                 statealias1_.State_id in (5, 6, 7, 8) 
                 OR statealias1_.State_id is null
         )
         AND ( this_.Status = 'InProgress' or this_.Status is null )