Mysql查询sum整数_mysql select sum with group by给出整数结果

我有两张桌子:

CREATE TABLE `product_det` (

`id` bigint(12) unsigned NOT NULL AUTO_INCREMENT,

`prod_name` varchar(64) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `product_det` (`id`,`prod_name`) VALUES

(1,'Pepper'),

(2,'Salt'),

(3,'Sugar');

CREATE TABLE `product_oper` (

`id` bigint(12) unsigned NOT NULL AUTO_INCREMENT,

`prod_id` bigint(12) unsigned NOT NULL,

`prod_quant` decimal(16,4) NOT NULL DEFAULT '1.0000',

`prod_value` decimal(18,2) NOT NULL DEFAULT '0.00',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `product_oper` (`id`,`prod_id`,`prod_quant`,`prod_value`) VALUES

(12,2,'3.0000','26.14'),

(13,2,'0.0450','26.23'),

(14,2,'0.0300','26.14'),

(10,1,'0.0600','13.20'),

(11,1,'0.0600','13.20');

我想找出每种产品的数量和价值加在第二个表中的价值。

我的疑问是:

SELECT product_det.*, SUM(in_out.p_q) as q, SUM(in_out.p_val) AS val

FROM

(SELECT product_oper.prod_id as p_id, SUM(product_oper.prod_quant) as p_q, SUM(product_oper.prod_quant*product_oper.prod_value) as p_val

FROM product_oper

GROUP BY product_oper.prod_id

) AS in_out

LEFT JOIN product_det ON in_out.p_id=product_det.id

GROUP BY in_out.p_id

HAVING q<>0.00 ORDER BY val;

我得到的结果是:

id, prod_name, q, val

1, 'Pepper', 0.1200, 2

2, 'Salt', 3.0750, 80

这是错误的,列中的值

val

是整数,不应该是。

但是,内部选择将给出下一个结果:

p_id, p_q, p_val

1, 0.1200, 1.584000

2, 3.0750, 80.384550

问题是:为什么我得到

瓦尔

当我从子查询中选择时?为什么

q

不是整数?

我需要使用子查询,因为我原来的查询是这样的:

SELECT ... FROM (SELECT ... UNION SELECT ...) AS in_out GROUP BY in_out.p_id

我有mysql版本5.1.39