如何使用逗号分隔符作为单行值获取计数值 [英] How to get the counted values with a comma separator as a single row value

问题描述

使用以下查询，我得到的结果类似于表中的玩家人数，但现在我还希望哪些玩家位于同一表中，并用逗号分隔符显示这些玩家的ID.

Using the following query I getting the result like how much the players in the table, but now I also want which players are in the same table, and show those players id with a comma separator.

select gt.id,
       bcc.min_coins,
       bcc.max_coins,
       (
          SELECT count(gtp.id)
            FROM game_table_players gtp
                 LEFT JOIN game_rounds gr
                 ON gr.id = gtp.game_round_id
           WHERE gt.id = gtp.game_table_id
             AND gtp.quit<>1 AND gr.finish=0
       ) AS no_of_players,
       IFNULL
       (
           (
               SELECT game_started
                 FROM game_rounds gr
                WHERE gr.game_table_id = gt.id
                  AND gr.finish=0
           ),0
        ) AS game_started
from game_tables gt
     LEFT JOIN pickdeck_category pc
          ON pc.id = gt.pickdeck_category_id
     LEFT JOIN bet_coin_category bcc
          ON bcc.id = gt.bet_coin_category_id
where gt.pickdeck_category_id =1
ORDER BY created_date
LIMIT 0,20

---

---

推荐答案

您要查找的功能是GROUP_CONCAT.请参阅文档:

The function you are looking for is GROUP_CONCAT. See the documentation:

http://dev .mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

在您的示例中，您可以再创建一个子查询(以下示例).或JOIN以标准方式将其查询到原始查询.

In your example you can just create one more subquery (example below). Or JOIN it in a standard way to the original query.

...
(
      SELECT GROUP_CONCAT(gtp.id)
          FROM game_table_players gtp
          LEFT JOIN game_rounds gr ON gr.id = gtp.game_round_id
      WHERE gt.id = gtp.game_table_id
          AND gtp.quit<>1 AND gr.finish=0
) AS players,
...

请注意有关在GROUP_CONCAT中处理NULL的注释.如果要在没有任何玩家在场时显示零，则可以改用COALESCE(GROUP_CONCAT(gtp.id), 0).

Note the comment on NULL handling in GROUP_CONCAT. If you want to show zeros when there aren't any players present, you might want to use COALESCE(GROUP_CONCAT(gtp.id), 0) instead.

这篇关于如何使用逗号分隔符作为单行值获取计数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！