INSERT INTO Goods_Deal
SELECT *,Vol=a.Price-b.Price 
FROM Goods_Order a WHERE EXISTS (SELECT 1 FROM Goods_PriceReport b WHERE a.Price>=b.Price)
DELETE Goods_Order WHERE id IN  (SELECT id FROM Goods_Deal )

------解决方案--------------------

SQL code

--> 测试数据：[Goods_PriceReport]
if object_id('[Goods_PriceReport]') is not null drop table [Goods_PriceReport]
create table [Goods_PriceReport]([Id] int,[Stuff] varchar(4),[Price] numeric(2,1))
insert [Goods_PriceReport]
select 4,'白菜',3.2 union all
select 2,'番茄',4.9 union all
select 1,'黄瓜',2.3 union all
select 5,'辣椒',5.9union all
select 3,'芹菜',1.2 union all
select 6,'土豆',7.9
--> 测试数据：[Goods_Order]
if object_id('[Goods_Order]') is not null drop table [Goods_Order]
create table [Goods_Order]([Id] int,[Stuff] varchar(4),[Price] numeric(2,1))
insert [Goods_Order]
select 4,'白菜',3.2 union all
select 2,'番茄',5.6 union all
select 1,'黄瓜',2.3 union all
select 5,'辣椒',4.5 union all
select 3,'芹菜',1.2 union all
select 6,'土豆',9.8
--> 测试数据：[Goods_Deal]
if object_id('[Goods_Deal]') is not null drop table [Goods_Deal]
create table [Goods_Deal](
[Id] int,
[Stuff] varchar(10),
[Price] float,
[Vol] varchar(10)
)
--插入:
insert [Goods_Deal](Id,[Stuff],Price)
select a.*from [Goods_PriceReport] a 
inner join [Goods_Order] b on a.Id=b.Id and a.[Stuff]=b.[Stuff]
where a.Price>=b.Price

select * from [Goods_Deal]
/*
Id    Stuff    Price    Vol
4    白菜    3.2    NULL
1    黄瓜    2.3    NULL
5    辣椒    5.9    NULL
3    芹菜    1.2    NULL
*/
--删除:
delete from [Goods_Order] where Id in(select Id from(
select a.*from [Goods_PriceReport] a 
inner join [Goods_Order] b on a.Id=b.Id and a.[Stuff]=b.[Stuff]
where a.Price>=b.Price )a
)
select * from [Goods_Order]
/*
Id    Stuff    Price
2    番茄    5.6
6    土豆    9.8
*/


改了你的测试数据，price都是一样的，看不出效果，我改了一下