Mysql 游标获取order by limit 1 结果不是selec 出来的结果

Mysql 游标获取order by limit 1 结果不是selec 出来的结果 2015-04-11 12:04:13 559人浏览才女

摘要

SELECT location_no, lot_batch_no, qty_onhand, qty_reserved,

SELECT location_no,
                lot_batch_no,
                qty_onhand,
                qty_reserved,
                id,
                receipt_date,
                product_date,
                expiry_date,
                pos_x
          FROM (
                 SELECT  s.location_no                 location_no, 
                         s.lot_batch_no                lot_batch_no,
                         s.qty_onhand                  qty_onhand, 
                         s.qty_reserved                qty_reserved,
                         s.id                          id,
                         s.receipt_date                receipt_date,
                         s.product_date                product_date,
                         s.expiry_date                 expiry_date,
                         m.pos_x                       pos_x
                 FROM    bay_list b, inventory_location m, inventory_part_in_stock s
                 WHERE   b.logistics_company_id = m.logistics_company_id
                 AND     b.bay_no = m.bay_no
                 AND     b.warehouse = m.warehouse
                 AND     m.lock_inventory = 0    -- 未锁定
                 AND     (b.bay_type = 1 OR 1 IS NULL) 
                 AND     m.location_no != "JHKW" -- JHKW为虚拟库位,不可以拣
                 AND     m.logistics_company_id = s.logistics_company_id
                 AND     m.warehouse = s.warehouse
                 AND     m.location_no = s.location_no
                 AND     s.qty_reserved >= 0
                 AND     s.qty_onhand - s.qty_reserved > 0
                 AND     s.part_no = "2047222"
                 AND     s.loGIStics_company_id = 10003
                 AND     s.warehouse = "GLP"
                 AND     s.owner_id = "CRM-YC"
                 AND     b.pick_type != 3             -- 拣货类型 (1. 零散拣货   ; 2.批量拣货   ; 3. 预包拣货 )
                 AND     s.lot_batch_no LIKE CONCAT("%", "@NRM@NRM", "%")  -- @contrl_state@qulity_state                
                   ) k
                 ORDER BY product_date
                 LIMIT 1)

做了order by 第一个出来的时生产日期最旧的,

但是跑存储过程的时候select 出来的缺不是这样的存储过程里面

DELIMITER $$

USE `zita_zwy_wms`$$

DROP PROCEDURE IF EXISTS `InventoryPartInStockUtil_FindAndResvWithLot`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `InventoryPartInStockUtil_FindAndResvWithLot`(  
             OUT qty_to_resv_              INT,
             OUT location_no_              VARCHAR(30),
             OUT lot_batch_no_             VARCHAR(30),
             IN company_id_                INT, 
             IN warehouse_                 VARCHAR(20),
             IN owner_id_                  VARCHAR(20),
             IN part_no_                   VARCHAR(30),
             IN qty_remain_                INT,
             IN location_type_             INT,              -- 库位类型.1.拣货区,2.存储区, 3: 次品区; 4: 待上架; 5: 退货区; 6: 分拣区
             IN user_id_                   VARCHAR(40),
             IN activity_id_               INT,
             IN pick_type_                 INT,
             IN part_lot_batch_no_         VARCHAR(20))
