记录一次mysql语句子查询调成连接查询的过程

1，有两个表。第一个tb_order_day_account,第二个tb_order_info.

tb_order_day_account记录的是每个员工上班期间内所有的交易汇总。tb_order_info记录的是所有交易的详细信息。两个表关联字段是gas_id。需求是统计出在员工上下班期间的所有交易汇总和统计出tb_order_info表中的微信和支付宝支付的汇总。

2，其实就是查出tb_order_day_account的数据，并用查询结果的gas_id, onwork_date和offwork_date作为条件，去tb_order_info中统计所有消费数据。  使用子查询比较简单。清晰明了，但是效率应该比较低。

SELECT
    t1.id AS id,
    t1.settle_accounts_date AS settleAccountsDate,
    t1.onwork_date AS onworkDate,
    t1.offwork_date AS offworkDate,
    t1.company_id AS companyId,
    t1.gas_id AS gasId,
    t1.onwork_emp_id AS onworkEmpId,
    t1.offwork_emp_id AS offworkEmpId,
    t1.total_trade_num AS totalTradeNum,
    t1.total_trade_amount AS totalTradeAmount,
    t1.box_trade_num AS boxTradeNum,
    t1.box_trade_amount AS boxTradeAmount,
    t1.etc_trade_num AS etcTradeNum,
    t1.etc_trade_amount AS etcTradeAmount,
    t1.remarks AS remarks,
    t1.create_date AS createDate,
    t1.update_date AS updateDate,
    t1.create_user_id AS createUserId,
    t1.update_user_id AS updateUserId,
    t1.is_delete AS isDelete,
    t1.is_correct_data AS isCorrectData,
    t1.is_total_settle AS isTotalSettle,
    t1.is_box_settle AS isBoxSettle,
    t1.is_etc_settle AS isEtcSettle,
    t1.pos_trade_num AS posTradeNum,
    t1.pos_trade_amount AS posTradeAmount,
    t1.nosense_trade_num AS nosenseTradeNum,
    t1.nosense_trade_amount AS nosenseTradeAmount,
    t1.is_pos_settle AS isPosSettle,
    t1.is_nosense_settle AS isNosenseSettle,
    t2.emp_name AS onworkEmpName,
    t3.emp_name AS offworkEmpName,
    t4.company_name AS companyName,
    t5.gas_name AS gasName,
    (
SELECT
    count( CASE WHEN t6.pay_channel = 2 THEN t6.id ELSE NULL END ) AS wxPayTradeNum 
FROM
    tb_order_info t6 
WHERE
    t6.is_delete = 0 
    AND t6.order_status = 2 
    AND t6.payment_time >= onworkDate 
    AND t6.payment_time <= offworkDate 
    ) AS wxPayTradeNum,
    (
SELECT
    sum( CASE WHEN t6.pay_channel = 2 THEN t6.real_price ELSE 0 END ) AS wxPayTradeAmount 
FROM
    tb_order_info t6 
WHERE
    t6.is_delete = 0 
    AND t6.order_status = 2 
    AND t6.payment_time >= onworkDate 
    AND t6.payment_time <= offworkDate 
    ) AS wxPayTradeAmount,
    (
SELECT
    count( CASE WHEN t6.pay_channel = 3 THEN t6.id ELSE NULL END ) AS aliPayTradeNum 
FROM
    tb_order_info t6 
WHERE
    t6.is_delete = 0 
    AND t6.order_status = 2 
    AND t6.payment_time >= onworkDate 
    AND t6.payment_time <= offworkDate 
    ) AS aliPayTradeNum,
    (
SELECT
    sum( CASE WHEN t6.pay_channel = 3 THEN t6.real_price ELSE 0 END ) AS aliPayTradeAmount 
FROM
    tb_order_info t6 
WHERE
    t6.is_delete = 0 
    AND t6.order_status = 2 
    AND t6.payment_time >= onworkDate 
    AND t6.payment_time <= offworkDate 
    ) AS aliPayTradeAmount 
