我知道使用 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