SQL不同的多列 [英] SQL distinct multiple columns

问题描述

我有一张桌子，如下:

book_id   author_id     mark     year
   1          1          BAD     2014
   1          1         MEDIUM   2014
   1          1         GREAT    2015

我想执行一个查询，该查询将为我提供每位作者最好的书. 像这样:

I would like to execute a query that will give me the best book for each author. Something like this:

book_id   author_id     mark     year
   1          1         GREAT    2015

我尝试在多个字段上使用distinct关键字-但是当我这样做时:

I tried to use the distinct keyword on multiple fields - but when I do this:

select distinct book_id, author_id from Books 

我只得到book_id和author_id(与预期的一样)-但我也需要标记和年份-但我无法将其添加到不同的短语中.

I get only the book_id and the author_id (as expected) - but I also need the mark and the year - but I cannot add it to the distinct phrase.

当前我正在使用Postgres 9.4，但是我需要ANSI-SQL解决方案.

Currently I'm using Postgres 9.4 but I need an ANSI-SQL solution.

有办法吗?

推荐答案

最棒的n-per-group 问题通常使用窗口函数来解决:

greatest-n-per-group questions are usually solved using window functions:

select *
from (
   select book_id, author_id, mark, year, 
          row_number() over (partition by author_id order by case mark when 'GREAT' then 1 when 'MEDIUM' then 2 else 3 end) as rn
   from books
) t
where rn = 1;

以上是标准的ANSI SQL，但在Postgres中使用(专有)distinct on通常会更快:

The above is standard ANSI SQL, but in Postgres using the (proprietary) distinct on is usually much faster:

select distinct on (author_id) book_id, author_id, mark, year, 
from books
order by author_id, 
         case mark when 'GREAT' then 1 when 'MEDIUM' then 2 else 3 end

这篇关于SQL不同的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！