sql语句统计求和两种 写的方式

第一种：

SELECT t1.name , 
 (select COUNT(*) from ysl_order where TYPE ='YSL_TEACHING_ROUNDS' and t1.id = hospital_id)  as jxcfTj,
 (select COUNT(*) from ysl_order where TYPE ='YSL_COMMON_CLINIC' and t1.id = hospital_id)    as pthzTj,
 (select COUNT(*) from ysl_order where TYPE ='YSL_MULTI_DISCIPLINE' and t1.id = hospital_id) as dxkhzTj,
 (select COUNT(*) from ysl_order where TYPE ='YSL_OUTPATIENT' and t1.id = hospital_id)       as mzTj,
 (select COUNT(*) from ysl_order where TYPE ='YSL_OPERATION' and t1.id = hospital_id)        as ssTj,
 (select COUNT(*) from ysl_order where TYPE ='YSL_MEDICAL_TRAINING' and t1.id = hospital_id) as ylpxTj,
 (select COUNT(*) from ysl_order where TYPE ='YSL_WORK_SHOP' and t1.id = hospital_id)        as xshyTj,
 (select COUNT(*) from ysl_order where TYPE ='YSL_MADE_TO_ORDER' and t1.id = hospital_id)    as zdrwTj
FROM ysl_hospital t1 LEFT JOIN ysl_order t2 on t1.id = t2.hospital_id 
<where>
<if test="hospitalName != null and hospitalName != ''">
AND t1.`NAME` like '%${hospitalName}%'
</if>
<if test="timeBegin != null and timeBegin != ''">
AND t2.ORDER_DATE &gt;= #{timeBegin}
</if>
<if test="timeEnd != null and timeEnd != ''">
AND  t2.ORDER_DATE &lt;= #{timeEnd}
</if>
</where>
GROUP BY t1.id

第二种：

 SELECT t1.USER_NAME,
       IFNULL(t2.YSL_MEDICAL_TRAINING, 0) as YSL_MEDICAL_TRAINING,
       IFNULL(t2.YSL_MULTI_DISCIPLINE, 0) as YSL_MULTI_DISCIPLINE,
       IFNULL(t2.YSL_WORK_SHOP, 0) as YSL_WORK_SHOP,
       IFNULL(t2.YSL_MADE_TO_ORDER, 0) as YSL_MADE_TO_ORDER,
       IFNULL(t2.YSL_OPERATION, 0) as YSL_OPERATION,
       IFNULL(t2.YSL_TEACHING_ROUNDS, 0) as YSL_TEACHING_ROUNDS,
       IFNULL(t2.YSL_COMMON_CLINIC, 0) as YSL_COMMON_CLINIC,
       IFNULL(t2.YSL_OUTPATIENT, 0) as YSL_OUTPATIENT,
       IFNULL(t2.total_cnt, 0) as total_cnt
 FROM ysl_doctor t1
 LEFT JOIN (SELECT t11.DOCTOR_ID,
                   sum(IF(t12.TYPE = 'YSL_MEDICAL_TRAINING', 1, 0)) AS YSL_MEDICAL_TRAINING,
                   sum(IF(t12.TYPE = 'YSL_MULTI_DISCIPLINE', 1, 0)) AS YSL_MULTI_DISCIPLINE,
                   sum(IF(t12.TYPE = 'YSL_WORK_SHOP', 1, 0)) AS YSL_WORK_SHOP,
                   sum(IF(t12.TYPE = 'YSL_MADE_TO_ORDER', 1, 0)) AS YSL_MADE_TO_ORDER,
                   sum(IF(t12.TYPE = 'YSL_OPERATION', 1, 0)) AS YSL_OPERATION,
                   sum(IF(t12.TYPE = 'YSL_TEACHING_ROUNDS', 1, 0)) AS YSL_TEACHING_ROUNDS,
                   sum(IF(t12.TYPE = 'YSL_COMMON_CLINIC', 1, 0)) AS YSL_COMMON_CLINIC,
                   sum(IF(t12.TYPE = 'YSL_OUTPATIENT', 1, 0)) AS YSL_OUTPATIENT,
                   count(1) AS total_cnt
              FROM ysl_doctor_Order t11
             INNER JOIN ysl_order t12
                ON t11.ORDER_ID = t12.id
             WHERE t11. STATUS = '80'
              <if test="timeBegin != null and timeBegin != ''">
               AND t11.ACCEPT_TIME &gt;= #{timeBegin}
             </if>
             <if test="timeEnd != null and timeEnd != ''">
               AND t11.ACCEPT_TIME &lt;= #{timeEnd}
             </if>
             GROUP BY t11.DOCTOR_ID) t2
   ON t1.id = t2.DOCTOR_ID
   <where>
    <if test="hospitalName != null and hospitalName != ''">
    AND t1.USER_NAME LIKE '%${hospitalName}%'
    </if>
   </where>

/*********************************************************************************************************************************************************/

 SELECT T11.ID AS HOSPITAL_ID,
       T11.NAME AS HOSPITAL_NAME,
       T12. TYPE_VAL,
       IFNULL(T12.MONEY, 0) AS MONEY,
       IFNULL(T12.ASSING_MONEY, 0) AS ASSING_MONEY,
       IFNULL(T12.REQUIRE_NUM, 0) AS REQUIRE_NUM,
       IFNULL(T12.SIGN_NUM, 0) AS SIGN_NUM,
       IFNULL(T12.AVG_MONEY, 0) AS MONEY
    FROM YSL_HOSPITAL T11
  LEFT JOIN (SELECT T1.HOSPITAL_ID,
  T1.ORDER_DATE,
                    T2.LABEL AS TYPE_VAL,
                    SUM(T1.MONEY) AS MONEY,
                    SUM(T1.ASSING_MONEY) AS ASSING_MONEY,
                    SUM(T1.REQUIRE_NUM) AS REQUIRE_NUM,
                    SUM(T1.SIGN_NUM) AS SIGN_NUM,
                    FORMAT(SUM(T1.MONEY) / COUNT(1), 2) AS AVG_MONEY
               FROM YSL_ORDER T1
               LEFT JOIN (SELECT LABEL, VALUE
                           FROM SYS_DICT
                          WHERE TYPE = 'order_task_type') T2
                 ON T1.TYPE = T2. VALUE
              ORDER BY T1.HOSPITAL_ID, T1.TYPE) T12
     ON T11.ID = T12.HOSPITAL_ID
    <where>
<if test="hospitalName != null and hospitalName != ''">
AND T11.`NAME` LIKE '%${hospitalName}%'
</if>
        <if test="timeBegin != null and timeBegin != ''">
        AND T12.ORDER_DATE &gt;= #{timeBegin}
        </if>
        <if test="timeEnd != null and timeEnd != ''">
        AND T12.ORDER_DATE &lt;= #{timeEnd}
        </if>
</where>