mysql - 根据 mysql 列和组结果增加一个或另一个值

Question

我有 table1(id, name), table2(id [FK to table1.id],user_id,visit)。对于每个 table1.id，我需要显示 table1 中的名称，计算用户数量并显示属于该 id 的 user_id，当 table2.visit=1 时，计算用户数量并显示属于该 id 的 user_id，当 table2.visit= 时0。另外，table2 中可能不存在 table1 中的某些 id，对于这些 id，我希望计数器中的值为 0。

结果表应如下所示:

id|name|count_visitors|list_of_visitors_id|count_non_visitors|list_of_non_visitors_id|
1 |a   |2             |5,10               |1                 |4                      |

您能帮忙解答一下吗？

Answer 1

SELECT t1.name, 
       COALESCE( visitors.visitors_count, 0) AS `count_visitors`,
       COALESCE( visitors.visitors_list, '') AS `list_of_visitors_id`, 
       COALESCE( nonvisitors.nonvisitors_count, 0 ) AS `count_non_visitors`,
       COALESCE( nonvisitors.nonvisitors_list, '') AS `list_of_non_visitors_id`
FROM table1 t1
LEFT JOIN
( SELECT id, GROUP_CONCAT( user_id ) AS visitors_list, COUNT(*) AS visitors_count
    FROM table2
    WHERE visit = 1
    GROUP BY id ) visitors
ON t1.id = visitors.id
LEFT JOIN
  ( SELECT id, GROUP_CONCAT( user_id ) AS nonvisitors_list, COUNT(*) AS nonvisitors_count
    FROM table2
    WHERE visit = 0
    GROUP BY id ) nonvisitors
ON t1.id = nonvisitors.id

自己尝试一下:Sqlfiddle

如果您需要为一个用户提供它，只需在末尾添加一行:

WHERE t1.id = 1