SQL Server temporary table Application

1. Table P is as follows:

Question: Calculate the product name and the difference quantity in red and blue in the table.

(1) first, it is divided into two temporary tables.

Use tempdb

Go
If object_id ('# Table1', 'U') is not null // you can check whether a table exists.
Drop table # Table1
Go
If object_id ('# Table2', 'U') is not null
Drop table # Table2
Go

Use admin1

Select productname, sum (Num) as num into # Table1 from P where color = 'red' group by productnameselect productname, sum (Num) as num into # Table2 from P where color = 'blue' group by productname

(2) Find the expected values from the two tables.

Select # table1.productname, # table1.num as numh, # table2.num as numl, (# table1.num-# table2.num) as margin from # Table1, # Table2 where # table1.num> # table2.num and # table1.productname = # table2.productname