FROM
    tb_order_day_accounts t1
    LEFT JOIN tb_base_employee_info t2 ON t2.is_delete = 0 
    AND t2.id = t1.onwork_emp_id
    LEFT JOIN tb_base_employee_info t3 ON t3.is_delete = 0 
    AND t3.id = t1.offwork_emp_id
    LEFT JOIN tb_base_company_info t4 ON t4.is_delete = 0 
    AND t4.id = t1.company_id
    LEFT JOIN tb_base_gas_info t5 ON t5.is_delete = 0 
    AND t5.id = t1.gas_id 
WHERE
    t1.is_delete = 0 
    AND t1.onwork_date IS NOT NULL 
    AND t1.offwork_date IS NOT NULL 
  AND t1.settle_accounts_date >= '2018-08-20 00:00:00' 
  AND t1.settle_accounts_date <= '2019-08-20 23:59:59' 
ORDER BY
    t1.create_date,
    t5.gas_name DESC 

3，优化为带子查询的连接查询。

select t.*,
    SUM( IF ( pay_channel = 2, real_price, 0 ) ) wxPayTradeNum,
    count( pay_channel = 2 OR NULL ) wxPayTradeAmount,
    SUM( IF ( pay_channel = 3, real_price, 0 ) ) aliPayTradeNum,
    count( pay_channel = 3 OR NULL ) aliPayTradeAmount 
from
(SELECT
  t1.id AS id,
  t1.settle_accounts_date AS settleAccountsDate,
  t1.onwork_date AS onworkDate,
  t1.offwork_date AS offworkDate,
  t1.company_id AS companyId,
  t1.gas_id AS gasId,
  t1.onwork_emp_id AS onworkEmpId,
  t1.offwork_emp_id AS offworkEmpId,
  t1.total_trade_num AS totalTradeNum,
  t1.total_trade_amount AS totalTradeAmount,
  t1.box_trade_num AS boxTradeNum,
  t1.box_trade_amount AS boxTradeAmount,
  t1.etc_trade_num AS etcTradeNum,
  t1.etc_trade_amount AS etcTradeAmount,
  t1.remarks AS remarks,
  t1.create_date AS createDate,
  t1.update_date AS updateDate,
  t1.create_user_id AS createUserId,
  t1.update_user_id AS updateUserId,
  t1.is_delete AS isDelete,
  t1.is_correct_data AS isCorrectData,
  t1.is_total_settle AS isTotalSettle,
  t1.is_box_settle AS isBoxSettle,
  t1.is_etc_settle AS isEtcSettle,
  t1.pos_trade_num AS posTradeNum,
  t1.pos_trade_amount AS posTradeAmount,
  t1.nosense_trade_num AS nosenseTradeNum,
  t1.nosense_trade_amount AS nosenseTradeAmount,
  t1.is_pos_settle AS isPosSettle,
  t1.is_nosense_settle AS isNosenseSettle,
  t2.emp_name AS onworkEmpName,
  t3.emp_name AS offworkEmpName,
  t4.company_name AS companyName,
  t5.gas_name AS gasName,
  t6.pay_channel as pay_channel,
  t6.real_price as real_price
FROM
  tb_order_day_accounts t1
  LEFT JOIN tb_base_employee_info t2 ON t2.is_delete = 0 
  AND t2.id = t1.onwork_emp_id
  LEFT JOIN tb_base_employee_info t3 ON t3.is_delete = 0 
  AND t3.id = t1.offwork_emp_id
  LEFT JOIN tb_base_company_info t4 ON t4.is_delete = 0 
  AND t4.id = t1.company_id
  LEFT JOIN tb_base_gas_info t5 ON t5.is_delete = 0 
  AND t5.id = t1.gas_id 
  left join tb_order_info t6 on t6.is_delete = 0  
   and t6.gas_id = t1.gas_id
   and t6.payment_time >= t1.onwork_date  
   and t6.payment_time  <= t1.offwork_date  
WHERE
  t1.is_delete = 0 
  AND t1.onwork_date IS NOT NULL 
  AND t1.offwork_date IS NOT NULL 
  AND t1.settle_accounts_date >= '2018-08-20 00:00:00' 
  AND t1.settle_accounts_date <= '2019-08-20 23:59:59' 
ORDER BY
  t1.create_date ) t
  GROUP BY settleAccountsDate

