注册

union连接报数据溢出错误,大侠看看哪里需要改进

松林 2021/01/28 2079 2

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:dm8
【操作系统】:windows2016
【CPU】:intel pentium G2010 2.8G
【问题描述】*:报数据溢出错误
从sql server 迁移到dm8
3个sql单独执行都可以执行,没错误;前两个union也没有错误,第三个和前两个一合并就报溢出错误

SELECT peformance_inst_id f00,member_id f01,KPI_peformance_item.peformance_item_id f02,KPI_peformance_item.item_type f03,item_name f04
,(SELECT EnumString FROM UEnumData WHERE EnumTypeCode=‘KPI_data_source’ AND EnumValue=data_source) f05
,peformance_value f06,course_workload f07,order_no f08,KPI_peformance_item.parent_id f09,data_source f10,select_type f11,item_ratio f12
,order_no f13,0 f14,0 f15,1 f16
FROM KPI_peformance_item LEFT JOIN (SELECT * FROM KPI_peformance_inst WHERE member_id=3026) KPI_peformance_inst
ON KPI_peformance_item.peformance_item_id=KPI_peformance_inst.peformance_item_id
WHERE KPI_peformance_item.parent_id=0 AND KPI_peformance_item.workload_type_id=1 AND KPI_peformance_item.year_id=2

UNION

SELECT peformance_inst_id f00,member_id f01,KPI_peformance_item.peformance_item_id f02,KPI_peformance_item.item_type f03
,item_name f04,’ ’ f05,peformance_value f06,course_workload f07,order_no f08,KPI_peformance_item.parent_id f09,data_source f10
,select_type f11,item_ratio f12,(SELECT i2.order_no FROM KPI_peformance_item i2 WHERE i2.peformance_item_id=KPI_peformance_item.parent_id) f13
,order_no f14,0 f15,2 f16
FROM KPI_peformance_item LEFT JOIN (SELECT * FROM KPI_peformance_inst WHERE member_id=3026) KPI_peformance_inst
ON KPI_peformance_item.peformance_item_id=KPI_peformance_inst.peformance_item_id
WHERE KPI_peformance_item.parent_id>0 AND KPI_peformance_item.workload_type_id=1 AND KPI_peformance_item.year_id=2

UNION

SELECT inst_detail_id f00,KPI_peformance_inst_detail.member_id f01,KPI_peformance_item.peformance_item_id f02,KPI_peformance_item.item_type f03,
data_title + COALESCE(data_time,‘’) f04,(SELECT EnumString FROM UEnumData WHERE EnumTypeCode=‘KPI_InstDetailType’ AND EnumValue=data_type) f05
,data_count f06,data_course_time f07,KPI_peformance_inst_detail.order_no f08,KPI_peformance_item.parent_id f09,data_source f10,select_type f11
,item_ratio f12,(CASE WHEN KPI_peformance_item.parent_id=0 THEN KPI_peformance_item.order_no ELSE (
SELECT i2.order_no FROM KPI_peformance_item i2 WHERE i2.peformance_item_id=KPI_peformance_item.parent_id) END) F13,KPI_peformance_item.order_no f14
,(CASE WHEN KPI_peformance_item.parent_id=0 THEN 0 ELSE KPI_peformance_item.order_no END) F15,3 f16
FROM KPI_peformance_item INNER JOIN KPI_peformance_inst_detail
ON KPI_peformance_item.peformance_item_id=KPI_peformance_inst_detail.peformance_item_id
INNER JOIN (SELECT * FROM KPI_peformance_inst WHERE member_id=3026) KPI_peformance_inst
ON KPI_peformance_item.peformance_item_id=KPI_peformance_inst.peformance_item_id
WHERE KPI_peformance_item.workload_type_id=1 AND KPI_peformance_item.year_id=2 AND KPI_peformance_inst_detail.member_id=3026

ORDER BY f13,f14,F15

回答 0
暂无回答
扫一扫
联系客服