sqlserver 暫存資料表應用

1. 有表P如下：

 

問題：求表中紅色多於藍色的商品名稱和差額數量

    （1）首先分成兩個暫存資料表

use tempdb

go
if object_id('#table1','U')is not null //判斷表是不是存在
   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='紅色' group by productNameselect productName, sum(num) as num into #table2 from P where color='藍色' group by productName

    （2）從兩個表中找出所要的值

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