sql - 左加入和分组依据

Question

我有如下两个表格，我想列出任务表中已使用的位置行，并且不要重复 location.id，也按任务表中的列排序。

我不确定如何使用 group by 或其他方法解决它？
(我之前也检查过distinct，但是它限制了选择特定的列，似乎不是我想要的..)
如何解决？

location
id | status | ...
1  | ...
2  | ...

id SERIAL NOT NULL

task
id | location_id | create_date | start_date | ...
1  | 1           | ...
2  | 1           | ...
3  | 2           | ...

id SERIAL NOT NULL
location_id integer DEFAULT NULL
create_date timestamp without time zone NOT NULL  
start_date date NOT NULL

需要输出结果

location  order by task.create_date
id | ...
1  | ...
2  | ...

查询

我添加了 select t.location_id 它可以计数，但我在选择行时仍然有问题

计数作品

SELECT count(l.*)
   AS total_row_count 
   FROM location l
         LEFT JOIN (
           SELECT t.location_id
           FROM task t
           GROUP BY t.location_id
           ) t ON t.location_id  = l.id
        WHERE l.status = ANY($1::int[])

选择

SELECT
  l.* 
  FROM location l 
        LEFT JOIN (
          SELECT t.location_id, t.create_date
          FROM task t
          GROUP BY t.location_id
          ) t ON t.location_id  = l.id
       WHERE l.status = ANY($1::int[]) 
       ORDER BY t.create_date desc NULLS LAST, 
       l.name asc NULLS LAST, l.id desc NULLS LAST OFFSET $2 LIMIT $3

错误:

column "t.create_date" must appear in the GROUP BY clause or be used in an aggregate function SELECT t.create_date, t.location_id

Answer 1

你可以简单地做一个左连接来获取任务表中的 location_id 和相应的行数，如下所示:

select l.id, count(t.location_id) times
from location l
left join task t
on l.id = t.location_id
group by l.id
order by max(t.create_date) desc;

如果 location_id 在您的位置表中是唯一的(可能是 PK)并且您想从该表中选择所有或更多列，您可以将它们放在查询的 select 和 group by 子句中。