postgresql 在 Postresql 中使用 group by 子句和多个内部联接选择查询

Select query with group by clause and multiple inner joins in Postresql

提问by Sudar

I have 3 tabels with the following definitions

我有 3 个具有以下定义的表格

people
------
- wid 
- name

types
-----
- guid
- type

mapping
-------
- guid
- wid

The people table has the list of people

人员表有人员列表

The types table the type information for each row present in the people table. If a person belongs to more than one type, then two rows are present in the types table.

类型表显示了人员表中每一行的类型信息。如果一个人属于多个类型，则类型表中将出现两行。

The mapping table provides a mapping between people and types table.

映射表提供了人员和类型表之间的映射。

Now to find out who are the people of type 'politician' I can use the following query.

现在要找出谁是“家”类型的人，我可以使用以下查询。

select name from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid where types.type = 'politician'

But now I want to find out what are the other types a politician belongs. I know that I have to use group byand havingclause. But I am not able to come up with the query. How to write this query?

但现在我想找出家属于的其他类型是什么。我知道我必须使用group by和having条款。但我无法提出查询。这个查询怎么写？

采纳答案by il_guru

Try:

尝试：

select p.name, t2.type
from types t1
join mapping m1 on m1.guid = t1.guid
join people p on p.wpid = m1.wpid 
join mapping m2 on p.wpid = m2.wpid 
join types t2 on m2.guid = t2.guid
where t1.type = 'politician'
order by 1, 2

- for a list of all politicans and all the types that they belong to.

- 获取所有家及其所属的所有类型的列表。

Alternatively, if you just want a list of all politicians and the numberof different types that they belong to, try:

或者，如果您只想列出所有家及其所属的不同类型的数量，请尝试：

select p.name, count(*)
from mapping m1
join people p on p.wpid = m1.wpid 
join mapping m2 on p.wpid = m2.wpid 
where m1.guid = 1 /* replace 1 with appropriate guid for politicians */
group by p.name
order by 1

回答by il_guru

A group by has to be used to give the result of an aggregate function on a group of value (like receiving the count of different types, or a sum of values). If you just need to get what group of types a people belongs too, you can use a single query like this.

必须使用 group by 来给出一组值的聚合函数的结果（例如接收不同类型的计数，或值的总和）。如果您只需要获得一个人属于哪一组类型，您可以使用这样的单个查询。

select name, types
from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid
where people.wpid in (select people.wpid from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid where types.type = 'politician')

A group by would be useful to know how many groups a politician is into

一个 group by 将有助于知道一个家有多少个组

select name, count(types)
from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid
where people.wpid in (select people.wpid from people inner join 
(mapping inner join types on mapping.guid = types.guid) 
on people.wpid = mapping.wpid where types.type = 'politician')
group by name

EDIT: avoid IN subquery

编辑：避免 IN 子查询

If you know the guid of the politiciangroup, you could do something like this. I did not test the query, but the idea is to filter the peopletable using a join with the mappingtable with guid equal to the politician guid

如果你知道团体的指导，你可以做这样的事情。我没有测试查询，但我的想法是使用与 guid 等于家 guid的映射表的连接来过滤people表

select p.name, count(t.types)
from people p inner join mapping m1
on p.wid = m1.wid and m1.guid = [politician guid]
inner join mapping m2
on p.wid = m2.wid
inner join types t
in m2.guid = t.guid