您可以使用聚合和 RANK()
窗口函数来实现。
这个查询:
SELECT idUser
FROM skills
WHERE idSkill IN (9,4,7)
GROUP BY idUser
HAVING SUM(idSkill IN (9,4)) = 2 -- for the 2 mandatory skills
返回至少具有 9 和 4 2 个必备技能的所有用户。
如果您像这样使用带有 ORDER BY
的 LIMIT
子句:
SELECT idUser
FROM skills
WHERE idSkill IN (9,4)) = 2 -- for the 2 mandatory skills
ORDER BY COUNT(*) DESC
LIMIT 1
您将从至少拥有 2 个必选技能 9 和 4 的所有用户处获得,只有 1 个用户:拥有最多技能(必选和可选)的用户。
如果您想返回平局,请使用 RANK()
窗口函数:
SELECT idUser
FROM (
SELECT idUser,RANK() OVER (ORDER BY COUNT(*) DESC) rnk
FROM skills
WHERE idSkill IN (9,7)
GROUP BY idUser
HAVING SUM(idSkill IN (9,4)) = 2 -- for the 2 mandatory skills
) t
WHERE rnk = 1
参见demo。
,
您的回答将是一个简单的查询。
select * from (select distinct b.idUser,(select 1 from Skill a where a.idUser=b.idUser and a.idSkill=4) 'four',(select 1 from Skill a where a.idUser=b .idUser and a.idSkill=9) 'nine',(select 1 from Skill a where a.idUser=b.idUser and a.idSkill=7) 'seven' from Skill b) t where t.four=1 and t .nine=1 和 t.seven=1
本文链接:https://www.f2er.com/15543.html