SQL simple difference (subtraction, inventory) Calculation

A few days ago, the inventory should be displayed when the school made a product release. The inventory is calculated by the difference between the incoming and outgoing goods. The main fields in the record table of incoming goods are as follows: PID is the product ID, num is the quantity, state is the status, and 0 is the incoming value, 1 is output.

PID num state1 100 02 50 03 150 02 5 11 10 1

SQL Implementation Method

Select PID, sum (case state when 0 then num else-num end) as num from recordgroup by PID

To connect to the product table, obtain the product name.

PID pname1 small A2 small B3 small C

Cascade

 

Select max (pname), record. PID, sum (case state when 0 then num else-num end) as num from record inner join product on record. PID = product. pidgroup by record. PID