视图中计算double类型数据得到错误的结果，该怎么办？

ALTER
ALGORITHM=UNDEFINED
DEFINER=`root`@`localhost`
SQL SECURITY INVOKER
VIEW `show_data` AS
SELECT
stock_data.get_time AS get_time,
stock_data.real_time AS real_time,
Sum(if((`stock_data`.`stock_code` = 883300),format(((`stock_data`.`bigbuycount` - `stock_data`.`bigsellcount`) / 1000000),1),NULL)) AS data1,
Sum(if((`stock_data`.`stock_code` = 883300),format(((`stock_data`.`waitbuycount` - `stock_data`.`waitsellcount`) / 1000000),1),NULL)) AS data2,
Sum(if((`stock_data`.`stock_code` = 883300),format(((`stock_data`.`bigbuycount1` - `stock_data`.`bigsellcount1`) / 1000000),1),NULL)) AS data3,
Sum(if((`stock_data`.`stock_code` = 881155),format(((`stock_data`.`bigbuycount` - `stock_data`.`bigsellcount`) / 1000000),1),NULL)) AS data4,
Sum(if((`stock_data`.`stock_code` = 881155),format(((`stock_data`.`waitbuycount` - `stock_data`.`waitsellcount`) / 1000000),1),NULL)) AS data5,
Sum(if((`stock_data`.`stock_code` = 881155),format(((`stock_data`.`bigbuycount1` - `stock_data`.`bigsellcount1`) / 1000000),1),NULL)) AS data6,
Sum(if((`stock_data`.`stock_code` = 881153),format(((`stock_data`.`bigbuycount` - `stock_data`.`bigsellcount`) / 1000000),1),NULL)) AS data7,
Sum(if((`stock_data`.`stock_code` = 881153),format(((`stock_data`.`waitbuycount` - `stock_data`.`waitsellcount`) / 1000000),1),NULL)) AS data8,
Sum(if((`stock_data`.`stock_code` = 881153),format(((`stock_data`.`bigbuycount1` - `stock_data`.`bigsellcount1`) / 1000000),1),NULL)) AS data9,
Sum(if((`stock_data`.`stock_code` = 881145),format(((`stock_data`.`bigbuycount` - `stock_data`.`bigsellcount`) / 1000000),1),NULL)) AS data10,
Sum(if((`stock_data`.`stock_code` = 881145),format(((`stock_data`.`waitbuycount` - `stock_data`.`waitsellcount`) / 1000000),1),NULL)) AS data11,
Sum(if((`stock_data`.`stock_code` = 881145),format(((`stock_data`.`bigbuycount1` - `stock_data`.`bigsellcount1`) / 1000000),1),NULL)) AS data12,
Sum(if((`stock_data`.`stock_code` = 881105),format(((`stock_data`.`bigbuycount` - `stock_data`.`bigsellcount`) / 1000000),1),NULL)) AS data13,
Sum(if((`stock_data`.`stock_code` = 881105),format(((`stock_data`.`waitbuycount` - `stock_data`.`waitsellcount`) / 1000000),1),NULL)) AS data14,
Sum(if((`stock_data`.`stock_code` = 881105),format(((`stock_data`.`bigbuycount1` - `stock_data`.`bigsellcount1`) / 1000000),1),NULL)) AS data15,
Sum(if((`stock_data`.`stock_code` = 881156),format(((`stock_data`.`bigbuycount` - `stock_data`.`bigsellcount`) / 1000000),1),NULL)) AS data16,
Sum(if((`stock_data`.`stock_code` = 881156),format(((`stock_data`.`waitbuycount` - `stock_data`.`waitsellcount`) / 1000000),1),NULL)) AS data17,
Sum(if((`stock_data`.`stock_code` = 881156),format(((`stock_data`.`bigbuycount1` - `stock_data`.`bigsellcount1`) / 1000000),1),NULL)) AS data18,
Sum(if((`stock_data`.`stock_code` = 881157),format(((`stock_data`.`bigbuycount` - `stock_data`.`bigsellcount`) / 1000000),1),NULL)) AS data19,
Sum(if((`stock_data`.`stock_code` = 881157),format(((`stock_data`.`waitbuycount` - `stock_data`.`waitsellcount`) / 1000000),1),NULL)) AS data20,
Sum(if((`stock_data`.`stock_code` = 881157),format(((`stock_data`.`bigbuycount1` - `stock_data`.`bigsellcount1`) / 1000000),1),NULL)) AS data21,
Sum(if((`stock_data`.`stock_code` = 881113),format(((`stock_data`.`bigbuycount` - `stock_data`.`bigsellcount`) / 1000000),1),NULL)) AS data22,
Sum(if((`stock_data`.`stock_code` = 881113),format(((`stock_data`.`waitbuycount` - `stock_data`.`waitsellcount`) / 1000000),1),NULL)) AS data23,
Sum(if((`stock_data`.`stock_code` = 881113),format(((`stock_data`.`bigbuycount1` - `stock_data`.`bigsellcount1`) / 1000000),1),NULL)) AS data24,
Sum(if((`stock_data`.`stock_code` = 881112),format(((`stock_data`.`bigbuycount` - `stock_data`.`bigsellcount`) / 1000000),1),NULL)) AS data25,
Sum(if((`stock_data`.`stock_code` = 881112),format(((`stock_data`.`waitbuycount` - `stock_data`.`waitsellcount`) / 1000000),1),NULL)) AS data26,
Sum(if((`stock_data`.`stock_code` = 881112),format(((`stock_data`.`bigbuycount1` - `stock_data`.`bigsellcount1`) / 1000000),1),NULL)) AS data27
from `stock_data`
group by `stock_data`.`get_time`,`stock_data`.`real_time` ;
以上是视图创建代码
SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for `stock_data`
-- ----------------------------
DROP TABLE IF EXISTS `stock_data`;
CREATE TABLE `stock_data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stock_code` varchar(6) NOT NULL,
  `real_time` time NOT NULL,
  `bigbuycount1` double(16,2) unsigned NOT NULL,
  `bigsellcount1` double(16,2) unsigned NOT NULL,
  `bigbuycount` double(16,2) unsigned NOT NULL,
  `bigsellcount` double(16,2) unsigned NOT NULL,
  `waitbuycount` double(16,2) unsigned NOT NULL,
  `waitsellcount` double(16,2) unsigned NOT NULL,
  `stock_price` double(16,2) unsigned NOT NULL,
  `turnover` double(16,2) unsigned NOT NULL,
  `get_time` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3436493660 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of stock_data
