sql - 如何编写为特定条件选择不同对值的SQL查询？

我在为以下问题制定查询时遇到麻烦:

对于具有特定分数的对值，您如何以仅返回各自分数最高的不同对值的方式对它们进行分组？

例如，假设我有一个具有以下行值的表:

(t1,p1,65)
(t1,p2,60)
(t1,p3,20)
(t2,p1,60)
(t2,p2,59)
(t2,p3,15)

前两列表示对值，第三列表示对分数。最佳分数是(t1,p1,65)。由于现在使用了t1和p1，因此我想从进一步的分析中排除它们。

下一个最佳分数是(t2,p2,59)。即使(t1,p2)的得分是60，我也想排除它，因为已经使用了“t1”。 (t2,p1)的得分也为60，但由于p1也已被使用，因此该对不包括在内。

这导致以下两个不同的对分数值:

(t1,p1,65)
(t2,p2,59)

有什么方法可以仅通过查询生成此结果？我尝试过考虑对结果进行分组和划分的方法，但是由于必须已经根据分数等级对值进行了一些核算，因此我发现这很难实现。

编辑:

生成数据:

with t(t, p, score) as (
    (values ('t1','p1',65),
           ('t1','p2',60),
           ('t1','p3',20),
           ('t2','p1',60),
           ('t2','p2',59),
           ('t2','p3',15)
     ))
select t.* from t;

最佳答案

使用存储的功能相对简单:

--drop function if exists f();
--drop table if exists t;
create table t(x text,y text, z int);
insert into t values
  ('t1','p1',65),
  ('t1','p2',60),
  ('t1','p3',20),
  ('t2','p1',60),
  ('t2','p2',59),
  ('t2','p3',15)/*,
  ('t3','p1',20),
  ('t3','p2',60),
  ('t3','p3',40)*/;

create function f() returns setof t immutable language plpgsql as $$
declare
  ax text[];
  ay text[];
  r t;
begin
  ax := '{}'; ay := '{}';
  loop
    select * into r
      from t
      where x <> all(ax) and y <> all(ay)
      order by z desc, x, y limit 1;
    exit when not found;
    ax := ax || r.x; ay := ay || r.y;
    return next r;
  end loop;
end $$;

select * from f();
╔════╤════╤════╗
║ x  │ y  │ z  ║
╠════╪════╪════╣
║ t1 │ p1 │ 65 ║
║ t2 │ p2 │ 59 ║
╚════╧════╧════╝

但是，如果取消注释第三组值，结果将有所不同:

╔════╤════╤════╗
║ x  │ y  │ z  ║
╠════╪════╪════╣
║ t1 │ p1 │ 65 ║
║ t3 │ p2 │ 60 ║
║ t2 │ p3 │ 15 ║
╚════╧════╧════╝

Upd:以及对相同的测试数据使用递归CTE的等效项:

with recursive r as (
  (select x, y, z, array[x] as ax, array[y] as ay from t order by z desc, x, y limit 1)
  union all
  (select t.x, t.y, t.z, r.ax || t.x, r.ay || t.y from t, r
  where not (t.x = any(r.ax) or t.y = any(r.ay))
  order by t.z desc, t.x, t.y limit 1))
select * from r;

关于sql - 如何编写为特定条件选择不同对值的SQL查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40365185/