mysql - 连接的子查询(存在/存在)

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_id bigint(20) unsigned NOT NULL auto_increment,
wallet_id int(11) NOT NULL 默认 '0',
member_id int(10) unsigned NOT NULL default '0',
withdraw_date 日期默认为NULL,
数量 int(10) unsigned NOT NULL default '0',
gross_amount float(10,2) unsigned NOT NULL default '0.00',
admin_charge float(10,2) unsigned NOT NULL default '0.00',
tds float(10,2) unsigned NOT NULL default '0.00',
repo float(10,2) unsigned NOT NULL default '0.00',
net_amount float(10,2) unsigned NOT NULL default '0.00',
withdraw_type varchar(50) default NULL,
bank_name varchar(50) 默认 NULL,
cheque_no varchar(50) 默认 NULL,
courier_name varchar(50) 默认 NULL,
tracking_no varchar(50) 默认 NULL,
cheque_date 日期默认 '0000-00-00',
主键(request_id)，
KEY member_id (member_id)
) ENGINE=InnoDB 默认字符集=latin1 AUTO_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