我有一个表,表示提供、配置文件和技能之间的三元关系。
任何给定的报价都可以有多个配置文件和多个相关技能。
像这样:
三元关系表
id_Offer - id_Profile - id_Skill
1 - 1 - 1
1 - 1 - 2
1 - 1 - 3
1 - 2 - 1
2 - 1 - 1
2 - 1 - 2
2 - 1 - 3
2 - 2 - 1
报价表
Offer - business_name
1 - business-1
1 - business-1
1 - business-1
1 - business-1
2 - business-2
2 - business-2
2 - business-2
2 - business-2
我想做一个查询过滤的个人资料,只有计数提供一次,无论有多少技能相关联。
我做了如下查询:
SELECT business_name, COUNT(*)
FROM Offer INNER JOIN
ternary_relationship
ON Offer.id_Offer = ternary_relationship.id_Offer AND
id_Profile = '1'
GROUP BY business_name
ORDER BY COUNT(*) DESC;
我已经看到了几种可行的解决方案,但我不能让任何人为我的案子工作。当我同时按名称和id\u offer进行分组时,或者只按id\u offer进行筛选时,都不起作用。我总是在某处得到重复的条目。
1条答案
按热度按时间ukqbszuj1#
你可以做这个
JOINS
一次使用DISTINCT
```select o.business_name, count(distinct tr.id_Offer) counts
from Offer o inner join
ternary_relationship tr on
o.Offer = tr.id_Offer
where tr.id_Profile = ?
group by o.business_name
order by 2 desc;