我有3个实体,类型、项目、所有者
所有者
id name
1 owner1
2 owner2
3 owner3
一个所有者可以拥有多个项目
Item
id name owner_id
1 item1 1
2 item2 1
3 item3 1
4 item4 3
5 item5 2
Type
id name
1 alpha
2 beta
Item和Type之间存在多对多关系。一个项目可以有多个类型,一个类型可以有多条项目
Item_Type
id item_id type_id
1 1 1
2 1 2
3 3 1
4 4 1
根据名称给定一个类型,我需要获得所有拥有该类型项目的所有者,并按每个类型的项目数量排序。
我使用的是PostgreSQL 10。
所以我从开始
SELECT Type.name, Item_Type.item_id from Type WHERE Type.name = 'my_var' INNER JOIN Item_Type ON Type.id = Item_Type.type_id
因此,我需要计算每个类型的项目,并在获得按计数排序的所有者DISTINCT之后。
我不知道如何计数处理这个订单。
预期-示例:
因此,如果为类型I选择name
:"阿尔法",它对应于id 1
与Type
和id 1
对应的item_id
是:1,3,4
id为1,3
的项对应于owner_id
为1(计数=2)。id为4
、对应于owner_id
的Item为3(计数=1)。
所以结果,(按顺序)将是owner1, owner3
您可以在下面尝试-
演示
select d.name as ownername,count(distinct c.name) as itemcount from
Item_Type a
inner join Types b on a.type_id=b.id
inner join Item c on a.item_id=c.id
inner join Owner d on c.owner_id=d.id
group by d.name
此查询将获取您想要的结果。它查找每个所有者拥有的所有项目,然后按项目类型对其进行筛选,使其仅包括alpha
类型的项目,然后对所有者拥有的项目数量进行COUNT
,并按该值降序对结果进行排序:
SELECT t.name, o.name, COUNT(i.id) AS num_type
FROM Owner o
JOIN Item i ON i.owner_id = o.id
JOIN Item_Type it ON it.item_id = i.id
JOIN Type t ON t.id = it.type_id
WHERE t.name = 'alpha'
GROUP BY t.name, o.name
ORDER BY num_type DESC
输出:
name name num_type
alpha owner1 2
alpha owner3 1
dbfiddle 演示