1.行转列

1.1有id

group by + sum/max/min(case when...)

本质类似js中：

departments.reduce(total, department => total + department.revenue)

SELECT 
    product_id,
    SUM(CASE store WHEN 'store1' THEN price ELSE NULL END) AS 'store1',
    SUM(CASE store WHEN 'store2' THEN price ELSE NULL END) AS 'store2', 
    SUM(CASE store WHEN 'store3' THEN price ELSE NULL END) AS 'store3' 
FROM 
    products 
GROUP BY 
    product_id 

1.2构造id

先创建id，用 row_number() over (PARTITION BY ... ORDER BY ...) rk

SELECT
    MAX(CASE WHEN continent = 'America' THEN NAME ELSE NULL END) America,
    MAX(CASE WHEN continent = 'Asia' THEN NAME ELSE NULL END) Asia,
    MAX(CASE WHEN continent = 'Europe' THEN NAME ELSE NULL END) Europe
FROM
    (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY continent ORDER BY NAME) rk
        FROM
            student 
    ) t
GROUP BY rk

2.列转行

自增长的数列用with递归

# Write your MySQL query statement below
WITH RECURSIVE t(transactions_count) AS
(
    SELECT 0
    UNION ALL
    SELECT
        transactions_count + 1
    FROM
        t
    WHERE 
        transactions_count < (
            SELECT 
                MAX(count_num) max_num
            FROM
                (
                    SELECT
                        COUNT(*) OVER (PARTITION BY transaction_date, user_id) count_num
                    FROM
                        transactions
                ) t
        )
)

SELECT
    t.transactions_count,
    IFNULL(visits_count, 0) visits_count
FROM
    t
LEFT JOIN
    (
        SELECT
            t_num transactions_count,
            COUNT(*) visits_count
        FROM
            (
                SELECT
                    user_id,
                    visit_date,
                    COUNT(user_id2) t_num
                FROM
                    (
                        SELECT 
                            v.user_id,
                            v.visit_date,
                            t.user_id user_id2
                        FROM
                            visits v
                        LEFT JOIN
                            transactions t
                        ON
                            visit_date = t.transaction_date AND v.user_id = t.user_id
                    ) t1
                GROUP BY user_id, visit_date
            ) t2
        GROUP BY t_num
    ) t4
ON
    t.transactions_count = t4.transactions_count
GROUP BY
    t.transactions_count

未出现的字符串union all

# Write your MySQL query statement below

SELECT
    t2.spend_date,
    t2.platform,
    IFNULL(SUM(amount), 0) AS total_amount,
    IFNULL(COUNT(DISTINCT user_id), 0) AS total_users
FROM
    (
        SELECT DISTINCT spend_date, "desktop" AS platform FROM Spending
        UNION
        SELECT DISTINCT spend_date, "mobile" AS platform FROM Spending
        UNION
        SELECT DISTINCT spend_date, "both" AS platform FROM Spending
    ) t2
LEFT JOIN
    (
        SELECT
            spend_date,
            user_id,
            SUM(amount) AS amount,
            IF(COUNT(*) = 1, platform, 'both') AS platform
        FROM
            Spending
        GROUP BY
            spend_date, user_id
    ) t1
ON
    t2.spend_date = t1.spend_date AND t2.platform = t1.platform
GROUP BY
    t2.spend_date, t2.platform