经常去重复数据，数据需要转等操作，汇总高级SQL MySQL操作

 

一、数据去重（Data Deduplication）

 

去重常用于清除重复记录，保留唯一数据。

 

1. 使用DISTINCT关键字去重单列

 

-- 从用户表中获取唯一的邮箱地址

SELECT DISTINCT email FROM users;

 

 

2. 使用GROUP BY结合聚合函数去重多列

 

-- 按姓名和手机号去重，获取最新注册的用户

SELECT name, phone, MAX(register_time) AS latest_time

FROM users

GROUP BY name, phone;

 

 

3. 使用CTE和ROW_NUMBER()窗口函数标记重复行并删除

 

-- 先标记重复行，再删除非首行记录

WITH DuplicateRows AS (

    SELECT id, name, email,

           ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) AS row_num

    FROM users

)

DELETE FROM DuplicateRows WHERE row_num > 1;

 

 

二、数据行转列（Pivot Table）

 

将行数据转换为列，常用于统计汇总场景。

 

1. 使用CASE WHEN手动实现行转列

 

-- 统计每个用户的不同订单类型数量

SELECT user_id,

       SUM(CASE WHEN order_type = '食品' THEN 1 ELSE 0 END) AS food_orders,

       SUM(CASE WHEN order_type = '服装' THEN 1 ELSE 0 END) AS clothes_orders,

       SUM(CASE WHEN order_type = '数码' THEN 1 ELSE 0 END) AS digital_orders

FROM orders

GROUP BY user_id;

 

 

2. 使用IF函数结合GROUP BY实现动态列

 

-- 按月份统计销售额（假设月份存于month列）

SELECT product_id,

       SUM(IF(month = 1, sales_amount, 0)) AS jan_sales,

       SUM(IF(month = 2, sales_amount, 0)) AS feb_sales,

       SUM(IF(month = 3, sales_amount, 0)) AS mar_sales

FROM sales

GROUP BY product_id;

 

 

3. 使用动态SQL生成行转列语句（适用于列名不确定的场景）

 

-- 动态生成SQL（需先查询列名）

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT 

       CONCAT('SUM(CASE WHEN category = ''', category, ''' THEN amount ELSE 0 END) AS `', category, '`')

) INTO @sql

FROM sales_data;

 

SET @sql = CONCAT('SELECT year, ', @sql, ' FROM sales_data GROUP BY year');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

 

 

三、数据类型转换（Data Type Conversion）

 

在MySQL中转换数据类型，需注意兼容性和精度损失。

 

1. 显式转换：使用CAST()函数

 

-- 将字符串转为日期类型

SELECT CAST('2025-06-20' AS DATE) AS date_value;

 

-- 将数字转为字符串（常用于拼接）

SELECT CONCAT('订单金额：', CAST(amount AS CHAR)) AS order_info

FROM orders;

 

 

2. 显式转换：使用CONVERT()函数

 

-- 将字符串转为DECIMAL类型（保留2位小数）

SELECT CONVERT('123.45', DECIMAL(10,2)) AS price;

 

-- 将日期转为UNIX时间戳

SELECT CONVERT('2025-06-20', UNSIGNED) AS timestamp_value;

 

 

3. 隐式转换（MySQL自动转换，但需注意风险）

 

-- 字符串自动转数字（仅当字符串为纯数字时有效）

SELECT '123' + 456; -- 结果：579

 

-- 日期字符串自动转日期类型（需符合格式）

INSERT INTO dates (date_col) VALUES ('2025-06-20');

 

 

注意事项

 

- 数据去重：使用 DISTINCT 时会对所有列去重，性能低于 GROUP BY ；删除重复行前建议先备份数据。

- 行转列：手动编写CASE WHEN适用于列数固定的场景，动态SQL需注意SQL注入风险。

- 类型转换：隐式转换可能导致意外错误（如 'abc' + 1 会转为 0 + 1 ），建议优先使用显式转换。