mysql4 不支持子查询 支持标量查询

SELECT a.id, a. name eventName, a.startDate, a.endDate, c.odds, c.oddsStatusId, c.oddsChangedDate, e. name providerName, (

SELECT b. name
FROM Participant b
WHERE a.homeParticipantId = b.id
)homeParticipantName, (

SELECT b. name
FROM Participant b
WHERE a.awayParticipantId = b.id
)awayParticipantName, (

CASE d.integerParameter
WHEN a.homeParticipantId
THEN 'win'
WHEN a.awayParticipantId
THEN 'lose'
WHEN NULL
THEN 'draw'
END
)oddWinnerType
FROM Event a, BettingOffer c, Outcome d, Provider e
WHERE c.outcomeId = d.id
AND d.eventId = a.id
AND d.typeId =43
AND c.providerId = e.id
ORDER BY startDate DESC
LIMIT 0 , 30

其中

(SELECT b. name
FROM Participant b
WHERE a.homeParticipantId = b.id
)homeParticipantName, (

SELECT b. name
FROM Participant b
WHERE a.awayParticipantId = b.id
)awayParticipantName

这两个如果用内联接的话,速度将降慢许多,用标量查询,速度快很多,但要求一次标量查询出来的必须是一条记录

('标量查询'是在oracle里称呼这种用法的)

转载于:https://blog.51cto.com/xu20cn/97910