Mysql游标获取orderbylimit1结果不是selec出来的结果

database

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_

变量不可以写到order by 里面

 

以上是 Mysql游标获取orderbylimit1结果不是selec出来的结果 的全部内容, 来源链接: utcz.com/z/533180.html

回到顶部