永洪社区

标题: 创建数据集中使用了union all,数据不对 [打印本页]

作者: yhdata_ImTWeAhs 时间: 2024-12-3 09:02
标题: 创建数据集中使用了union all,数据不对

作者: yhbi老6了 时间: 2024-12-3 09:09
有点意思，围观

作者: 阿姆斯特朗炮 时间: 2024-12-3 09:11
看看sql

作者: yhdata_ImTWeAhs 时间: 2024-12-3 09:24
SELECT hsdw, cpx, xsyssdq, khmc, tjkhmc, htslm, 0 prehtslm,zrmbje,CAST(FORMAT(shrq, 'yyyy-MM') + '-01' AS DATE) AS shrq into #a
FROM EDA_ODS_Yxgl_Yxmljdsj
WHERE YEAR(shrq) = year(getdate()) AND MONTH(shrq) = 11
union all
SELECT hsdw, cpx, xsyssdq, khmc, tjkhmc, 0 htslm, htslm prehtslm,zrmbje,CAST(FORMAT(shrq, 'yyyy-MM') + '-01' AS DATE) AS shrq
FROM EDA_ODS_Yxgl_Yxmljdsj
WHERE YEAR(shrq) = year(getdate())-1 AND MONTH(shrq) = 11

select * from #a
select sum(htslm)htslm,sum(prehtslm)prehtslm from #a

作者: yhdata_ImTWeAhs 时间: 2024-12-3 09:25

显示的结果是错误的，但去掉union all 是对的

作者: 防超 时间: 2024-12-3 09:50
单独执行试一下，看看是否有重复数据，SELECT hsdw,
   cpx,
   xsyssdq,
   khmc,
   tjkhmc,
   htslm,
   0 prehtslm,
   zrmbje,
   CAST(FORMAT(shrq, 'yyyy-MM') + '-01' AS DATE) AS shrq
  into #a
  FROM EDA_ODS_Yxgl_Yxmljdsj
WHERE YEAR(shrq) = year(getdate())
AND MONTH(shrq) = 11;

SELECT hsdw,
   cpx,
   xsyssdq,
   khmc,
   tjkhmc,
   0 htslm,
   htslm prehtslm,
   zrmbje,
   CAST(FORMAT(shrq, 'yyyy-MM') + '-01' AS DATE) AS shrq
  into #a
  FROM EDA_ODS_Yxgl_Yxmljdsj
WHERE YEAR(shrq) = year(getdate()) - 1
AND MONTH(shrq) = 11;

欢迎光临永洪社区 (https://club.yonghongtech.com/)