SQL选择无重复项，并有条件地选择要保留的正确重复项

Question

我有一个带有产品列表的只读表，我需要避免根据序列号("序列号"(选择重复项。当我有一个副本时，我想选择"标签"第一个字母在a和J之间的副本。

以下是我的数据和我尝试获得的无重复选择：

CREATE TABLE products(id INT, serial VARCHAR(25), label VARCHAR(50) , type VARCHAR(25));
INSERT INTO products(id, serial, label, type)
VALUES
( 1, '111', 'A1', 'computer'),
( 2, '222', 'B2', 'computer'),
( 3, '333', 'Z3', 'computer'),
( 4, '333', 'D4', 'computer'),
( 5, '555', 'E5', 'computer'),
( 6, '666', 'X6', 'computer'),
( 7, '777', 'G7', 'computer'),
( 8, '777', 'Y7', 'computer'),
( 9, '888', 'I8', 'computer'),
(10, '999', 'J9', 'screen'),
(11, '777', 'G7bis', 'computer'),
(12, '666', 'X6bis', 'computer');

SELECT COUNT(serial) OVER(PARTITION BY serial) as nbserial, *
FROM products
where type='computer' and nbserial=1 or
(nbserial>1 and LEFT(label, 1) between 'A' and 'J')
;

我有几个问题：这里我不能在where子句中定义关于nbserial的条件。如果有3个重复，我需要选择一行来验证条件：标签的第一个字母在a和J之间。如果有多个重复项，但没有一个验证条件(A和J之间的第一个字母(，则选择任意一行。

预期结果示例：(没有序列重复，如果可能的话，标签以A和J之间的字母开头(

( 1, '111', 'A1', 'computer'),
( 2, '222', 'B2', 'computer'),
( 4, '333', 'D4', 'computer'),
( 5, '555', 'E5', 'computer'),
( 6, '666', 'X6', 'computer'),
( 7, '777', 'G7', 'computer'),
( 9, '888', 'I8', 'computer'),
(10, '999', 'J9', 'screen'),

如何使用SELECT执行此操作，并且不能更改表内容？

感谢

Answer 1

您可以使用row_number()和条件排序：

select *
from (
select p.*,
row_number() over(
partition by serial
order by case when left(label, 1) between 'A' and 'J' then 0 else 1 end, id
) rn
from products p
) p
where rn = 1

或者更好的是，在Postgres:中使用distinct on

select distinct on (serial) p.*
from products p
order by serial, (left(label, 1) between 'A' and 'J') desc, id

这给每个serial一行，并优先考虑第一个字母在"0"one_answers"0"之间的标签；A"；以及"；J"；。当存在平局时，保留具有最少id的行。

DB Fiddle上的演示：

id|serial|label|type-：|：-----|：----|：-------1|111|A1|计算机2|222|B2|计算机4|333|D4|计算机5|555|E5|计算机6|666|X6|计算机7|777|G7|计算机9 | 888 | I8 |计算机10|999|J9|屏幕