sql - 带有 GROUP_CONCAT 和 CONCAT 的子查询返回 STILL 多于 1 行，错误 1242

Question

我得到这个错误：

“错误 1242 超过 1 行”

即使我已经使用 group_concat ..

这是我的查询，它给了我错误

SELECT 
`coms_participant_exam_event`.`coms_exam_event_id` AS PEE_ID,
(SELECT GROUP_CONCAT((SELECT CONCAT(`bpmspace_coms_v1`.`state`.`name`," (",`coms_participant_exam_event`.`state_id`,") ",  count(`coms_participant_exam_event`.`state_id`))  
FROM `coms_participant_exam_event`
JOIN `bpmspace_coms_v1`.`state` ON
    (
            `bpmspace_coms_v1`.`state`.`state_id` =
            `bpmspace_coms_v1`.`coms_participant_exam_event`.`state_id`
    )
where `coms_exam_event_id` = `coms_participant_exam_event`.`coms_exam_event_id`
group by `bpmspace_coms_v1`.`coms_participant_exam_event`.`state_id`) , ','))
FROM `coms_participant_exam_event`
group by `coms_participant_exam_event`.`coms_exam_event_id`

当我“硬编码”coms_exam_event_id带有 id 的只有 1 行时它正在工作 - 不知道为什么 GROUP_CONCAT 不够

SELECT 
`coms_participant_exam_event`.`coms_exam_event_id` AS PEE_ID,
(SELECT GROUP_CONCAT((SELECT CONCAT(`bpmspace_coms_v1`.`state`.`name`," (",`coms_participant_exam_event`.`state_id`,") ",  count(`coms_participant_exam_event`.`state_id`))  
FROM `coms_participant_exam_event`
JOIN `bpmspace_coms_v1`.`state` ON
    (
            `bpmspace_coms_v1`.`state`.`state_id` =
            `bpmspace_coms_v1`.`coms_participant_exam_event`.`state_id`
    )
where `coms_exam_event_id` = 877523
group by `bpmspace_coms_v1`.`coms_participant_exam_event`.`state_id`),','))
FROM `coms_participant_exam_event`
group by `coms_participant_exam_event`.`coms_exam_event_id`

感谢您的帮助抢劫

标签： sqlmariadbsubquerygroup-concat

Answer 1

看起来 group_concat 中不允许计数。我用“select concat ... count ... part”创建了一个帮助视图，然后从视图中做了 group_concat ...