MySQL 查询语句书写和执行顺序

MySQL 语句执行的顺序，并不是按照 SQL 语句的顺序。

下面是 SQL 的书写顺序

SELECT DISTINCT
 < select_list >
FROM
 < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
 < where_condition >
GROUP BY
 < group_by_list >
HAVING
 < having_condition >
ORDER BY
 < order_by_condition >
LIMIT < limit_number >

下面是 SQL 的执行顺序

FROM <driving_table>
WHERE <driving_table_where_condition>

IF <join_condition> Hits INDEX THEN:
    JOIN WITH INDEX # IGNORE <Using Join Buffer> 
    WHERE <driven_table_where_condition>
ELSE:
    WHERE <driven_table_where_condition> # 尽量使用 where 缩小结果集
    JOIN <Using Join Buffer>
END

SELECT [DISTINCT] <select_list>

GROUP BY <group_by_list> # SELECT 后的字段, 可以用于 GROUP BY 和 HAVING
HAVING <having_condition> # 分组后的条件筛选，常搭配聚合函数使用

ORDER BY <order_by_condition> # 对可能分组后的记录集进行排序

LIMIT <limit_number>

/**
 * 1. 先select TransTime2和TransType2, 后GROUP BY TransTime2, TerminalID, TransType2
 * 2. CASE WHEN THEN ELSE END 和IF(state1,state2,state3)语法
 * 3. SUM()和IF()函数同时用法
 */
SELECT 
    LEFT(TransTime,10) as TransTime2, 
    CASE WHEN `TransType`='CI' OR `TransType`='CO' THEN 'coin' ELSE ( CASE WHEN (`TransType`='PC' OR `TransType`='SR' OR `TransType`='VO' ) AND (TransResult = 'ff' or TransResult = '01') THEN 'card' ELSE '' END) END as TransType2,
    SUM(IF(`TransType`='VO', -TransAmount, TransAmount)) as TransType2
FROM 'table_name'
WHERE `TransTime` >= '2020-01-01 00:00:00' AND `TransTime` <= '2020-01-10 23:59:59'

GROUP BY TransTime2, TerminalID, TransType2
HAVING TransType2 != '' 
ORDER BY TransTime2 DESC