4，继续优化，修改成没有子查询的连接查询。

SELECT
    t1.id AS id,
    t1.settle_accounts_date AS settleAccountsDate,
    t1.onwork_date AS onworkDate,
    t1.offwork_date AS offworkDate,
    t1.company_id AS companyId,
    t1.gas_id AS gasId,
    t1.onwork_emp_id AS onworkEmpId,
    t1.offwork_emp_id AS offworkEmpId,
    t1.total_trade_num AS totalTradeNum,
    t1.total_trade_amount AS totalTradeAmount,
    t1.box_trade_num AS boxTradeNum,
    t1.box_trade_amount AS boxTradeAmount,
    t1.etc_trade_num AS etcTradeNum,
    t1.etc_trade_amount AS etcTradeAmount,
    t1.remarks AS remarks,
    t1.create_date AS createDate,
    t1.update_date AS updateDate,
    t1.create_user_id AS createUserId,
    t1.update_user_id AS updateUserId,
    t1.is_delete AS isDelete,
    t1.is_correct_data AS isCorrectData,
    t1.is_total_settle AS isTotalSettle,
    t1.is_box_settle AS isBoxSettle,
    t1.is_etc_settle AS isEtcSettle,
    t1.pos_trade_num AS posTradeNum,
    t1.pos_trade_amount AS posTradeAmount,
    t1.nosense_trade_num AS nosenseTradeNum,
    t1.nosense_trade_amount AS nosenseTradeAmount,
    t1.is_pos_settle AS isPosSettle,
    t1.is_nosense_settle AS isNosenseSettle,
    t2.emp_name AS onworkEmpName,
    t3.emp_name AS offworkEmpName,
    t4.company_name AS companyName,
    t5.gas_name AS gasName,
    SUM( IF ( pay_channel = 2, real_price, 0 ) ) wxPayTradeNum,
    count( pay_channel = 2 OR NULL ) wxPayTradeAmount,
    SUM( IF ( pay_channel = 3, real_price, 0 ) ) aliPayTradeNum,
    count( pay_channel = 3 OR NULL ) aliPayTradeAmount 
FROM
    tb_order_day_accounts t1
    LEFT JOIN tb_base_employee_info t2 ON t2.is_delete = 0 
    AND t2.id = t1.onwork_emp_id
    LEFT JOIN tb_base_employee_info t3 ON t3.is_delete = 0 
    AND t3.id = t1.offwork_emp_id
    LEFT JOIN tb_base_company_info t4 ON t4.is_delete = 0 
    AND t4.id = t1.company_id
    LEFT JOIN tb_base_gas_info t5 ON t5.is_delete = 0 
    AND t5.id = t1.gas_id
    LEFT JOIN tb_order_info t6 ON t6.is_delete = 0 
    AND t6.gas_id = t1.gas_id 
    AND t6.payment_time >= t1.onwork_date 
    AND t6.payment_time <= t1.offwork_date 
WHERE
    t1.is_delete = 0 
    AND t1.onwork_date IS NOT NULL 
    AND t1.offwork_date IS NOT NULL 
     AND t1.settle_accounts_date >= '2018-08-20 00:00:00' 
  AND t1.settle_accounts_date <= '2019-08-20 23:59:59' 
    GROUP BY settleAccountsDate
ORDER BY
    t1.create_date,
    t5.gas_name DESC