The method of adding conditional expression to aggregate function in MySql

Filtering timing of where condition and having condition when Mysql and aggregate function are together

Where filters before aggregation

When a query contains aggregate functions and where conditions, situations like this

Select max (cid) from t where t.id=5

Group first, then aggregate, and then filter the result set whose aggregate result is greater than or equal to 5

The difference between the two:

Where executes first and then executes the aggregate function. Having is executed after the aggregate function has been executed.

The following is a supplement

There is a requirement, a table, there is a status field (1: success, 2: failure, and so on). Now we want to use date grouping to count the number in different states.

Write a subquery first.

Select aa.logDate,aa.totalLogs, (select count (1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT (startTime,'% YMY% MMI% d') and executeStatus=1) pendingLogs, (select count (1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT (startTime,'% YMY% MMY% d') and executeStatus=2) successLogs, (select count (1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT (startTime,'% YMY% MMI% d') and executeStatus=3) errorLogs (select count (1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT (startTime,'% YMY% MMI% d') and executeStatus=4) callbackErrorLogsfrom (select DATE_FORMAT (a.startTime,'% YMY% MMI% d') logDate, count (1) totalLogs from dxp.dxp_handlermodel a group by DATE_FORMAT (a.startTime,'% YMY% MMI% d') aa

The execution is quite slow. Thinking about whether conditions can be added in count, I looked for it as follows:

SelectDATE_FORMAT (startTime,'% Ymuri% mmurf% d') logDate, count (1) totalLogs, count (if (executeStatus=1,true,null)) pendingLogs, count (if (executeStatus=2,true,null)) successLogs, count (if (executeStatus=3,true,null)) errorLogs, count (if (executeStatus=4,true,null)) callbackErrorLogsfrom dxp.dxp_handlermodelgroup by DATE_FORMAT (startTime,'% YMY% MFE% d'))

Simple and easy to understand, and very efficient

Other aggregate functions can also be used, such as SUM

Practical examples:

Select count (if (create_date < '2017-01-01' and host_profile_id = '9294d2bf-f457-4fe5-9a36mure5f832310dc2) from profile_visit_log-equivalent to select count (create_date <' 2017-01-01) count from profile_visit_log where host_profile_id = '9294d2bf-f457-4fe5-9a36mure5f832310dc2'

All right, that's all for this article. I hope you will support me in the future.