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

缺乏、安全感 2021-10-19 11:30 280阅读 0赞

1，有两个表。第一个tb\_order\_day\_account,第二个tb\_order\_info.

tb\_order\_day\_account记录的是每个员工上班期间内所有的交易汇总。tb\_order\_info记录的是所有交易的详细信息。两个表关联字段是gas\_id。需求是统计出在员工上下班期间的所有交易汇总和统计出tb\_order\_info表中的微信和支付宝支付的汇总。

![20190821091943350.png][]![2019082109201830.png][]

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

[20190821091943350.png]: https://img-blog.csdnimg.cn/20190821091943350.png
[2019082109201830.png]: /images/20211018/cba748bd9f6045bbac66bff30409ced5.png