mysql - 水平呈现中的分组依据和排序依据

Question

我有一个SQL语句如下:

SELECT
    ID,
    fld01, 
    fld02,
    fld03 AS AGENT,
    fld04 AS DIGIT,
    COUNT(*) AS fld05 AS OCCURRENCE
FROM table AS BaseView
GROUP BY fld03
ORDER BY fld03, fld05  DESC, fld04

我的返回结果总是10行，从0到9(对于fld03分组的返回结果)，其中count(*)的数量作为返回结果出现的次数。

因此，我的返回结果应该始终如下:

fld01 | fld02 | fld03 (AGENT) | fld04 (DIGIT) | fld05 (OCCURRENCE)

XX    | YY    | AGENT1        | 0             | 16
XX    | YY    | AGENT1        | 4             | 15
XX    | YY    | AGENT1        | 6             | 12
XX    | YY    | AGENT1        | 7             | 10
XX    | YY    | AGENT1        | 1             | 9
XX    | YY    | AGENT1        | 9             | 9
XX    | YY    | AGENT1        | 3             | 8
XX    | YY    | AGENT1        | 5             | 7
XX    | YY    | AGENT1        | 2             | 4
XX    | YY    | AGENT1        | 8             | 2

我的问题是:

我希望返回的结果以水平方式显示(在 DESC 模式下按计数顺序排列)。

预期返回结果如下:

fld01 | fld02 | fld03  | D01 | D02 | D03 | D04 | D05 | D06 | D07 | D08 | D09 | D10

XX    | YY    | AGENT1 | 0   | 4   | 6   | 7   | 1   | 9   | 3   | 5   | 2   | 8 

请多多指教。

Answer 1

首先，fld01 和 fld02 看起来对于 fld03 的所有值都是相同的，因此实际上它们应该位于单独的表中。您将该表标记为 BaseView，因此情况可能已经如此。请注意，以下示例查询假设上述情况成立(fld01、fld02 和 fld03 对于 fld03 的每个值始终相同)

此外，这感觉很糟糕 - 这实际上是一项输出工作，而不是这个阶段的数据选择。我不会在我的系统中这样做。

SELECT
    -- base values
    baseView.ID,
    baseView.fld01,
    baseView.fld02,
    baseView.fld03,

    -- counts from the various tables
    COALESCE(COUNT(d01.fld03),0) AS d01,
    COALESCE(COUNT(d02.fld03),0) AS d02,
    COALESCE(COUNT(d03.fld03),0) AS d03,
    COALESCE(COUNT(d04.fld03),0) AS d04,
    COALESCE(COUNT(d05.fld03),0) AS d05,
    COALESCE(COUNT(d06.fld03),0) AS d06,
    COALESCE(COUNT(d07.fld03),0) AS d07,
    COALESCE(COUNT(d08.fld03),0) AS d08,
    COALESCE(COUNT(d09.fld03),0) AS d09,
    COALESCE(COUNT(d10.fld03),0) AS d10

FROM table AS baseView
LEFT JOIN table AS d01
    ON d01.fld03 = baseView.fld03
    AND d01.fld04 = 1
LEFT JOIN table AS d02
    ON d02.fld03 = baseView.fld03
    AND d02.fld04 = 2
LEFT JOIN table AS d03
    ON d03.fld03 = baseView.fld03
    AND d03.fld04 = 3
LEFT JOIN table AS d04
    ON d04.fld03 = baseView.fld03
    AND d04.fld04 = 4
LEFT JOIN table AS d05
    ON d05.fld03 = baseView.fld03
    AND d05.fld04 = 5
LEFT JOIN table AS d06
    ON d06.fld03 = baseView.fld03
    AND d06.fld04 = 6
LEFT JOIN table AS d07
    ON d07.fld03 = baseView.fld03
    AND d07.fld04 = 7
LEFT JOIN table AS d08
    ON d08.fld03 = baseView.fld03
    AND d08.fld04 = 8
LEFT JOIN table AS d09
    ON d09.fld03 = baseView.fld03
    AND d09.fld04 = 9
LEFT JOIN table AS d10
    ON d10.fld03 = baseView.fld03
    AND d10.fld04 = 0

GROUP BY baseView.fld03
ORDER BY fld03