sqlserver 临时表应用

时间：2011-05-04 来源：爱测试的猫咪

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 productName
select 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

相关阅读更多 +