BEGIN 
   DECLARE id_                   INT;
   DECLARE qty_onhand_           INT;
   DECLARE qty_reserved_         INT;
   DECLARE order_by_             VARCHAR(2000);
   DECLARE receipt_date_         DATETIME;
   DECLARE product_date_         DATETIME;
   DECLARE expiry_date_          DATETIME;
   DECLARE pos_x_                INT;
   
   IF ((nvl(activity_id_ , 0) = 0) OR (pick_type_ != 3)) THEN   -- 拣货类型(1汇总拣货,2一单一品,3活动单拣货,4边分边拣, 5逐单拣货)
      BEGIN 
         -- 非活动订单的商品预留
         DECLARE get_inv_stk CURSOR FOR 
         SELECT location_no,
                lot_batch_no,
                qty_onhand,
                qty_reserved,
                id,
                receipt_date,
                product_date,
                expiry_date,
                pos_x
          FROM (
                 SELECT  s.location_no                 location_no, 
                         s.lot_batch_no                lot_batch_no,
                         s.qty_onhand                  qty_onhand, 
                         s.qty_reserved                qty_reserved,
                         s.id                          id,
                         s.receipt_date                receipt_date,
                         s.product_date                product_date,
                         s.expiry_date                 expiry_date,
                         m.pos_x                       pos_x
                 FROM    bay_list b, inventory_location m, inventory_part_in_stock s
                 WHERE   b.logistics_company_id = m.logistics_company_id
                 AND     b.bay_no = m.bay_no
                 AND     b.warehouse = m.warehouse
                 AND     m.lock_inventory = 0    -- 未锁定
                 AND     m.location_no != "JHKW" -- JHKW为虚拟库位,不可以拣货
                 AND     (b.bay_type = location_type_ OR location_type_ IS NULL) 
                 AND     m.logistics_company_id = s.logistics_company_id
                 AND     m.warehouse = s.warehouse
                 AND     m.location_no = s.location_no
                 AND     s.qty_reserved >= 0
                 AND     s.qty_onhand - s.qty_reserved > 0
                 AND     s.part_no = part_no_
                 AND     s.logistics_company_id = company_id_
                 AND     s.warehouse = warehouse_
                 AND     s.owner_id = owner_id_
                 AND     b.pick_type != 3             -- 拣货类型 (1. 零散拣货   ; 2.批量拣货   ; 3. 预包拣货 )
                 AND     s.lot_batch_no LIKE CONCAT("%", part_lot_batch_no_, "%")  -- @contrl_state@qulity_state 
                 ) k
                 ORDER BY order_by_
                 LIMIT 1; 
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET id_ = NULL;    
         
         SET   order_by_   := CompanyOwnerPriotiryUtil_GetConfig(company_id_, owner_id_);
         OPEN  get_inv_stk;
         FETCH get_inv_stk INTO location_no_, lot_batch_no_, qty_onhand_, qty_reserved_, id_, receipt_date_, product_date_, expiry_date_, pos_x_;
         CLOSE get_inv_stk;
         
         SELECT location_no_, lot_batch_no_, qty_onhand_, qty_reserved_, id_, receipt_date_, product_date_, expiry_date_, pos_x_;
      END;
   ELSE
      BEGIN 
        -- 活动订单的商品预留
        DECLARE get_inv_stk CURSOR FOR 
                SELECT  s.location_no                 location_no, 
                        s.lot_batch_no                lot_batch_no,
                        s.qty_onhand                  qty_onhand, 
                        s.qty_reserved                qty_reserved,
                        s.id                          id
                FROM    inventory_location m, inventory_part_in_stock s , activity_policy_loca a
                WHERE   a.logistics_company_id = s.logistics_company_id
                AND     a.warehouse = s.warehouse
                AND     a.owner_id = s.owner_id
                AND     m.logistics_company_id = s.logistics_company_id
                AND     m.warehouse = s.warehouse
                AND     m.lock_inventory = 0    -- 未锁定
                AND     m.location_no != "JHKW" -- JHKW为虚拟库位,不可以拣货
                AND     a.activity_id = activity_id_
                AND     s.qty_onhand - s.qty_reserved > 0
                AND     s.part_no = part_no_
                AND     s.logistics_company_id = company_id_
                AND     s.warehouse = warehouse_
                AND     s.owner_id = owner_id_
                AND     s.location_no = a.location_no
                AND     s.lot_batch_no LIKE CONCAT("%", part_lot_batch_no_, "%")  -- @contrl_state@qulity_state 
                ORDER BY s.lot_batch_no
                LIMIT 1; 
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET id_ = NULL;  
        
        OPEN  get_inv_stk;
        FETCH get_inv_stk INTO location_no_, lot_batch_no_, qty_onhand_, qty_reserved_, id_;
        CLOSE get_inv_stk;
      END;
   END IF; 
   
   SET qty_to_resv_ := 0;
   IF id_ > 0 THEN
      -- 锁定记录
      CALL InventoryPartInStockUtil_LockById( id_,
                                              Qty_onhand_,
                                              "InventoryPartInStockUtil_FindAndResvWithLot" );
      --   
      SET qty_to_resv_ := LEAST( qty_onhand_ - qty_reserved_ , qty_remain_ );
      
      UPDATE inventory_part_in_stock
             SET  qty_reserved = GREATEST(0, LEAST(qty_onhand_, qty_reserved_ + qty_to_resv_))
             WHERE id = id_;
   END IF;
END$$

DELIMITER ;

注意:

解决方案 1.

把order by 放到最里面去,结果正确

改了后还是不行

把变量放到 declare 这个游标前就可以了

SET @order_by_ := "s.product_date";
 SELECT  s.location_no                 location_no, 
                         s.lot_batch_no                lot_batch_no,
                         s.qty_onhand                  qty_onhand, 
                         s.qty_reserved                qty_reserved,
                         s.id                          id,
                         s.receipt_date                receipt_date,
                         s.product_date                product_date,
                         s.expiry_date                 expiry_date,
                         m.pos_x                       pos_x
                 FROM    bay_list b, inventory_location m, inventory_part_in_stock s
                 WHERE   b.logistics_company_id = m.logistics_company_id
                 AND     b.bay_no = m.bay_no
                 AND     b.warehouse = m.warehouse
                 AND     m.lock_inventory = 0    -- 未锁定
                 AND     m.location_no != "JHKW" -- JHKW为虚拟库位,不可以拣货
                 AND     (b.bay_type = 1 OR 1 IS NULL) 
                 AND     m.logistics_company_id = s.logistics_company_id
                 AND     m.warehouse = s.warehouse
                 AND     m.location_no = s.location_no
                 AND     s.qty_reserved >= 0
                 AND     s.qty_onhand - s.qty_reserved > 0
                 AND     s.part_no = "2047222"
                 AND     s.logistics_company_id = 10003
                 AND     s.warehouse = "GLP"
                 AND     s.owner_id = "CRM-YC"
                 AND     b.pick_type != 3             -- 拣货类型 (1. 零散拣货   ; 2.批量拣货   ; 3. 预包拣货 )
                 AND     s.lot_batch_no LIKE CONCAT("%", "@NRM@NRM", "%")  -- @contrl_state@qulity_state 
                 ORDER BY @order_by_