groupconcat—mysql中的count和groupconcat

nwwlzxa7  于 2021-06-23  发布在  Mysql
关注(0)|答案(1)|浏览(359)

我有三张table pcn_type , in_e_s_s__p_c_ns , p_c_n_details . 我正在尝试使用组concat将三个不同的值合并为一个值。
我的问题:

SELECT 'browser' AS NAME, CONCAT( '[', CONCAT('{"', pcn_type.name, '",', 
COUNT(JPN_ID), '}'), ']' ) AS DATA FROM p_c_n_details INNER JOIN 
in_e_s_s__p_c_ns RIGHT OUTER JOIN pcn_type ON pcn_type.name = 
p_c_n_details.type AND in_e_s_s__p_c_ns.pcnid= 
p_c_n_details.JPN_ID GROUP BY pcn_type.name

得到的结果:

NAME    |    DATA
-------------------------------------
browser      [{"Design Change",4}]
browser      [{"EOL",10}]
browser      [{"Process Change",21}]

期望结果:

NAME    |    DATA
--------------------------------------------------------------------
browser      [{"Design Change",4},{"EOL",10},{"Process Change",21}]

如何重新构造上述查询以获得预期结果。

dhxwm5r4

dhxwm5r41#

使用 GROUP_CONCAT 功能

select name,GROUP_CONCAT(DATA  SEPARATOR ' ') 
from 
(          
    SELECT 'browser' AS NAME, CONCAT( '[', CONCAT('{"', pcn_type.name, '",', 
    COUNT(JPN_ID), '}'), ']' ) AS DATA FROM p_c_n_details INNER JOIN 
    in_e_s_s__p_c_ns RIGHT OUTER JOIN pcn_type ON pcn_type.name = 
    p_c_n_details.type AND in_e_s_s__p_c_ns.pcnid= 
    p_c_n_details.JPN_ID GROUP BY pcn_type.name
) as T group by name

相关问题