mysql - 在 GROUP_CONCAT 中使用 MAX

Question

我遇到了与 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;

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

我实际上正在尝试构建某种 sqlfiddle 的东西来让你测试这些东西，但我认为我需要的只是另一双眼睛，让我知道在哪里看。

谢谢!

编辑#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 插入了 MAX(权重)。

通过这个简化的查询，我构建了一个 View ，并通过该 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;