急切询问 这2条语句如何优化
急切询问 这2条语句如何优化SELECT bon.id AS id,
bon.partnerId AS partnerId,
bon.bonusType AS bonusType,
bon.countryId AS countryId,
bon.affilateId AS affilateId,
bon.startDate AS startDate,
bon.endDate AS endDate,
bon.userId AS userId,
bon.createDate AS createDate,
bon.isEnable AS isEnable,
p.partnername AS partnerName,
u.userName AS userName,
ifnull (count (bte.userId), 0) AS bonusUserCount,
ifnull (sum (bte.bonusEUR), 0) AS bonusTotalAmount
FROM Bonus bon
INNER JOIN
Partner p
ON bon.partnerId = p.id
INNER JOIN
UserEntity u
ON bon.userId = u.id
LEFT OUTER JOIN
BonusTransactionEntity bte
ON bon.id = bte.bonusId
WHERE 1 = 1
AND bon.partnerId = 1
AND((bte.status NOT IN ('5', '8')) OR bte.status IS NULL)
GROUP BY bte.bonusId;
（插入话，这里左连接 where1=1 是什么意思啦）
特别是下面这条以后将用得很多的
SELECT bon1.id AS id,
bon1.partnerId AS partnerId,
bon1.bonusType AS bonusType,
bon1.countryId AS conuntryId,
bon1.affilateId AS affilateId,
bon1.startDate AS startDate,
bon1.endDate AS endDate,
bon1.userId AS userId,
bon1.createDate AS createDate,
bon1.isEnable AS isEnable,
p.partnername AS partnername,
u2.userName AS userName
FROM Bonus bon1, Partner p, UserEntity u1, UserEntity u2
WHERE u1.id = ?
AND u1.partnerId = bon1.partnerId
AND bon1.isEnable = '1'
AND u2.id = bon1.userId
AND bon1.partnerId = p.id
AND(bon1.id NOT IN
(SELECT bonTrans.bonusId
FROM BonusTransactionEntity bonTrans
WHERE bonTrans.userId = ?
AND bonTrans.status NOT IN ('5', '8'))); explain慢慢调
页:
[1]