SQL 筛选器唯一结果

Question

>我正在尝试获取一个SQL语句，该语句将输出唯一的零件号，例如没有重复项 但是，我希望类型为"已购买"是默认值，如果没有"已购买"部件，它会变回"制造"。 注意所有零件都可以购买

我要求的结果是只显示唯一的零件号 例如 1 到 10，已购买是默认类型

表1

Part_number | Type         | Cost
Part 1 |    Manufactured   | £1.00
Part 1 |    Purchased      | £0.56
Part 2 |    Manufactured   | £1.26
Part 2 |    Purchased      | £0.94
Part 3 |    Manufactured   | £0.36
Part 3 |    Purchased      | £0.16
Part 4 |    Manufactured   | £1.00 
Part 4 |    Purchased      | £1.50
Part 5 |    Manufactured   | £1.65
Part 6 |    Manufactured   | £1.98 
Part 7 |    Manufactured   | £0.15
Part 8 |    Manufactured   | £0.45
Part 9 |    Manufactured   | £1.20
Part 9 |    Purchased      | £0.80
Part 10|    Manufactured   | £1.00

这是我希望得到的结果

Part_number | Type         | Cost
Part 1 |    Purchased      | £0.56
Part 2 |    Purchased      | £0.94
Part 3 |    Purchased      | £0.16
Part 4 |    Purchased      | £1.50
Part 5 |    Manufactured   | £1.65
Part 6 |    Manufactured   | £1.98 
Part 7 |    Manufactured   | £0.15
Part 8 |    Manufactured   | £0.45
Part 9 |    Purchased      | £0.80
Part 10|    Manufactured   | £1.00

我已经尝试了很多不同的技术，但没有得到结果。

我猜我需要创建经过过滤的临时表，然后将这些表连接在一起，但我真的不知道。

任何帮助都将得到批准

Answer 1

您也可以通过对它们进行排序来抓取每个组中的第一行。当有其他数据列要返回时，这将使它更容易。

with data as (
select *, row_number() over (
partition by part_number
order by case when t.type = Purchased then 1 else 2 end) as rn
from t
)
select * from data where rn = 1;

如果有其他类型，这也可以工作，尽管如果每个零件有两个以上，您需要对其进行调整。

Answer 2

一种方法使用not exists. 假设每个部件最多有两行：

select t.*
from t
where t.type = 'Purchased' or
(t.type = 'Manufactured' and
not (exists (select 1 from t t2 where t2.part_number = t.part_number and t2.type = 'Purchased')
)
);

还有其他有趣的方法可以解决这个问题。 例如，聚合方法：

select part_number,
max(type) as type,
coalesce(max(case when type = 'Purchased' then cost end),,
max(cost)
) as cost
from t
group by part_number;