sql 查询同一个字段不同

 

 

 SELECT
	DATE_FORMAT( t_de.last_time, "%Y/%m/%d" ) AS lastTime,
	COUNT( CASE WHEN t.variance_of_result = 1 THEN t.variance_of_result END ) AS 'normal',
	COUNT( CASE WHEN t.variance_of_result = 2 THEN t.variance_of_result END ) AS 'Surplus',
	COUNT( CASE WHEN t.variance_of_result = 3 THEN t.variance_of_result END ) AS 'Deficit'
FROM
	t_tasks AS t,
	t_task_detail AS t_de
WHERE
	t.task_details_id = t_de.id
	AND t_de.tenant_number = #{tenantNumber}
	AND t.variance_of_result IN ( 1, 2, 3 )
        <choose>
            <when test="createrTimeBefore =null  ">
                AND date_format( t_de.last_time, '%Y-%m' )= date_format( now(), '%Y-%m' )
             </when>
            <otherwise>
                <if test="createrTimeBefore != null and createrTimeBefore != ''">
                    AND(creater_time BETWEEN DATE_FORMAT(#{createrTimeBefore},'%Y-%m-%d %H:%i:%s') AND
                    DATE_FORMAT(#{createrTimeAfter},'%Y-%m-%d %H:%i:%s'))
                </if>
            </otherwise>
        </choose>
GROUP BY
	DATE_FORMAT( t_de.last_time, "%Y/%m/%d/" );