SELECT '与血液透析相关血液感染发生率' as zbmc, SUBSTR(YW_TS, 1, 7) yearmonth, TO_DATE(YW_TS, 'YYYY-MM-DD') yw_ts, ORG_CODE, ORG_NAME, SUM(NUM_FZ_3) NUM_FZ, SUM(NUM_FM_3) NUM_FM FROM UH_GR_ICU GROUP BY SUBSTR(YW_TS, 1, 7), TO_DATE(YW_TS, 'YYYY-MM-DD'), ORG_CODE, ORG_NAME 加四个字段,一个是上个月的num_fz,num_fm,还有是去年这个月的num_fz,num_fm
时间: 2024-03-20 16:41:56 浏览: 66
公务员管理系统的数据库设计
3星 · 编辑精心推荐
可以通过使用子查询或连接外部表来实现添加这些新字段的功能。以下是使用子查询的示例:
```
SELECT
'与血液透析相关血液感染发生率' as zbmc,
SUBSTR(YW_TS, 1, 7) yearmonth,
TO_DATE(YW_TS, 'YYYY-MM-DD') yw_ts,
ORG_CODE,
ORG_NAME,
SUM(NUM_FZ_3) NUM_FZ,
SUM(NUM_FM_3) NUM_FM,
(SELECT SUM(NUM_FZ_3) FROM UH_GR_ICU WHERE SUBSTR(YW_TS, 1, 7) = SUBSTR(UH_GR_ICU.YW_TS, 1, 7) AND ORG_CODE = UH_GR_ICU.ORG_CODE AND TO_DATE(YW_TS, 'YYYY-MM-DD') = ADD_MONTHS(TO_DATE(YW_TS, 'YYYY-MM-DD'), -1)) AS LAST_MONTH_NUM_FZ,
(SELECT SUM(NUM_FM_3) FROM UH_GR_ICU WHERE SUBSTR(YW_TS, 1, 7) = SUBSTR(UH_GR_ICU.YW_TS, 1, 7) AND ORG_CODE = UH_GR_ICU.ORG_CODE AND TO_DATE(YW_TS, 'YYYY-MM-DD') = ADD_MONTHS(TO_DATE(YW_TS, 'YYYY-MM-DD'), -1)) AS LAST_MONTH_NUM_FM,
(SELECT SUM(NUM_FZ_3) FROM UH_GR_ICU WHERE SUBSTR(YW_TS, 1, 7) = SUBSTR(UH_GR_ICU.YW_TS, 1, 7) - 1 AND ORG_CODE = UH_GR_ICU.ORG_CODE AND TO_DATE(YW_TS, 'YYYY-MM-DD') = TO_DATE(YW_TS, 'YYYY-MM-DD')) AS LAST_YEAR_NUM_FZ,
(SELECT SUM(NUM_FM_3) FROM UH_GR_ICU WHERE SUBSTR(YW_TS, 1, 7) = SUBSTR(UH_GR_ICU.YW_TS, 1, 7) - 1 AND ORG_CODE = UH_GR_ICU.ORG_CODE AND TO_DATE(YW_TS, 'YYYY-MM-DD') = TO_DATE(YW_TS, 'YYYY-MM-DD')) AS LAST_YEAR_NUM_FM
FROM UH_GR_ICU
GROUP BY
SUBSTR(YW_TS, 1, 7),
TO_DATE(YW_TS, 'YYYY-MM-DD'),
ORG_CODE,
ORG_NAME;
```
这个查询将在原始查询的基础上添加上个月和去年同期的 num_fz 和 num_fm 字段。请注意,这里使用了 Oracle 的 ADD_MONTHS 函数来计算上个月的日期,并使用了子查询来获取相应的值。如果使用的是其他数据库,可能需要使用不同的日期函数和语法来实现类似的查询。
阅读全文