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')));作者: kider 时间: 2009-6-12 14:55
explain慢慢调