水平显示中的分组依据和排序依据

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分组)，number of 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