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

问题描述

使用以下查询，我得到了表中有多少位玩家的结果，但现在我还希望哪些玩家在同一张表中，请显示该玩家ID并以逗号分隔.

Using following query i gettinog the result like how much players in the table but now i also want which players are in same table ,show that players id with comma seprator .

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 that the comment on NULL handling in GROUP_CONCAT. If you want to show zeros when no players are present, you might want to use COALESCE(GROUP_CONCAT(gtp.id), 0) instead.

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