包括COUNT() =0的结果

Question

若要选择带有计数的表字段，请执行以下操作。那部分起作用了。我还需要包含计数为0的结果。

这就是我试过的

SELECT mallname, COUNT( * ) AS Count
FROM `malls`
INNER JOIN Mal_Client_Relationship ON Mal_Client_Relationship.m_Id = malls.m_Id
INNER JOIN clients ON Mal_Client_Relationship.id_user = clients.id_user
INNER JOIN dates ON dates.date_id = Mal_Client_Relationship.date_id
WHERE confirmcode = 'y' && EndDate >= CURRENT_DATE( ) || HAVING count(*) = 0
GROUP BY `mallname`
ORDER BY `mallname` 

和

SELECT mallname, COUNT( * ) AS Count
FROM `malls`
INNER JOIN Mal_Client_Relationship ON Mal_Client_Relationship.m_Id = malls.m_Id
INNER JOIN clients ON Mal_Client_Relationship.id_user = clients.id_user
INNER JOIN dates ON dates.date_id = Mal_Client_Relationship.date_id
WHERE confirmcode = 'y' && EndDate >= CURRENT_DATE( ) || count(*) = 0
GROUP BY `mallname`
ORDER BY `mallname` 

和

SELECT mallname, COUNT( * ) AS Count
FROM `malls`
INNER JOIN Mal_Client_Relationship ON Mal_Client_Relationship.m_Id = malls.m_Id
INNER JOIN clients ON Mal_Client_Relationship.id_user = clients.id_user
INNER JOIN dates ON dates.date_id = Mal_Client_Relationship.date_id
WHERE confirmcode = 'y' && EndDate >= CURRENT_DATE( ) || count(mallname) = 0
GROUP BY `mallname`
ORDER BY `mallname` 

来阐明它背后的逻辑。当计数达到60 (60个客户端)时，我需要禁用mallname。我在php中这样做，这是可行的。但是我需要包含带0计数的mallname (这意味着购物中心还没有客户链接到它。

希望一切都有意义..。

Answer 1

您所面临的问题是，某些组(商城名称)在有零计数的情况下会被删除。这个问题的一个解决方案是首先选择所有的购物中心名称，然后根据每个购物中心名称分组得到的结果LEFT JOIN这个结果。

SELECT DISTINCT m.mallname, COALESCE(t.Count, 0)
FROM `malls` m LEFT JOIN
(
    SELECT mallname, COUNT( * ) AS Count
    FROM `malls`
    INNER JOIN Mal_Client_Relationship ON Mal_Client_Relationship.m_Id = malls.m_Id
    INNER JOIN clients ON Mal_Client_Relationship.id_user = clients.id_user
    INNER JOIN dates ON dates.date_id = Mal_Client_Relationship.date_id
    WHERE confirmcode = 'y' && EndDate >= CURRENT_DATE( ) || HAVING count(*) = 0
    GROUP BY `mallname`
    ORDER BY `mallname`
) t
ON m.mallname = t.mallname