如何在SQL中实现列间数据迁移并按周一至周日生成列展示

一、列数据迁移操作 #

要将某一列的数据迁移到另一列，直接使用UPDATE语句即可。假设你的表名为target_table，源列是source_column，目标列是target_column，执行以下语句：

UPDATE target_table
SET target_column = source_column;

如果迁移完成后需要清空源列数据（可选操作），可以追加设置源列为空：

UPDATE target_table
SET target_column = source_column,
    source_column = NULL;

注意：执行UPDATE前建议先通过SELECT source_column, target_column FROM target_table LIMIT 10;验证迁移逻辑，或备份数据避免误操作。

二、展示周一至周日全量数据（指定列名） #

假设表中包含日期列（如record_date）和需要按星期展示的数值列（如record_value），可通过条件聚合将数据按星期维度展开，生成指定名称的列。以下是主流数据库的实现示例：

MySQL / SQL Server 示例 #

SELECT
    MAX(CASE WHEN DAYOFWEEK(record_date) = 2 THEN record_value END) AS Monday,
    MAX(CASE WHEN DAYOFWEEK(record_date) = 3 THEN record_value END) AS Tuesday,
    MAX(CASE WHEN DAYOFWEEK(record_date) = 4 THEN record_value END) AS Wednesday,
    MAX(CASE WHEN DAYOFWEEK(record_date) = 5 THEN record_value END) AS Thursday,
    MAX(CASE WHEN DAYOFWEEK(record_date) = 6 THEN record_value END) AS Friday,
    MAX(CASE WHEN DAYOFWEEK(record_date) = 7 THEN record_value END) AS Saturday,
    MAX(CASE WHEN DAYOFWEEK(record_date) = 1 THEN record_value END) AS Sunday
FROM target_table;

Oracle 示例 #

Oracle使用TO_CHAR函数获取星期标识：

SELECT
    MAX(CASE WHEN TO_CHAR(record_date, 'D') = '2' THEN record_value END) AS Monday,
    MAX(CASE WHEN TO_CHAR(record_date, 'D') = '3' THEN record_value END) AS Tuesday,
    MAX(CASE WHEN TO_CHAR(record_date, 'D') = '4' THEN record_value END) AS Wednesday,
    MAX(CASE WHEN TO_CHAR(record_date, 'D') = '5' THEN record_value END) AS Thursday,
    MAX(CASE WHEN TO_CHAR(record_date, 'D') = '6' THEN record_value END) AS Friday,
    MAX(CASE WHEN TO_CHAR(record_date, 'D') = '7' THEN record_value END) AS Saturday,
    MAX(CASE WHEN TO_CHAR(record_date, 'D') = '1' THEN record_value END) AS Sunday
FROM target_table;

说明：如果需要按周/月分组展示每周的星期数据，只需添加GROUP BY子句（如GROUP BY WEEK(record_date)）即可。

内容的提问来源于stack exchange，提问作者anjali mani