连接的子查询（位于/存在）

Question

SELECT *, `o_cheque_request.member_id`, `o_cheque_request.wallet_id`
FROM `o_cheque_request`, `o_member`, o_memberinfo`
WHERE `o_cheque_request.member_id` = `o_member.member_id`
AND `o_member.member_id` = `o_memberinfo.member_id`
AND withdraw_date >='2012-07-21'
AND `o_cheque_request.member_id`
IN (SELECT `member_id` FROM `o_cheque_request` GROUP BY `member_id` HAVING SUM(gross_amount ) <=10000)
GROUP BY `o_cheque_request.withdraw_date`, `o_cheque_request.member_id` ORDER BY `request_id` DESC

这需要大约29秒的时间来减少...使用连接...伙计们请帮帮我...表结构o_cheque_request
request_idbigint（20）无符号非NULLauto_increment，
wallet_idint（11）非NULL默认'0'，
member_idint（10）无符号非NULL默认'0'，
withdraw_date日期默认为NULL，
金额int（10）无符号非NULL默认'0'，
gross_amount浮动（10,2）无符号非NULL默认'0.00'，
admin_charge浮动（10,2）无符号非NULL默认'0.00'，
tds浮动（10,2）无符号非NULL默认'0.00'，
回购浮动（10,2）无符号非NULL默认'0.00'，
net_amount浮动（10,2）无符号非NULL默认'0.00'，
withdraw_typebank_namevarchar（50）默认NULL，
cheque_novarchar（50）默认NULL，
courier_namevarchar（50）默认NULL，
tracking_novarchar（50）默认NULL，
cheque_date日期默认'0000-00-00'，
主要关键（request_id），
关键member_id（member_id）
）ENGINE=InnoDB DEFAULT CHARSET=latin1AUTO_INCREMENT=4738；





Answer 1

试试这个

 SELECT * FROM (
        SELECT *, `o_cheque_request.member_id`, `o_cheque_request.wallet_id`
        FROM `o_cheque_request`
        JOIN `o_member` ON `o_cheque_request.member_id` = `o_member.member_id`
        JOIN `o_memberinfo` ON `o_member.member_id` = `o_memberinfo.member_id`
        WHERE withdraw_date >='2012-07-21'
        GROUP BY `o_cheque_request.member_id` HAVING SUM(`o_cheque_request.gross_amount` ) <=10000
    ) AS T
    GROUP BY `withdraw_date`, `member_id` ORDER BY `request_id` DESC