在GROUP_CONCAT中使用MAX

Question

我有一个与Invalid use of group function in MySQL相关的问题。

我正在尝试执行的查询是这样的(我知道这是一个非常大的查询，但是如果我能够执行这个查询，那么我将调整和修复性能和易于阅读的内容)：

SELECT
    `id_c` AS `sceneId`,
    `scene`.`id` AS `sceneName`,
    `scene`.`frameCount` AS `frames`,
    task.id_mv AS `id_mv`,
    CONCAT( '[',GROUP_CONCAT(
        CONCAT( 
            '{',
                '"idTk": ', `task`.`id_tk`, ', ',
                -- '"tktype": ', `task`.`id_tk_type`, ', ',
                -- '"subDepType": ', IF ( `task`.`id_sub_dep_type` = 54, `task`.`id_sub_dep_type`, 'null'), ', ',
                -- '"color": "', `task`.`color`, '", ',
                '"weight": ', MAX(`task`.`weight`), ', ',
                '"dept": ', '"',
                    CASE `task`.`id_tk_type`
                        WHEN 43 THEN 'MVB'
                        WHEN 12 THEN 'LYT'
                        WHEN 32 THEN
                            CONCAT_WS(
                                ', ',
                                IF( `task`.`id_sub_dep_type` != 54, 'ANI', NULL ),
                                IF( `task`.`id_sub_dep_type` = 54, 'CKD', NULL )
                            )
                        WHEN 190 THEN 'DYN'
                        WHEN 104 THEN 'FIN'
                        WHEN 191 THEN 'LGT'
                    END, '"',
                -- task.id_tk_type, ', ',
                -- task.id_sub_dep_type,            
            '}'
        )
        ORDER BY
            FIELD( `task`.`id_tk_type`, 43, 12, 32, 190, 104, 191 ),
            `task`.`weight` DESC
        SEPARATOR
            ', '
    ), ']') AS `tkInfo`
FROM
        `V_task` AS `task`
        JOIN `tk_fct_mcsc` AS `mcsc` ON `task`.`id_tk` = `mcsc`.`id_tk`
        JOIN `scene` ON `mcsc`.`id_c` = `scene`.`id_scene`
WHERE
    `task`.`id_sub_dep_type` IN (
        SELECT
            `id_sub_dep_type`
        FROM
            `tk_dim_sub_dep_type`
        WHERE `id_tk_type` IN (
            SELECT
                `id_tk_type`
            FROM
                `tk_dim_dep_type`
            WHERE
                `id_tk_type` IN ( 43, 12, 32, 190, 104, 191 )
        )
              AND `id_sub_dep_type` != 54
        UNION
        SELECT
            `id_sub_dep_type`
        FROM
            `tk_dim_sub_dep_type`
        WHERE
            `id_sub_dep_type` = 54
    )
    AND `task`.`is_appr` = FALSE
    AND `mcsc`.`type` = 'sc'
    AND `scene`.`id` != '000'
    -- AND `mcsc`.`id_b` = 530
GROUP BY
    `scene`.`id`,
    `scene`.`id_scene`
    -- task.weight
ORDER BY
    `scene`.`id`,
    `task`.`weight` DESC;

错误，我知道是MAX( task.weight )内部的GROUP_CONCAT函数，但我不知道如何避免这件事。

我其实是在试着做一些让你测试这些东西的东西，但是我想我需要的只是另一双眼睛，让我知道去哪里看。

谢谢!

编辑# 1就像kickstart问的那样，我想得到的是这样的东西：

sceneId sceneName   frames  id_mv   tkInfo
200     001         1200    1       "[{"dept": "LGT"}, {"dept": "LGT"}]"
1342    001         45      14      "[{"dept": "LYT"}, {"dept": "LGT"}, {"dept": "LGT"}]"
1335    001         460     14      "[{"dept": "MVB"}, {"dept": "LYT"}, {"dept": "LGT"}, {"dept": "LGT"}]"
1351    001         20      11      "[{"dept": "LYT"}, {"dept": "ANI"}, {"dept": "FIN"}, {"dept": "LGT"}]"
1375    001         63      11      "[{"dept": "LYT"}, {"dept": "CKD"}, {"dept": "ANI"}, {"dept": "DYN"}, {"dept": "FIN"}, {"dept": "FIN"}, {"dept": "FIN"}, {"dept": "LGT"}]"
1382    001         66      11      "[{"dept": "LYT"}, {"dept": "CKD"}, {"dept": "ANI"}, {"dept": "FIN"}]"

