mysql - 按水平分组和排序

我有一条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

好心提醒。

最佳答案

首先，它看起来像fld01和fld02，对于fld03的所有值都相同，因此实际上，它们应该放在单独的表中。您将表标记为BaseView，因此可能已经存在。请注意，以下示例查询假定上述条件为真（对于fld03的每个值，fld01，fld02和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

关于mysql - 按水平分组和排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24369243/