时间戳

首页 > 解决方案 > PostgreSQL - 每个 id 的最大值

sql - PostgreSQL - 每个 id 的最大值

问题描述

我正在尝试从每个 protege 的表考试中获取exam_id 的最大值。

proteges

 protege_id | protege_patron | protege_firstname | protege_lastname
------------+----------------+-------------------+------------------
          1 |              1 | Andrzej           | Maniek
          2 |              1 | Anna              | Maj
          3 |              1 | Joanna            | Jankowska

exams

 exam_id | exam_protege | exam_weight | exam_glucose | exam_pressure
---------+--------------+-------------+--------------+---------------
       1 |            1 | 84          | 3ml          | 123/84
       2 |            1 | 99          | 23ml         | 124/72
       3 |            2 | 99          | 23ml         | 124/72
       4 |            3 | 94          | 23ml         | 124/72

首先我试过

SELECT DISTINCT protege_patron, exams.* 
FROM exams INNER JOIN proteges ON protege_id = exam_protege 
WHERE exam_id = (SELECT MAX(exam_id) FROM exams WHERE protege_patron = 1);

输出是：

 protege_patron | exam_id | exam_protege | exam_weight | exam_glucose | exam_pressure
----------------+---------+--------------+-------------+--------------+---------------
              1 |       4 |            3 | 94          | 23ml         | 124/72
(1 row)

尝试后SELECT protege_firstname, protege_lastname, MAX (exam_id) FROM exams JOIN proteges ON protege_id = exam_protege GROUP BY protege_id;的输出是：

 protege_firstname | protege_lastname | max
-------------------+------------------+-----
 Andrzej           | Maniek           |   2
 Anna              | Maj              |   3
 Joanna            | Jankowska        |   4
(3 rows)

所以，合乎逻辑的方法是添加更多的东西，比如exam_weight 这就是我所做的：

SELECT protege_firstname, protege_lastname, exam_weight, MAX (exam_id) FROM exams JOIN proteges ON protege_id = exam_protege GROUP BY protege_id;
ERROR:  column "exams.exam_weight" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select protege_firstname, protege_lastname, exam_weight, MAX...
                                                    ^

Atm 我不知道如何解决这个问题。尝试不同，阅读一些关于聚合函数的信息......有什么办法吗？我想要做的就是加入两个表，并为每个门徒选择他的所有价值观和他的考试价值观，最大考试ID ...

标签： sqlpostgresql

解决方案

你可以这样做：

select * from
(
  select max(exam_id) maxexamid, exam_protege from exams group by exam_protege
) as maxexams  
inner join proteges p
    on maxexams.exam_protege = p.protege_id
inner join exams e 
    on e.exam_id = maxexams.maxexamid

推荐阅读