横扫SQL面试——行列（行转列、列转行）转换问题

横扫SQL面试

📌行列转换问题

📊 “面试官让我把一列数据拆成三行，我慌了…”hhhh 别慌！学完这个专题 宝子们绝对没问题！！🤣量大管饱！！！

话不多说——直接上题：🎈🎈🎈🎈🎈🎈🎈

列转行🌟🌟🌟🌟🌟

题目🎯

学生成绩表 student_scores，结构如下：

要求将该表转换为如下格式：

📝分步解析

原始表结构 → 列转行操作
┌──────────────┬───────────────┬────────────────┐
│ math_score   │ english_score │ science_score  │
├──────────────┼───────────────┼────────────────┤
│ 85           │ 90            │ 78             │
└──────────────┴───────────────┴────────────────┘
            ⇩ UNPIVOT
┌──────────────┬───────────────┬───────┐
│ subject      │ score         │ ...   │
├──────────────┼───────────────┼───────┤
│ math         │ 85            │ ...   │
│ english      │ 90            │ ...   │
│ science      │ 78            │ ...   │
└──────────────┴───────────────┴───────┘

步骤1：提取数学成绩

SELECT student_id, 'math' AS subject, math_score AS score
FROM student_scores

中间表 math_scores ✅

步骤2：提取英语成绩

SELECT student_id, 'english' AS subject, english_score AS score
FROM student_scores

中间表 english_scores ✅

步骤3：提取科学成绩

SELECT student_id, 'science' AS subject, science_score AS score
FROM student_scores

中间表 science_scores ✅

步骤4：合并所有结果✅

-- 合并三个中间表
SELECT ... FROM math_scores
UNION ALL
SELECT ... FROM english_scores
UNION ALL
SELECT ... FROM science_scores

最终结果表 ✅

💡 技术要点总结

1. UNION ALL 作用：

   • 合并多个查询结果（不去重）
   • 比 UNION 更高效（无去重开销）

2. 字面量生成分类标签：

   • 'math' AS subject 硬编码科目名称

题目🎯

现有一个水果销售记录表 fruit_sales，表格结构如下：

编写SQL查询，将其转换为以下格式：

步骤1：提取苹果销售额

SELECT 
    store_id,
    'apple' AS fruit,  -- 固定值标记水果类型
    apple_sales AS sales  -- 提取苹果销售额
FROM fruit_sales

中间表 apple_data

步骤2：提取香蕉销售额

SELECT 
    store_id,
    'banana' AS fruit,
    banana_sales AS sales
FROM fruit_sales

中间表 banana_data

步骤3：提取橙子销售额

SELECT 
    store_id,
    'orange' AS fruit,
    orange_sales AS sales
FROM fruit_sales

中间表 orange_data

步骤4：合并所有结果

-- 最终查询
SELECT * FROM apple_data
UNION ALL
SELECT * FROM banana_data
UNION ALL
SELECT * FROM orange_data

最终结果表

完整SQL代码（一步到位） 套路是不是一样~😁

SELECT store_id, 'apple' AS fruit, apple_sales AS sales FROM fruit_sales
UNION ALL
SELECT store_id, 'banana' AS fruit, banana_sales FROM fruit_sales
UNION ALL
SELECT store_id, 'orange' AS fruit, orange_sales FROM fruit_sales
ORDER BY store_id, fruit;  -- 可选排序

💡 技术要点总结

题目🎯

假设有一个员工信息表 employee_info，结构如下：

要求将该表转换为：

同理~😂😂😂 量大管饱

SELECT employee_id, 2023 AS year, salary_2023 AS salary
FROM employee_info
UNION ALL
SELECT employee_id, 2024 AS year, salary_2024 AS salary
FROM employee_info
UNION ALL
SELECT employee_id, 2025 AS year, salary_2025 AS salary
FROM employee_info;

题目🎯：商品库存统计转换

现有一张商品库存信息表 product_stock，记录了不同仓库中各类商品的库存数量，表结构如下：

要求通过SQL查询，将其转换为如下格式：

同理~😂😂😂 量大管饱

SELECT warehouse_id, 'product_a' AS product_name, product_a AS stock_quantity
FROM product_stock
UNION ALL
SELECT warehouse_id, 'product_b' AS product_name, product_b AS stock_quantity
FROM product_stock
UNION ALL
SELECT warehouse_id, 'product_c' AS product_name, product_c AS stock_quantity
FROM product_stock;

题目🎯：季度销售数据转换

假设有一张公司季度销售数据表 quarterly_sales，记录了不同区域在各个季度的销售额，表结构如下：

请编写SQL查询，将其转换为：

同理~😂😂😂 量大管饱

SELECT region, 'q1' AS quarter, q1_sales AS sales_amount
FROM quarterly_sales
UNION ALL
SELECT region, 'q2' AS quarter, q2_sales AS sales_amount
FROM quarterly_sales
UNION ALL
SELECT region, 'q3' AS quarter, q3_sales AS sales_amount
FROM quarterly_sales
UNION ALL
SELECT region, 'q4' AS quarter, q4_sales AS sales_amount
FROM quarterly_sales;

题目🎯：学生课程成绩转换

有一张学生课程成绩表 student_course_scores，记录了不同学生在多门课程的成绩，表结构如下：

要求把该表转换为：

同理~😂😂😂 量大管饱

SELECT student_id,'math' AS course_name, math_score AS score
FROM student_course_scores
UNION ALL
SELECT student_id, 'history' AS course_name, history_score AS score
FROM student_course_scores
UNION ALL
SELECT student_id,'science' AS course_name, science_score AS score
FROM student_course_scores
UNION ALL
SELECT student_id, 'art' AS course_name, art_score AS score
FROM student_course_scores;

