|
我有以下结构:
$ J' R9 P2 c: @) ?4 O# O8 ] `* RCREATE TABLE author id integer ,name varchar(255));CREATE TABLE book id integer ,author_id integer ,title varchar(255) ,rating integer);我希望每个作者都能读到最后一本书:- ]. U: J% n* e6 o( S
SELECT book.id,author.id,author.name,book.title as last_bookFROM authorJOIN book book ON book.author_id = author.idGROUP BY author.idORDER BY book.id ASC显然,你可以在那里mysql执行此操作:在MySQL连接两个表,只从第二个表返回一行。3 u: U3 F8 o8 Y* q6 n5 z" X7 @
但是postgres给出这个错误:
0 B C8 `8 V0 `; F( S错误:“ book.id必须出现列GROUP BY用于子句或聚合函数:SELECT
m. Y( X: j6 A i' R" e" Z" obook.id,author.id,author.name,book.title为last_book FROM author JOIN book
, Y# i* t _0 zbook on book.author_id = author.id GROUP BY author.id ORDER BY book.id ASC
1 H7 T* `' @9 b$ i! M; r. U这是因为:
& D) h* [& \# [0 L$ ~$ {如果存在GROUP BY,则SELECT未分组列(聚合函数除外)不能引用列表表达式,因为未分组列将返回多个可能值。/ i" ]$ q' l, T, G" @8 p
我如何指定postgres:“仅joined_table.id在连接表中按顺序给我最后一行? s' b' V+ g. m0 ~* d* K
编辑:使用此数据:
& L5 q' G% q: F$ `$ i! Y/ y* [5 cINSERT INTO author (id,name) VALUES (1,'Bob(二),David(3),John');INSERT INTO book (id,author_id,title,rating) VALUES 1、1、1、1st book from bob五、nd book from bob六、rd book from bob七、st book from David(、nd book from David',6);我应该看到:) J2 K) t Q/ X- x( ?
book_id author_id name last_book3 3 1 "Bob" "3rd book from bob"5 2 "David" "2nd book from David"
1 d- h. r: `) k3 f( T7 H 解决方案: , a9 H" i1 M* z5 J) | }6 T
select distinct on (author.id) book.id,author.id,author.name,book.title as last_bookfrom author inner join book on book.author_id = author.idorder by author.id,book.id desc查看 distinct on+ R/ ~: q! }+ W' {1 K. ?5 z+ r
SELECT DISTINCT ON(expression [,…])每组行的第一行只保留给定表达式。ORDER& _, G. |) g8 q
BY解释相同的规则DISTINCT ON表达式(请参见上面)。请注意,除非使用。ORDER) p- E, a1 R. D. A; F2 K
BY确保所需的行先出现,否则每一集的第一行都是不可预测的。' X; G5 S) h+ A
内容独特,有必要在其中添加独特列order by。如果不是你想要的顺序,你需要包装查询并重新排序
/ T$ m! y9 s. d& qselect *from select distinct on (author.id) book.id,author.id,author.name,book.title as last_book from author inner join book on book.author_id = author.id order by author.id,book.id desc) authors_with_first_bookorder by authors_with_first_book.name另一种解决方案是使用Lennart答案中的窗口函数。另一个很常见的是
, I/ j. Z9 {7 U/ P& M! b/ `select book.id,author.id,author.name,book.title as last_bookfrom book inner join ( select author.id as author_id,max(book.id) as book_id from author inner join book on author.id = book.author_id group by author.id ) s on s.book_id = book.id inner join author on book.author_id = author.id |
|