There is now a is a product inventory table, a table records for each product delivery, the number of shipments ship an order list, records need total number of product model and the corresponding products
Each order will be N times of shipment, the order table and delivery table record is 1: N relationship

Under normal circumstances, the total number of shipment delivery table and the requirements of the order table is equal to the total, but some special order delivery will be more than the number of order of total demand,

Demand in 1000, for example, shipped 1200, shipment of 200 for the first time, the second shipment of 500, the third time shipped 400, shipment of 100 for the fourth time, I now need to find out the third shipment time

I now is to use two cursors, the first cursor order cycle table, the second cursor loop shipment, but this query time is very long

Is there any way to optimize?

CodePudding user response:

Is the query list ORDER single goods shipment number greater than or equal to three ORDER shipping record for the third time shipment, it is in the shipping list with ROW_NUMBER () OVER (PARTITION BY ORDER ID, product model ID ORDER BY delivery time) to generate serial number, then take serial number=3 delivery record,