带聚合的行列转换🌟🌟🌟

假设有一张订单详情表 order_details，记录了不同订单中各类商品的购买数量和价格，表结构如下：

现在需要统计每个订单中不同商品类型的总购买金额，并将结果进行行列转换，输出格式如下：

SELECT
    order_id,
    SUM(CASE WHEN product_type = 'electronics' THEN quantity * price ELSE 0 END) AS electronics_amount,
    SUM(CASE WHEN product_type = 'clothing' THEN quantity * price ELSE 0 END) AS clothing_amount,
    SUM(CASE WHEN product_type = 'books' THEN quantity * price ELSE 0 END) AS books_amount
FROM
    order_details
GROUP BY
    order_id;

行转列🌟🌟🌟🌟🌟

• group by + 聚合函数 + case when（或者if）

题目🎯：不同部门不同职位的员工薪资总和统计与转换

有一张员工信息表 employee_info，结构如下：

要求先统计每个部门中不同职位的员工薪资总和，再将结果进行行列转换，输出格式如下：

SELECT
    department,
    SUM(CASE WHEN position = 'Manager' THEN salary ELSE 0 END) AS Manager_salary,
    SUM(CASE WHEN position = 'Clerk' THEN salary ELSE 0 END) AS Clerk_salary,
    SUM(CASE WHEN position = 'Engineer' THEN salary ELSE 0 END) AS Engineer_salary,
    SUM(CASE WHEN position = 'Technician' THEN salary ELSE 0 END) AS Technician_salary
FROM
    employee_info
GROUP BY
    department;

题目🎯：不同城市不同季节的商品销售数量统计与转换

有一张商品销售表 product_sales，结构如下：

要求先统计每个城市在不同季节的各类商品销售数量总和，再将结果进行行列转换，输出格式如下：

SELECT
    city,
    SUM(CASE WHEN season = 'Spring' AND product_type = 'Electronics' THEN sales_quantity ELSE 0 END) AS Spring_Electronics,
    SUM(CASE WHEN season = 'Summer' AND product_type = 'Clothing' THEN sales_quantity ELSE 0 END) AS Summer_Clothing,
    SUM(CASE WHEN season = 'Autumn' AND product_type = 'Books' THEN sales_quantity ELSE 0 END) AS Autumn_Books,
    SUM(CASE WHEN season = 'Winter' AND product_type = 'Electronics' THEN sales_quantity ELSE 0 END) AS Winter_Electronics
FROM
    product_sales
GROUP BY
    city;

题目🎯：不同店铺不同时间段的顾客消费金额统计与转换

有一张顾客消费表 customer_consumption，结构如下：

要求先统计每个店铺在不同时间段的顾客消费金额总和，再将结果进行行列转换，输出格式如下：

SELECT
    store_id,
    SUM(CASE WHEN time_period = 'Morning' THEN consumption_amount ELSE 0 END) AS Morning_amount,
    SUM(CASE WHEN time_period = 'Afternoon' THEN consumption_amount ELSE 0 END) AS Afternoon_amount,
    SUM(CASE WHEN time_period = 'Evening' THEN consumption_amount ELSE 0 END) AS Evening_amount
FROM
    customer_consumption
GROUP BY
    store_id;

这些题目都遵循先按一定条件进行聚合统计，再使用 CASE WHEN 结合 SUM 函数进行行列转换的思路~😄

题目🎯：学生成绩行列转换

有一张学生成绩表 student_scores，记录了学生的考试科目和成绩，表结构如下：

要求将每个学生的成绩按照科目进行行列转换，输出格式如下：

SELECT
    student_id,
    SUM(CASE WHEN subject = 'Math' THEN score ELSE 0 END) AS Math_score,
    SUM(CASE WHEN subject = 'English' THEN score ELSE 0 END) AS English_score,
    SUM(CASE WHEN subject = 'Physics' THEN score ELSE 0 END) AS Physics_score,
    SUM(CASE WHEN subject = 'Chemistry' THEN score ELSE 0 END) AS Chemistry_score
FROM
    student_scores
GROUP BY
    student_id;

题目🎯：员工考勤记录行列转换

有一张员工考勤表 attendance_record，记录了员工每天的考勤状态，表结构如下：

要求将员工的考勤记录转换为以日期为列，员工考勤状态为值的格式，输出格式如下：

SELECT
    employee_id,
    MAX(CASE WHEN attendance_date = '2025-01-01' THEN status END) AS '2025-01-01',
    MAX(CASE WHEN attendance_date = '2025-01-02' THEN status END) AS '2025-01-02',
    MAX(CASE WHEN attendance_date = '2025-01-03' THEN status END) AS '2025-01-03'
FROM
    attendance_record
GROUP BY
    employee_id;

题目🎯：订单商品分类统计行列转换

有一张订单商品表 order_products，记录了订单中商品的类别和数量，表结构如下：

要求统计每个订单中不同商品类别的数量，并进行行列转换，输出格式如下：

SELECT
    order_id,
    SUM(CASE WHEN category = 'Food' THEN quantity ELSE 0 END) AS Food_quantity,
    SUM(CASE WHEN category = 'Beverage' THEN quantity ELSE 0 END) AS Beverage_quantity,
    SUM(CASE WHEN category = 'Household' THEN quantity ELSE 0 END) AS Household_quantity
FROM
    order_products
GROUP BY
    order_id;

好了好了 真的吃撑了哈哈哈哈哈🤣🤣🤣

整理不易 后续还会继续更新 希望列位多多支持~🚀🚀🚀