带外键的子表计数，多次，带条件

Question

我有两个表，

1. 标签->id、名称、描述、用户、状态
2. 标签连接。->id，Label_id，类别

所以有多个类别，比如说1=>新，2=>旧。

我需要根据类别对子表进行计数。

这就是我现在所拥有的，

SELECT `L`.*, COUNT(DISTINCT LC1.id) as count1, COUNT(DISTINCT LC2.id) as count2 
FROM (`Labels` L) 
LEFT JOIN `Label_connection` LC1 ON `LC1`.`Label_id` = `L`.`id` AND LC1.categories = "1" 
LEFT JOIN `Label_connection` LC2 ON `LC2`.`Label_id` = `L`.`id` AND LC2.categories = "2" 
WHERE `L`.`status` = '0' AND `L`.`user` = 1
GROUP BY `L`.`id` 
ORDER BY `L`.`id` DESC 
LIMIT 20

这确实让我得到了正确的计数，但我担心多个联接，因为类别的数量会增加。

如果有人能改进这一点，我将不胜感激，

提前谢谢。

Answer 1

对于条件聚合，您也可以使用COUNT DISTINCT .. CASE..WHEN..END。

此外，您当前的查询不是有效的GROUP BY(除非L.id是主键(。有关详细信息，请阅读：在MySql 中执行查询时发生的与only_full_group_by相关的错误

此外，请更喜欢在字符串文字周围使用SQL标准单引号，而不是双引号。

SELECT L.id,  
COUNT(DISTINCT CASE WHEN LC.categories = '1' THEN LC.id END) AS count1, 
COUNT(DISTINCT CASE WHEN LC.categories = '2' THEN LC.id END) AS count2 
FROM Labels AS L
LEFT JOIN Label_connection AS LC 
ON LC.Label_id = L.id
WHERE L.status = '0' AND
L.user = 1
GROUP BY L.id 
ORDER BY L.id DESC 
LIMIT 20

Answer 2

您可以尝试将其写成一个具有条件聚合的JOIN：

SELECT `L`.*
, SUM(CASE WHEN LC.categories = "1" THEN 1 END) as count1
, SUM(CASE WHEN LC.categories = "2" THEN 1 END) as count2
FROM `Labels` L
LEFT JOIN `Label_connection` LC ON `LC`.`Label_id` = `L`.`id`
WHERE `L`.`status` = '0' AND `L`.`user` = 1
GROUP BY `L`.`id` 
ORDER BY `L`.`id` DESC 
LIMIT 20