时间戳

首页 > 解决方案 > 如何从两个表的 UNION 中清除重复值？

sql - 如何从两个表的 UNION 中清除重复值？

问题描述

我会很感激一点帮助！

真正的代码在所有条件下看起来都更丑陋，所以这应该足够了：

SELECT
Column1 AS FIRST_COL, 
Column2 AS SECOND_COL, 
Column3 AS THIRD_COL, 
Column4 AS FOURTH_COL, 
Phone AS PHONE, 
Column6 AS SIXTH_COl,
FROM Standard_Table
WHERE Column01 = 'xy01'
UNION ALL
SELECT 
FIRST_COL,
SECOND_COL,
THIRD_COL,
FOURTH_COL,
PHONE,
SIXTH_COL,
FROM Special_Table
WHERE COLUMN01 = 'xy'

问题是我想根据他们的电话号码（PHONE 列）摆脱重复的用户。一些用户在两个表中，但他们的记录在表中不一致，因此 DISTINCT 不起作用。

最好的方法是什么？

标签： sqlsoql

解决方案

希望这对你有用

; WITH PartionedData AS (
    SELECT *, 
            ROW_NUMBER() OVER (PARTITION BY PHONE ORDER BY FIRST_COL) RowNum
    FROM (
        SELECT 
        Column1 AS FIRST_COL,
        Column3 AS THIRD_COL, 
        Column4 AS FOURTH_COL, 
        Phone AS PHONE, 
        Column6 AS SIXTH_COl,
        FROM Standard_Table
        WHERE Column01 = 'xy01'
        UNION ALL
        SELECT 
        FIRST_COL,
        SECOND_COL,
        THIRD_COL,
        FOURTH_COL,
        PHONE,
        SIXTH_COL,
        FROM Special_Table
        WHERE COLUMN01 = 'xy' AND 
        PHONE NOT IN (
          SELECT Phone
          FROM Standard_Table
        ) CombinedData
)
SELECT 
FIRST_COL,
THIRD_COL, 
FOURTH_COL, 
PHONE, 
SIXTH_COl,
FROM PartionedData
WHERE RowNum = 1

推荐阅读