但是，没有重复的“部门”，并这样做，我需要最大的重量为该部门。

编辑# 2我用一个小欺骗解决了我的这个问题。

首先，我简化了大量的主查询，删除了case-thing，并为每个sceneId插入了一个最大值(权重)。

使用这个简化的查询，我构建了一个View，并使用该视图应用了case/group_concat .所有的结果都很好。

..sqlfiddle将在这里被插入。

Answer 1

有一点发挥，(非常)未经测试，但认为您需要有效地进行两次查询。一次得到细节，一次得到最大重量。你可以移动你的一些子选择(做一个连接-联盟将处理重复的加入之前)。

就像这样：-

SELECT
    Sub1.id_c AS sceneId,
    Sub1.id AS sceneName,
    Sub1.frameCount AS frames,
    Sub1.id_mv AS id_mv,
    CONCAT( '[',GROUP_CONCAT(
        CONCAT( 
            '{',
                '"idTk": ', Sub1.id_tk, ', ',
                '"weight": ', Sub2.MaxTaskWeight, ', ',
                '"dept": ', '"', Sub1.id_tk_type_decode, '"',
            '}'
        )
        ORDER BY
            FIELD( Sub1.id_tk_type, 43, 12, 32, 190, 104, 191 ),
            Sub2.MaxTaskWeight DESC
        SEPARATOR
            ', '
    ), ']') AS tkInfo
FROM
(
    SELECT
        id_c AS sceneId,
        scene.id AS sceneName,
        scene.frameCount AS frames,
        task.id_mv AS id_mv,
        task.id_tk, 
        CASE task.id_tk_type
            WHEN 43 THEN 'MVB'
            WHEN 12 THEN 'LYT'
            WHEN 32 THEN IF( task.id_sub_dep_type = 54, 'CKD', 'ANI' )
            WHEN 190 THEN 'DYN'
            WHEN 104 THEN 'FIN'
            WHEN 191 THEN 'LGT'
        END AS id_tk_type_decode,
        task.id_tk_type
    FROM    V_task AS task
    INNER JOIN  tk_fct_mcsc AS mcsc ON task.id_tk = mcsc.id_tk
    INNER JOIN  scene ON mcsc.id_c = scene.id_scene
    INNER JOIN 
    (   
        SELECT id_sub_dep_type
        FROM tk_dim_sub_dep_type 
        INNER JOIN tk_dim_dep_type
        ON tk_dim_sub_dep_type.id_tk_type = tk_dim_dep_type.id_tk_type
        WHERE  tk_dim_dep_type.id_tk_type  IN ( 43, 12, 32, 190, 104, 191 )
        AND tk_dim_sub_dep_type.id_sub_dep_type != 54
        UNION
        SELECT id_sub_dep_type
        FROM tk_dim_sub_dep_type 
        WHERE id_sub_dep_type = 54
    ) Sub1
    ON task.id_sub_dep_type = Sub1.id_sub_dep_type
    WHERE task.is_appr = FALSE
    AND mcsc.`type` = 'sc'
    AND scene.`id` != '000'
)
INNER JOIN
(
    SELECT scene.id, scene.id_scene, MAX(task.weight) AS MaxTaskWeight
    FROM    V_task AS task
    INNER JOIN  tk_fct_mcsc AS mcsc ON task.id_tk = mcsc.id_tk
    INNER JOIN  scene ON mcsc.id_c = scene.id_scene
    INNER JOIN 
    (   
        SELECT id_sub_dep_type
        FROM tk_dim_sub_dep_type 
        INNER JOIN tk_dim_dep_type
        ON tk_dim_sub_dep_type.id_tk_type = tk_dim_dep_type.id_tk_type
        WHERE  tk_dim_dep_type.id_tk_type  IN ( 43, 12, 32, 190, 104, 191 )
        AND tk_dim_sub_dep_type.id_sub_dep_type != 54
        UNION
        SELECT id_sub_dep_type
        FROM tk_dim_sub_dep_type 
        WHERE id_sub_dep_type = 54
    ) Sub1
    ON task.id_sub_dep_type = Sub1.id_sub_dep_type
    WHERE task.is_appr = FALSE
    AND mcsc.`type` = 'sc'
    AND scene.`id` != '000'
    GROUP BY scene.id, scene.id_scene 
) Sub2
ON Sub1.id = Sub2.id
AND Sub1.id_scene = Sub2.id_scene
GROUP BY
    Sub1.id,
    Sub1.id_scene
ORDER BY
    Sub1.id,
    MaxTaskWeight DESC;