mysql 2条查询结果 UNION 后 数据溢出

[遁去得一]

mysql 2条查询结果 UNION 后 数据溢出

sql 语句一

SELECT
	a.BILLNO AS DanHao,a.StorageNAME AS LaiYuan,a.ArrivalTIME,
	b.ITEM_ID,b.PRICE,b.APPROVALCNT AS cnt,b.AMT AS amt,a.type
FROM
	erp_returngys_m a,erp_returngys_d b
WHERE
	a.ArrivalTIME BETWEEN '2017/1/1 0:00:00' AND '2017/6/22 0:00:00'
	AND a.statue = '已审核'AND b.item_id = '3789'AND a.id = b.MAIN_ID

结果一


sql 语句二

SELECT
		c.BILLNO AS DanHao,c.StorageNAME AS LaiYuan,c.ArrivalTIME,d.ITEM_ID,
		d.PRICE,d.FHCNT AS cnt,ifnull((d.PRICE * d.FHCNT), 0) AS amt,c.type
	FROM
		erp_billwl_m c,erp_billwl_d d
	WHERE
		c.ArrivalTIME BETWEEN '2017/1/1 0:00:00'AND '2017/6/22 0:00:00' 
		AND ( c.STATUE = '已验收' OR c.STATUE = '已发货')AND d.item_id = '3789'
	AND c.type = '总仓配送'AND c.id = d.MAIN_ID AND c.BILLNO = 'SG2017030800022'

结果二



那么问题来了
sql语句一 UNION sql 语句二

SELECT
	a.BILLNO AS DanHao,a.StorageNAME AS LaiYuan,a.ArrivalTIME,
	b.ITEM_ID,b.PRICE,b.APPROVALCNT AS cnt,b.AMT AS amt,a.type
FROM
	erp_returngys_m a,erp_returngys_d b
WHERE
	a.ArrivalTIME BETWEEN '2017/1/1 0:00:00' AND '2017/6/22 0:00:00'
	AND a.statue = '已审核'AND b.item_id = '3789'AND a.id = b.MAIN_ID
UNION 
SELECT
		c.BILLNO AS DanHao,c.StorageNAME AS LaiYuan,c.ArrivalTIME,d.ITEM_ID,
		d.PRICE,d.FHCNT AS cnt,ifnull((d.PRICE * d.FHCNT), 0) AS amt,c.type
	FROM
		erp_billwl_m c,erp_billwl_d d
	WHERE
		c.ArrivalTIME BETWEEN '2017/1/1 0:00:00'AND '2017/6/22 0:00:00' 
		AND ( c.STATUE = '已验收' OR c.STATUE = '已发货')AND d.item_id = '3789'
	AND c.type = '总仓配送'AND c.id = d.MAIN_ID AND c.BILLNO = 'SG2017030800022'

结果


amt 数据多了好多小数。


那么哪位大神能告诉下小弟，，这个是为啥？？？

[ACMAIN_CHM]

d.PRICE * d.FHCNT 返回浮点小数。

[zjcxc]

union 的两个查询的字段数据类型不一致，所以最终的结果会根据两种类型算出一个能够容纳两种数据的类型，所以多小数位正常

[遁去得一]

引用 2 楼 zjcxc 的回复:

union 的两个查询的字段数据类型不一致，所以最终的结果会根据两种类型算出一个能够容纳两种数据的类型，所以多小数位正常

2个数据得字段和类型都是一致得！

[遁去得一]

引用 1 楼 ACMAIN_CHM 的回复:

d.PRICE * d.FHCNT 返回浮点小数。

返回得浮点小数是没问题哈，类型本来也都是浮点小数 2个浮点小数得结果相加，数据会多出来？或者减少？