gpt4 book ai didi

mysql - 多次计算带有外键的子表,有条件

转载 作者:行者123 更新时间:2023-11-29 05:51:28 30 4
gpt4 key购买 nike

我有两个表,

  1. 标签 -> id,name,description,user,status
  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

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

如果有人可以改进这一点,我将不胜感激,

提前致谢。

最佳答案

对于条件聚合,您还可以使用COUNT DISTINCT .. CASE..WHEN..END

此外,您当前的查询 GROUP BY 无效(除非 L.id 是主键)。详情请阅读:Error related to only_full_group_by when executing a query in MySql

此外,请更喜欢在字符串文字周围使用 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

关于mysql - 多次计算带有外键的子表,有条件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53568712/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com