Table 1
Destination item order_no quantity
Shanghai 001 1000
Shanghai 002 700
Shanghai 002 300 B
Nanjing A 001 500
Nanjing A 002 1000
Hangzhou C 002 1200
Ningbo D 001 200

Table 2
Destination item truck_no quantity
Shanghai A T1 1500
Shanghai A T2 200
Shanghai B T2 300
Nanjing A T3 1500
Hangzhou C T4 500
Hangzhou C T5 700
Ningbo D T6 200

Table 1 is a list of orders, destination destination, item commodity name, order_no order no., quantity orders
A list of table 2 are processed, table 1 is to arrange the corresponding freight table, at the time of processing is blanked out order_no of this field, truck_no truck number
Now you need to get table 3 according to table 1 and table 2, which is to add order_no give back, troublesome everybody see what good method? First thank you,

Table 3
Destination item order_no truck_no quantity
Shanghai 001 T1 1000
Shanghai 002 T1 500
Shanghai 002 T2 200
Shanghai B 002 T2 300
Nanjing A 001 T3 500
Nanjing A 002 T3 1000
Hangzhou C 002 T4 500
Hangzhou C 002 T5 700
Ningbo D 001 T6 200

With table 1 and table 2 SQL
WITH tab1 (destination, item, order_no, quantity) AS (
Select the 'Shanghai', 'A', '001', 1000 union all
Select the 'Shanghai', 'A', '002', 700 union all
Select the 'Shanghai', 'B', '002', 300 union all
Select 'nanjing', 'A', '001', 500 union all
Select 'nanjing', 'A', '002', 1000 union ALL
Select 'hangzhou', 'C', '002', 1200 union ALL
Select 'ningbo', 'D', '001', 200
)
Truck_no, tab2 (destination, item, quantity) AS (
Select the 'Shanghai', 'A', 'T1' 1500 union all
Select the 'Shanghai', 'A', 'T2' 200 union all
Select the 'Shanghai', 'B', 'T2' 300 union all
Select 'nanjing', 'A', 'T3, 1500 union all
Select 'hangzhou', 'C', 'T4, 500 union ALL
Select 'hangzhou', 'C', '0', 700 union ALL
Select 'ningbo', 'D', 'T6, 200
)

CodePudding user response:

The building Lord give an example of a little puzzling, such as the data in table 2

Hangzhou C T4 500
Hangzhou C T5 700

This truck has two T4 and T5, so you have a little problem, so the rows of data in table 1

Hangzhou C 002 1200

Back to fill in for which truck number? The same data in table 3

Hangzhou C 002 T4 500
Hangzhou C 002 T5 700

You may need to be said about the truck line of car number logic,
With truck, in a similar statement subquery,

 
Select tab1. Destination, tab1. Item, tab1, order_no, tab1. Quantity, 
(select top 1 truck_no from tab2 where tab1. Destination=tab2. Destination and tab1. Item=tab2. Item) truck_no 
The from tab1 

How to get a row of truck number to fill in T4, and another line with T5?

CodePudding user response:

reference 1st floor lich2005 response:

the building Lord give an example of a little puzzling, such as the data in table 2

Hangzhou C T4 500
Hangzhou C T5 700

This truck has two T4 and T5, so you have a little problem, so the rows of data in table 1

Hangzhou C 002 1200

Back to fill in for which truck number? The same data in table 3

Hangzhou C 002 T4 500
Hangzhou C 002 T5 700

You may need to be said about the truck line of car number logic,
With truck, in a similar statement subquery,

 
Select tab1. Destination, tab1. Item, tab1, order_no, tab1. Quantity, 
(select top 1 truck_no from tab2 where tab1. Destination=tab2. Destination and tab1. Item=tab2. Item) truck_no 
The from tab1 

How to get a row of truck number to fill in T4, and another line with T5?

Sorry, I face in my example described in table 3 is less, I'm here to add that the

Table 3
Destination item order_no truck_no quantity
According to table 2, can know van T1, sent to Shanghai, can put A 1500 A, again according to the information in table 1, the priority assigned to the order 001100 0 A
Shanghai 001 T1 1000
Order 001 has been allocated 1000 A, the rest of 500, was assigned to the order 002
Shanghai 002 T1 500
According to table 2, can know van T2, sent to Shanghai, can put A 200 A, and then based on the information of table 1, assigned to the order of 002200 A
Shanghai 002 T2 200
According to table 2, can know van T2, sent to Shanghai, can put a, 300 B, and then based on the information of table 1, assigned to the order of 002300 B
Shanghai B 002 T2 300
According to table 2, can know van T3, sent to nanjing, can put A 1500 A, and then based on the information of table 1, the priority assigned to the order of 001500 A, then assigned to order 002100 0 A
Nanjing A 001 T3 500
Nanjing A 002 T3 1000
According to table 2 can know, van T4, sent to hangzhou, can put 500 C, and then based on the information of table 1, table 1 only order 002. As a result, assigned to the order of 002500 C
nullnullnullnullnullnullnullnullnullnull