选择所有行值作为列表

发布于 2025-01-22 02:55:28 字数 858 浏览 2 评论 0原文

我有一个表格的表：

userId          caption    status       id
1               Paul       done         1
2               Ali        notDone      18
3               Kevin      notDone      12
3               Elisa      notDone      13

我将其加入另一个表用户以查找task的数量其中status = Notdone。我这样做：

    SELECT u.id, 
       t.number_of_tasks,
    FROM users u
    INNER JOIN (
      SELECT userId, COUNT(*) number_of_tasks
      FROM tasks 
      WHERE status = "notDone"
      GROUP BY userId
      ) t ON u.id = t.userId
    """

现在，我想创建另一列字幕以某种方式包含Count> Count> Count和满足连接条件 +的条件。

例如，我希望这是行之一。我该如何实现？

userId      number_of_tasks     captions
3           2                   ["Kevin", "Elisa"]

原文

I have a table tasks that looks like this:

userId          caption    status       id
1               Paul       done         1
2               Ali        notDone      18
3               Kevin      notDone      12
3               Elisa      notDone      13

I join it with another table users to find the number of taskswhere status = notDone. I do it like this:

    SELECT u.id, 
       t.number_of_tasks,
    FROM users u
    INNER JOIN (
      SELECT userId, COUNT(*) number_of_tasks
      FROM tasks 
      WHERE status = "notDone"
      GROUP BY userId
      ) t ON u.id = t.userId
    """

Now, I want create another column captions that somehow includes a list of all captions that were included in the countand fulfil the join + where conditions.

For example, I would expect this as one of the rows. How can I achieve this?

userId      number_of_tasks     captions
3           2                   ["Kevin", "Elisa"]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

天邊彩虹 2025-01-29 02:55:28

您可以使用 JSON_GROUP_ARRAY（）为每个用户创建字幕列表：

SELECT u.id, t.number_of_tasks, t.captions
FROM users u 
INNER JOIN (
  SELECT userId, 
         COUNT(*) number_of_tasks,
         json_group_array(caption) captions
  FROM tasks 
  WHERE status = 'notDone'
  GROUP BY userId
) t ON u.id = t.userId;

You can use json_group_array() aggregate function inside the subquery to create the list of captions for each user:

SELECT u.id, t.number_of_tasks, t.captions
FROM users u 
INNER JOIN (
  SELECT userId, 
         COUNT(*) number_of_tasks,
         json_group_array(caption) captions
  FROM tasks 
  WHERE status = 'notDone'
  GROUP BY userId
) t ON u.id = t.userId;

回复收藏 0 原文

~没有更多了~