将多个MySQL查询转换为单个查询

Question

QUERY 1...

$result = $wpdb->get_results("SELECT wp_users.ID,wp_users.user_login,wp_users.user_registered,wp_users.user_email,t.total,t.acc_nums FROM wp_users LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) left join (SELECT count(*) as total,user_id,Group_concat(account_number) as acc_nums FROM `user_per_bank` group by user_id) as t on t.user_id=wp_users.ID WHERE 1=1 AND ( 
  ( 
( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"editor\"%' )
  )
) ORDER BY user_registered DESC", ARRAY_A);

TO BE COMBINED WITH QUERY...

$out = $wpdb->get_results('SELECT `user_id`, sum(`amount`) as outstanding FROM `assist_trans` LEFT JOIN `wp_users` ON wp_users.id = assist_trans.user_id WHERE `status` IN (0,2,4) GROUP BY assist_trans.user_id ORDER DESC');

so that "$out" variable can be dropped and I can use "$result" instead...

Answer 1

If I've understood your question correctly, you need to make a join from the first query to the second, and have the outstanding column in the result set. Something like this should work. It's untested, but it should point you in the right direction if I've made an error.

The only changes (besides formatting) are the new join and the additional column in the result set.

$result = $wpdb->get_results("SELECT wp_users.ID,
                                     wp_users.user_login,
                                     wp_users.user_registered,
                                     wp_users.user_email,
                                     t.total,
                                     t.acc_nums,
                                     o.outstanding
                                FROM wp_users
                                     LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )
                                     LEFT JOIN (
                                         SELECT count(*) as total,
                                                user_id,
                                                Group_concat(account_number) as acc_nums
                                          FROM `user_per_bank`
                                       GROUP BY user_id) as t on t.user_id = wp_users.ID
                                     LEFT JOIN (
                                         SELECT `user_id`, sum(`amount`) as outstanding
                                           FROM `assist_trans`
                                                LEFT JOIN `wp_users` ON wp_users.id = assist_trans.user_id
                                          WHERE `status` IN (0,2,4)
                                       GROUP BY assist_trans.user_id) as o ON ( wp_users.ID = o.user_id )
                               WHERE 1=1
                                 AND ( 
                                       ( 
                                         ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"editor\"%' )
                                       )
                                     )
                            ORDER BY user_registered DESC",
            ARRAY_A);