postgresql 左加入和分组依据

left join and group by

提问by user1775888

I have table like below two, I want to list location row base on been used in task table, and dont duplicate location.id, also order by column in task table.

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

I'm not sure how to solve it use group by or other method?
(I also check distinct before, but it limit select specific column, seems not what I want..)
how to solve it ?

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

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

need output result

需要输出结果

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

query

询问

I add select t.location_id it works to count, but I still have problem in select rows

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

count works

计数作品

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(::int[])

select

选择

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(::int[]) 
       ORDER BY t.create_date desc NULLS LAST, 
       l.name asc NULLS LAST, l.id desc NULLS LAST OFFSET  LIMIT 

error:

错误：

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

列“t.create_date”必须出现在 GROUP BY 子句中或用于聚合函数 SELECT t.create_date, t.location_id

回答by Gurwinder Singh

You can simply do a left join to get the location_id and corresponding number of rows in task table like this:

你可以简单地做一个左连接来获取 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;

If location_id is unique (probably PK) in your location table and you want to select all or more columns from that table, you can just put them in both the select and group by clause of the query.

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

回答by cars10m

If you want to list the records and columns of table locationonly then you can use exists, like shown below:

如果只想列出表的记录和列，location则可以使用exists，如下所示：

select * from location l where exists (select 1 from task where location_id=l.id)

You can find a demo here.

您可以在此处找到演示。