php - 选择仅具有一个值的优先级数组

Question

我有视频表:

如何获取对请求类别定义较好的记录并忽略相似。

SELECT [...] WHERE category IN(1,2,3,4);

预期结果:

Answer 1

关键是找到具有最小基数的匹配类别:

with given_data(cat) as (
    select * from unnest (array[1,2,3,4])
    )
select distinct on(cat) *
from urls
cross join given_data
where cat = any(category)
order by cat, cardinality(category);

 id |   url    | category  | cat 
----+----------+-----------+-----
  1 | example1 | {1,2,3,4} |   1
  2 | example2 | {2}       |   2
  3 | example3 | {3}       |   3
  4 | example4 | {4}       |   4
(4 rows)