-- ----------------------------
INSERT INTO `stock_data` VALUES ('3436493639', '883300', '14:40:00', '6256880500.00', '7328822100.00', '4217294300.00', '4250102600.00', '4189125700.00', '4321254200.00', '2945626.00', '43734509000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493640', '883300', '14:41:00', '6266530800.00', '7346851700.00', '4228331000.00', '4257823100.00', '4197025900.00', '4332743400.00', '2947415.00', '43847442000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493641', '883300', '14:42:00', '6288701500.00', '7363596600.00', '4245374200.00', '4267818900.00', '4206532400.00', '4350427500.00', '2953137.00', '44016679000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493642', '883300', '14:43:00', '6306625000.00', '7382366700.00', '4259443700.00', '4276742500.00', '4213293100.00', '4363978400.00', '2955936.00', '44104408000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493643', '883300', '14:44:00', '6330704000.00', '7400667800.00', '4279797300.00', '4290358700.00', '4225066500.00', '4383881800.00', '2960096.00', '44254664000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493644', '883300', '14:45:00', '6345751500.00', '7422648900.00', '4292329600.00', '4297372300.00', '4234288800.00', '4393968400.00', '2962239.00', '44358527000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493645', '883300', '14:46:00', '6365550600.00', '7432018700.00', '4305914900.00', '4308719100.00', '4244917100.00', '4405395200.00', '2963302.00', '44505285000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493646', '883300', '14:47:00', '6381997500.00', '7443129600.00', '4322679300.00', '4319059300.00', '4255628900.00', '4420404000.00', '2962679.00', '44613592000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493647', '883300', '14:48:00', '6397561200.00', '7457954800.00', '4334288400.00', '4331437100.00', '4268134400.00', '4432019900.00', '2961375.00', '44747472000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493648', '883300', '14:49:00', '6414819700.00', '7481067800.00', '4349099200.00', '4345506700.00', '4281754100.00', '4444838500.00', '2959826.00', '44852274000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493649', '883300', '14:50:00', '6427814200.00', '7500533800.00', '4359987100.00', '4356386800.00', '4294020900.00', '4455382300.00', '2959531.00', '44956346000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493650', '883300', '14:51:00', '6449930800.00', '7524579100.00', '4378580700.00', '4371752700.00', '4309648800.00', '4473953300.00', '2959516.00', '45123153000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493651', '883300', '14:52:00', '6466833900.00', '7541680300.00', '4390843800.00', '4383010700.00', '4320771700.00', '4486624800.00', '2960610.00', '45226706000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493652', '883300', '14:53:00', '6490039900.00', '7571898300.00', '4408809900.00', '4397101500.00', '4337484500.00', '4503286200.00', '2961879.00', '45435458000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493653', '883300', '14:54:00', '6522362100.00', '7594232000.00', '4427600800.00', '4413003200.00', '4354754700.00', '4521456400.00', '2963040.00', '45641903000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493654', '883300', '14:55:00', '6554154300.00', '7614546700.00', '4447886600.00', '4429459100.00', '4370885700.00', '4542041000.00', '2963664.00', '45789705000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493655', '883300', '14:56:00', '6576333600.00', '7635555000.00', '4465873000.00', '4443738100.00', '4385488400.00', '4556556800.00', '2965108.00', '46014858000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493656', '883300', '14:57:00', '6598116700.00', '7658238100.00', '4482283400.00', '4456504900.00', '4399341000.00', '4571529600.00', '2965940.00', '46147330000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493657', '883300', '14:58:00', '6626769700.00', '7678305200.00', '4501378400.00', '4472869800.00', '4414356900.00', '4591553200.00', '2967011.00', '46327742000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493658', '883300', '14:59:00', '6643306600.00', '7684412400.00', '4508708100.00', '4479516200.00', '4419126800.00', '4600858300.00', '2967460.00', '46445759000.00', '2014-12-04');
INSERT INTO `stock_data` VALUES ('3436493659', '883300', '15:00:00', '6651246900.00', '7689741600.00', '4517086300.00', '4483185500.00', '4423522200.00', '4608628200.00', '2967549.00', '46615668000.00', '2014-12-04');
DROP TRIGGER IF EXISTS `count_num`;
DELIMITER ;;
CREATE TRIGGER `count_num` AFTER INSERT ON `stock_data` FOR EACH ROW BEGIN
if not exists(select * from get_count where stock_code = new.stock_code) then
insert into get_count values(new.stock_code,1,new.get_time);
elseif exists(select * from get_count where get_time = new.get_time and stock_code = new.stock_code) then
update get_count set cur_num = cur_num + 1 where stock_code=new.stock_code and get_time = new.get_time;
else
update get_count set cur_num = 1,get_time = new.get_time where stock_code=new.stock_code;
end if;
END
;;
DELIMITER ; 以上是stock_data 以及测试数据

调试易

视图中计算double类型数据得到错误的结果，该怎么办？

解决方案 »