SELECT AA.*, (SELECT SUM(JF) FROM YGJFLIST WHERE YGJFLIST.YGDM = AA.YGDM AND YGJFLIST.ZT = 1 AND YGJFLIST.JLRQ >= #{begin,jdbcType=TIMESTAMP} AND YGJFLIST.JLRQ <= #{end,jdbcType=TIMESTAMP}) AS ZJF
FROM (
SELECT YGDM,YGXM,SUM(CCHS) AS CCHS,SUM(DGRC) AS DGRC,SUM(ZLHS) AS ZLHS,SUM(DGRS) AS DGRSRC, SUM(XNLCC) AS
XNLCCRC,
SUM(ZBLB1) AS ZBLB1RC,SUM(ZBLB2) AS ZBLB2RC,SUM(ZBLB3) AS ZBLB3RC,SUM(ZBLB4) AS ZBLB4RC, SUM(WGS) AS
WGSRC,SUM(ZYRC) AS ZYRC,
SUM(RZXGRC) AS RZXGRC,SUM(HDRC) AS HDRC,SUM(HDFRC) AS HDFRC,SUM(HPRC) AS HPRC,SUM(UFHDRC) AS UFHDRC,SUM(txbmrc) AS txbmrc,
SUM(sjrc) as sjrc, sum(xjrc) as xjrc
FROM (
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
COUNT(1) AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0
AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST, HEMOLIST, EMPLIST, VASLIST, TLLXDICT , AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.CCHS = EMPLIST.YGDM AND
HEMOLIST.TLXH = VASLIST.TLXH AND
VASLIST.TLLX = TLLXDICT.JLID AND
HEMOLIST.SBID = AREADEV.SBID AND
TLLXDICT.TLTYPE = 1 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">${whereClause}
</if>
GROUP BY EMPLIST.YGDM,
EMPLIST.YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,COUNT(1) AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST, HEMOLIST, EMPLIST, VASLIST, TLLXDICT , AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.CCHS = EMPLIST.YGDM AND
HEMOLIST.TLXH = VASLIST.TLXH AND
VASLIST.TLLX = TLLXDICT.JLID AND
HEMOLIST.SBID = AREADEV.SBID AND
TLLXDICT.TLTYPE = 2 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY EMPLIST.YGDM,
EMPLIST.YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,COUNT(1) AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST, AREADEV
WHERE HLXJLIST.XTXH=HEMOLIST.XTXH AND
HLXJLIST.ZLHS=EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,COUNT(1) AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,HLJLLIST,HLJLLIST_QTSJ, AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.XTXH = HLJLLIST.XTXH AND
HLJLLIST.JLID = HLJLLIST_QTSJ.JLID AND
HEMOLIST.SBID = AREADEV.SBID AND
HLJLLIST_QTSJ.QTSJID = 9 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,COUNT(1) AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,HLJLLIST,HLJLLIST_QTSJ, AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.XTXH = HLJLLIST.XTXH AND
HLJLLIST.JLID = HLJLLIST_QTSJ.JLID AND
HEMOLIST.SBID = AREADEV.SBID AND
HLJLLIST_QTSJ.QTSJID = 10 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,COUNT(1) AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST, AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.ZBLB = 1 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,COUNT(1) AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST, AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.ZBLB = 2 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,COUNT(1) AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST, AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.ZBLB = 3 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,COUNT(1) AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST, AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.ZBLB = 4 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,COUNT(1) AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST, AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.KYFA = 3 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,COUNT(1) AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST, AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.BRLY = 2 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
COUNT(1) AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,AREADEV,VASLIST,TLLXDICT
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.TLXH = VASLIST.TLXH AND
VASLIST.TLLX = TLLXDICT.JLID AND
(TLLXDICT.LXMC = '人造血管' or VASLIST.TLBW IN (SELECT DMSB FROM DICTLIST WHERE DMLB = 44 AND DMMC = '人造血管')) AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,COUNT(1) AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,AREADEV,TXFSDICT
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.TXFS = TXFSDICT.TXFS AND
TXFSDICT.TXFSMC = 'HD' AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,COUNT(1) AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,AREADEV,TXFSDICT
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.TXFS = TXFSDICT.TXFS AND
TXFSDICT.TXFSMC = 'HDF' AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,COUNT(1) AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,AREADEV,TXFSDICT
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.TXFS = TXFSDICT.TXFS AND
TXFSDICT.GLBZ = 1 AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
UNION ALL
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,COUNT(1) AS UFHDRC,0 as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,AREADEV,TXFSDICT
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.TXFS = TXFSDICT.TXFS AND
TXFSDICT.TXFSMC = 'UFHD' AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
union all
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,count(1) as txbmrc, 0 as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.ZLHS = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
and hlxjlist.ZLSJ < 3.5
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
union all
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc,count(1) as sjrc, 0 as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.cchs = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
union all
SELECT EMPLIST.YGDM,EMPLIST.YGXM,
0 AS CCHS,0 AS DGRC,0 AS ZLHS,0 AS DGRS,0 AS XNLCC,0 AS ZBLB1,0 AS ZBLB2,0 AS ZBLB3,0 AS ZBLB4,0 AS WGS,0 AS ZYRC,
0 AS RZXGRC,0 AS HDRC,0 AS HDFRC,0 AS HPRC,0 AS UFHDRC,0 as txbmrc, 0 as sjrc, count(1) as xjrc
FROM HLXJLIST,HEMOLIST,EMPLIST,AREADEV
WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND
HLXJLIST.xjhs = EMPLIST.YGDM AND
HEMOLIST.SBID = AREADEV.SBID AND
HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND
HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND
HEMOLIST.TXZT = 3
<if test="centerid gt 0">
and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL}
</if>
<if test="qyid != null and qyid != ''">
and AREADEV.qyid IN (${qyid})
</if>
<if test="whereClause != null and whereClause != ''">
${whereClause}
</if>
GROUP BY YGDM,YGXM
) A GROUP BY YGDM,YGXM
) AA这种sql可以优化吗
扫一扫
194
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
