gpt4 book ai didi

MySQL 从组合的多个字段中获取 COUNT

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

假设我有一张 table :

user_id    parent_id    lev1    lev2   lev3    lev4
1 0 0 0 0 0
2 1 1 0 0 0
3 1 1 0 0 0
4 2 2 1 0 0
5 4 4 2 1 0
6 4 4 2 1 0
7 5 5 4 2 1

基本上,这是为了跟踪父子层次结构,我想知道 parent 有多少个 child 。下面是我想要的输出:

parent_id     children
1 5
2 4
3 0
4 3
5 1
6 0
7 0

我想计算组合的 lev1、lev2、lev3 和 lev4 字段,以计算这些字段中总共有多少个 ID。

我阅读了有关 UNION ALL 的信息,但我似乎无法弄清楚它是如何运作的。我在考虑一个带有 self JOIN 的 UNION ALL?

最佳答案

您需要针对每个 levN 列的子查询进行 LEFT JOIN,这会返回不同的级别,并对该列进行计数。然后将它们全部加起来并加入 user_id

SELECT
DISTINCT
user_id,
/* COALESCE() is needed so NULLs don't ruin the calculation */
COALESCE(l1count, 0) +
COALESCE(l2count, 0) +
COALESCE(l3count, 0) +
COALESCE(l4count, 0) AS children
FROM
yourtable
/* a left join individually against each of the `levN` columns to get the count per value of each */
LEFT JOIN (SELECT lev1, COUNT(*) AS l1count FROM yourtable GROUP BY lev1) l1 ON yourtable.user_id = l1.lev1
LEFT JOIN (SELECT lev2, COUNT(*) AS l2count FROM yourtable GROUP BY lev2) l2 ON yourtable.user_id = l2.lev2
LEFT JOIN (SELECT lev3, COUNT(*) AS l3count FROM yourtable GROUP BY lev3) l3 ON yourtable.user_id = l3.lev3
LEFT JOIN (SELECT lev4, COUNT(*) AS l4count FROM yourtable GROUP BY lev4) l4 ON yourtable.user_id = l4.lev4

http://sqlfiddle.com/#!2/214a8/16

关于MySQL 从组合的多个字段中获取 COUNT,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14730920/

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