mysql UNION duplicate values

本文关键字：values duplicate UNION mysql | 更新日期: 2023-09-27

我知道使用 UNION 会删除 sql 中的重复行，但我想知道如何删除与另一行共享重复主键的重复行。这是我的查询

"
SELECT 
    isbn, title, rank, 
    lowest_new_price                AS lowest_price,
    lowest_new_location             AS lowest_location,
    FROM ".TBL_BOOKS."          
UNION
SELECT 
    isbn, title, rank, 
    lowest_used_price               AS lowest_price,
    lowest_used_location            AS lowest_location,
    FROM ".TBL_BOOKS." 

ORDER BY rank ASC"

输入数据

isbn,    title,    rank,    lowest_new_price,    lowest_new_location
00001    t1        1        100                  uk
00001    t1        1        200                  uk
00002    t3        5        50                   uk

所需的输出数据

isbn,    title,    rank,    lowest_new_price,    lowest_new_location
00001    t1        1        100                  uk
00002    t3        5        50                   uk

或

isbn,    title,    rank,    lowest_new_price,    lowest_new_location
00001    t1        1        200                  uk
00002    t3        5        50                   uk

我认为您需要两个查询，一个用于将此数据放入临时表中，另一个用于获取所需的数据。如果需要，您可以在单个查询中执行此操作，但会有点混乱。

首先，我们需要 ISBN 的模糊数据，根据您的查询但没有排序子句 - 然后其次，我们需要从中获取正确的不同数据。

像这样：

select data.isbn, data.title, data.rank, min(data.lowest_price) as [Lowest Price], b.lowest_Location
from <your_query> data inner join <your_query> b on data.isbn = b.isbn and data.lowest_price = b.lowest_price
order by data.rank

下面怎么样

SELECT 
    isbn, title, rank, 
    lowest_new_price                AS lowest_price,
    lowest_new_location             AS lowest_location,
    FROM ".TBL_BOOKS."
    WHERE lowest_new_price <= lowest_used_price
UNION
SELECT 
    isbn, title, rank, 
    lowest_used_price               AS lowest_price,
    lowest_used_location            AS lowest_location,
    FROM ".TBL_BOOKS." 
    WHERE lowest_new_price > lowest_used_price