mysql - 如何在mysql查询中重用结果集

Question

执行“select * from temp”后我得到了这样的结果集

id | Month | value1 | value2 |
------------------------------
1  | Apr   | 100    | 150
2  | May   | 50     | 75
3  | Jan   | 50     | 75
5  | Feb   | 50     | 75
6  | mar   | 50     | 75

我想将它更改为列到行，如数据透视表。

我用 case when 语句来改变它。

select
    'value1' as Field,

    SUM(CASE 
        WHEN dc.month = 'January' THEN dc.value1 
        ELSE  ''
    END)    AS January,

    SUM(CASE 
        WHEN dc.month = 'February' THEN dc.value1 
        ELSE  ''
    END)    AS February,

    SUM(CASE 
        WHEN dc.month = 'March' THEN dc.value1 
        ELSE  ''
    END)    AS March
      SUM(CASE 
        WHEN dc.month = 'April' THEN dc.value1 
        ELSE  ''
    END)    AS April,

    SUM(CASE 
        WHEN dc.month = 'May' THEN dc.value1 
        ELSE  ''
    END)    AS May
      from
      (select 
         * from 
      temp)dc

Field   | Jan | Feb | Mar | Apr | May
-------------------------------------
Value1  | 50  | 50  | 50  | 100  | 50

我想在不使用 UNION 的情况下在同一查询中为 value2 获得相同的结果集。因为临时表有很多数据。

Field   | Jan | Feb | Mar | Apr | May
-------------------------------------
Value1  | 50  | 50  | 50  | 100  | 50
Value1  | 75  | 75  | 75  | 150  | 75

Answer 1

此解决方案使用联合，但仅创建“假”行来表示每个值*列，然后按这些行进行连接和分组:

SELECT
    `field`,

    SUM(CASE 
        WHEN dc.month = 'Jan' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS January,

    SUM(CASE 
        WHEN dc.month = 'Feb' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS February,

    SUM(CASE 
        WHEN dc.month = 'Mar' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS March,
      SUM(CASE 
        WHEN dc.month = 'Apr' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS April,

    SUM(CASE 
        WHEN dc.month = 'May' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS May
      FROM
      (SELECT 
         * FROM temp
      )dc
      INNER JOIN (
       SELECT 'value1' AS `field`
            UNION SELECT 'value2'
      ) AS value_columns
    GROUP BY `field`