这个SQL查询可以被简化吗?
我有以下表格:
人,{ “ID”, “姓名”, “姓氏”}
体育,{ “ID”, “姓名”, “类型”}
SportsPerPerson,{ “ID”, “PersonId”,“SportsId”}这个SQL查询可以被简化吗?
对于我的查询,我希望获得所有擅长某个特定体育项目的人员,而我只有体育“名称”属性可供我处理。要检索我已经想通了以下查询正确的行:
SELECT *
FROM Person
WHERE Person.Id in
(
SELECT SportsPerPerson.PersonId FROM SportsPerPerson
INNER JOIN Sports on SportsPerPerson.SportsId = Sports.Id
WHERE Sports.Name = 'Tennis'
)
AND Person.Id in
(
SELECT SportsPerPerson.PersonId FROM SportsPerPerson
INNER JOIN Sports on SportsPerPerson.SportsId = Sports.Id
WHERE Sports.Name = 'Soccer'
)
OR
SELECT *
FROM Person
WHERE Id IN
(SELECT PersonId FROM SportsPerPerson WHERE SportsId IN
(SELECT Id FROM Sports WHERE Name = 'Tennis'))
AND Id IN
(SELECT PersonId FROM SportsPerPerson WHERE SportsId IN
(SELECT Id FROM Sports WHERE Name = 'Soccer'))
现在的问题是,是不是有写这个查询更简单的方法?只用OR就行不通,因为我需要玩'网球'和'足球'的人。但使用AND也不起作用,因为这些值不在同一行。
你应该使用两个连接的查询:
SELECT *
FROM Person p INNER JOIN SportsPerPerson spp1 ON (p.PersonId = spp1.PersonId)
INNER JOIN Sports s1 ON (s1.SportsIN = spp1.SportId)
INNER JOIN SportsPerPerson spp2 ON (p.PersonId = spp2.PersonId)
INNER JOIN Sports s2 ON (s2.SportId = spp2.SportId)
WHERE s1.Name = 'Tennis' AND s2.Name='Soccer'
您可以使用另一种JOIN
避免了二次IN
。子只选择返回那些玩的人都网球和足球:
SELECT *
FROM Person
WHERE Person.Id IN
(
SELECT spp1.PersonId
FROM SportsPerPerson spp1
JOIN SportsPerPerson spp2 ON (spp2.PersonId = spp1.PersonId)
JOIN Sports s1 on spp1.SportsId = s1.Id
JOIN Sports s2 on spp2.SportsId = s2.Id
WHERE s1.Name = 'Tennis'
AND s2.Name = 'Soccer'
)
恭喜10K。 :) – 2010-05-11 07:42:09
@Mark:谢谢!猜猜我们很快就会祝贺你50K? :) – 2010-05-11 10:56:00
奇怪的是,查询返回0记录:o我喜欢这个主意,但我只是测试它,它似乎并没有工作。它返回0条记录,我的查询返回55>。 Bas 2010-05-11 12:05:20
诀窍是使用别名,这样就可以使用同一个表多次:
SELECT p.*
FROM Person p
INNER JOIN SportsPerPerson spa
ON p.Id = spa.PersonId
INNER JOIN Sports sa
ON spa.SportsId = sa.Id
INNER JOIN SportsPerPerson spb
ON p.Id = spb.PersonId
INNER JOIN Sports sb
ON spb.SportsId = sb.Id
WHERE
sa.Name = 'Tennis'
AND sb.Name = 'Soccer'
此:
SELECT *
FROM Person p
WHERE (
SELECT COUNT(*)
FROM Sports s
JOIN SportsPerPerson sp
ON sp.SportsID = s.id
WHERE s.name IN ('Tennis', 'Soccer')
AND sp.PersonID = p.id
) = 2
或该:
SELECT p.*
FROM (
SELECT sp.PersonID
FROM Sports s
JOIN SportsPerPerson sp
ON sp.SportsID = s.id
WHERE s.name IN ('Tennis', 'Soccer')
GROUP BY
sp.PersonID
HAVING COUNT(*) = 2
) q
JOIN person p
ON p.id = q.personID
您需要申报UNIQUE KEY
或 SportsPerPerson (sportsid, personid)
以使其正确,快速地工作。
您需要查询的是:
SELECT p.ID, p.Name, p.LastName
FROM Person p
JOIN SportsPerPerson sp ON p.ID = sp.PersonID
JOIN Sports s ON sp.SportsID = s.ID
WHERE s.Name = 'Football'
也就是说,顺便说一句,在SportsPerPerson表中的ID密钥,是完全没有必要为了实现众多的你有很多的关系。使用PersonID和SportID列作为复合主键就足够了。
这并不能真正帮助我...如果我添加另一项运动,这是我的意图,它返回0记录,因为运动不在同一行。另外OR还是不会帮助我,因为那样它也会返回那些拥有“足球”或“网球”的人,我需要 – Bas 2010-05-11 12:09:06
这一个伎俩:] – Bas 2010-05-11 12:11:47