赋值语句报错

Question

语句：
SET $FNeedDay=$T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$FNeedDay=$T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC)' at line 22

Answer 1

没有你说的这种问题。mysql> delimiter //
mysql> CREATE PROCEDURE p_lacasadeco ()
    -> BEGIN
    ->  declare $FNeedDay,$T_FAllAcount,$T_FWasteHour,$T_FAverAmount,$T_FMachineQty,$T_FBC int default 1;
    ->  SET $FNeedDay=$T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);
    ->  select $FNeedDay;
    -> END;
    -> //
Query OK, 0 rows affected (0.27 sec)mysql> delimiter ;
mysql> call p_lacasadeco();
+-----------+
| $FNeedDay |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>

Answer 2

DELIMITER $$DROP PROCEDURE IF EXISTS `wincodb`.`P_GetBosCombinedThroughput`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `P_GetBosCombinedThroughput`($M_FOrder INT,$M_FClassID INT)
BEGIN
DECLARE $T_FAllAcount,$T_FPlanAcount DECIMAL(28,10);
DECLARE $i,$j int;
DECLARE $T_FNote1 VARCHAR(128);
DECLARE $T_FNote2 VARCHAR(256);
DECLARE $T_FNote3 VARCHAR(128);
DECLARE $T_FWasteHour,$FNeedDay DECIMAL(10,2);
DECLARE $T_FAverAmount,$T_FMachineQty,$T_FBC INT;
DECLARE $T_FGxName,$T_FJtName VARCHAR(45);
DROP TABLE IF EXISTS PMC_GetThroughput;
CREATE TABLE PMC_GetThroughput(
   FOrder INT ,
   FClassID INT DEFAULT 0,
   FNote1 VARCHAR(128),
   FNote2 VARCHAR(256),
   FNote3 VARCHAR(128)
)ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
SET $T_FAllAcount=0;
SET $T_FPlanAcount=0;
SET $i=0;
SET $j=0;
IF $M_FOrder!=0 THEN
  SELECT FWasteHour,FAverAmount,FMachineQty,FBC,FGxName,FJtName
  INTO $T_FWasteHour,$T_FAverAmount,$T_FMachineQty,$T_FBC,$T_FGxName,$T_FJtName
  FROM WincoBosCombined_Output
  WHERE(FOrder=$M_FOrder)AND(FClassID=$M_FClassID); 
  #总体情况
  SELECT SUM(FBPSL-FJGSL),COUNT(FWorkNo)
  INTO $T_FAllAcount,$i
  FROM WincoBosCombinedPmcEntry
  WHERE(FOrder=$M_FOrder)AND(FMethodID!=2)AND(FClassID=$M_FClassID) 
  GROUP BY FOrder,FClassID;
  SET $T_FNote1=CONCAT('當前機臺為',$T_FJtName);
　SET $FNeedDay=$T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);
  IF($T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC))>4 THEN
    SET $T_FNote1=CONCAT($T_FNote1,'，系統完成所有訂單需','完成目前狀態為忙');
  END IF;
  SELECT SUM(FBPSL-FJGSL),COUNT(FWorkNo)
  INTO $T_FPlanAcount,$j
  FROM WincoBosCombinedPmcEntry
  WHERE(FOrder=$M_FOrder)AND(FMethodID!=2)AND(FClassID=$M_FClassID)AND(FisPlan=1) 
  GROUP BY FOrder,FClassID,FisPlan;  
  INSERT INTO PMC_GetThroughput
  VALUES($M_FOrder,$M_FClassID,$T_FNote1,$T_FNote2,$T_FNote3);
END IF;
SELECT * FROM PMC_GetThroughput;
DROP TABLE PMC_GetThroughput;
END$$DELIMITER ;

Answer 3

　SET $FNeedDay=$T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);
把你前面这个所谓的空格删除就行了。你放了一个中文全角的空格！

Answer 4

变量$FNeedDay修改为：
DECLARE $FNeedDay INT
没有问题了，但当对
SET $FNeedDay=$T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC); 
赋值时，如果右边等于0.15即小于0.5时，$FNeedDay＝0，有没有办法让它小数点位小于0.5时也向上入一位

Answer 5

值时，如果右边等于0.15即小于0.5时，$FNeedDay＝0，有没有办法让它小数点位小于0.5时也向上入一位:是1？
select case when mid(0.15,locate('.',0.15)+1,1)>0 then 1 else 0 end

Answer 6

mysql> select CEIL(1),CEIL(0.5),CEIL(0.15);
+---------+-----------+------------+
| CEIL(1) | CEIL(0.5) | CEIL(0.15) |
+---------+-----------+------------+
|       1 |         1 |          1 |
+---------+-----------+------------+
1 row in set (0.00 sec)mysql>

Answer 7

select case when mid(0.15,locate('.',0.15)+1,1)>0 then 1 else 0 end
将0.15修改成变量　OR　字段名ORCEILING(X), CEIL(X) Returns the smallest integer value not less than X. CEILING() and CEIL() are synonymous. mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEIL(-1.23);
        -> -1