SQL一个UPDATE问题

Question

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2013-11-23 14:49:30
-- Verstion:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
-- Feb 10 2012 19:39:15 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[库存表]
if object_id('[库存表]') is not null drop table [库存表]
go 
create table [库存表]([产品编号] varchar(3),[库存量] int)
insert [库存表]
select 'A01',100 union all
select 'A02',50  --> 测试数据：[订单需求表]
if object_id('[订单需求表]') is not null drop table [订单需求表]
go 
create table [订单需求表]([订单号] varchar(4),[产品编号] varchar(3),[订单需求] int,[使用库存] varchar(2),[缺少] varchar(2))
insert [订单需求表]
select 'PO01','A01',500,'XX','XX' union all
select 'PO02','A01',100,'XX','XX'
--------------开始查询--------------------------
;WITH f AS 
(
SELECT id=ROW_NUMBER()OVER(PARTITION BY 产品编号 ORDER BY GETDATE()),* FROM 订单需求表
),
f1 AS
(
SELECT 
id,订单号,产品编号,订单需求=(SELECT SUM(订单需求) AS 订单需求 FROM f WHERE 产品编号=a.产品编号 AND id<=a.id),使用库存,缺少
FROM 
[f] a
)SELECT
    b.订单号 ,
    b.产品编号 ,
    b.订单需求
,CASE WHEN a.库存量<=b.订单需求 AND b.id=1 THEN a.库存量 
      WHEN a.库存量>b.订单需求 THEN b.订单需求  ELSE 0 END AS 使用库存
,CASE WHEN a.库存量<=b.订单需求 AND b.id=1 THEN a.库存量-b.[订单需求] ELSE 0 END AS 缺少
FROM
    库存表 a
INNER JOIN f1 AS b ON a.产品编号=b.产品编号
----------------结果----------------------------
/* 订单号  产品编号 订单需求        使用库存        缺少
---- ---- ----------- ----------- -----------
PO01 A01  500         100         -400
PO02 A01  600         0           0(2 行受影响)
*/

Answer 1

--修改下：----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2013-11-23 14:49:30
-- Verstion:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
-- Feb 10 2012 19:39:15 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[库存表]
if object_id('[库存表]') is not null drop table [库存表]
go 
create table [库存表]([产品编号] varchar(3),[库存量] int)
insert [库存表]
select 'A01',100 union all
select 'A02',50  --> 测试数据：[订单需求表]
if object_id('[订单需求表]') is not null drop table [订单需求表]
go 
create table [订单需求表]([订单号] varchar(4),[产品编号] varchar(3),[订单需求] int,[使用库存] varchar(2),[缺少] varchar(2))
insert [订单需求表]
select 'PO01','A01',500,'XX','XX' union all
select 'PO02','A01',100,'XX','XX'
--------------开始查询--------------------------
;WITH f AS 
(
SELECT id=ROW_NUMBER()OVER(PARTITION BY 产品编号 ORDER BY GETDATE()),* FROM 订单需求表
),
f1 AS
(
SELECT 
id,订单号,产品编号,订单需求 AS  订单需求1,订单需求=(SELECT SUM(订单需求) AS 订单需求 FROM f WHERE 产品编号=a.产品编号 AND id<=a.id),使用库存,缺少
FROM 
[f] a
)SELECT
    b.订单号 ,
    b.产品编号 ,
    b.订单需求1 as 订单需求
,CASE WHEN a.库存量<=b.订单需求 AND b.id=1 THEN a.库存量 
      WHEN a.库存量>b.订单需求 THEN b.订单需求1  ELSE 0 END AS 使用库存
,CASE WHEN a.库存量<=b.订单需求 AND b.id=1 THEN a.库存量-b.[订单需求] 
  WHEN a.库存量>b.订单需求 THEN 0 ELSE 订单需求1 END AS 缺少
FROM
    库存表 a
INNER JOIN f1 AS b ON a.产品编号=b.产品编号
----------------结果----------------------------
/*订单号  产品编号 订单需求        使用库存        缺少
---- ---- ----------- ----------- -----------
PO01 A01  500         100         -400
PO02 A01  100         0           100(2 行受影响)*/

Answer 2

if object_id('tempdb.dbo.#stock') is not null drop table #stock
if object_id('tempdb..#order') is not null drop table #order
go
create table #stock (productno varchar(10),stock int)
create table #order(orderNo varchar(10),productNo varchar(10),orderQty int,usedStock int,lessqty int)
insert into #stock
select 'A01',100 union 
select 'A02',50
insert #order(orderNo,productNo,orderQty)
select 'P001','A01',500 union
select 'P002','A01',100
;with sel as(
select orderno,productNo,orderQty,row_number() over(partition by productno order by getdate()) rn
from #order
)
----select * from sel
,sel2 as(
select o.orderno,o.productno,o.orderqty,case when s.stock>=o.orderqty then  o.orderqty else s.stock end as usedstock,
case when s.stock>=o.orderqty then s.stock-o.orderqty else 0 end as stock,o.rn
from sel o join #stock s
on o.productno=s.productno
where o.rn=1
union all
select o2.orderno,o2.productno,o2.orderqty,case when sel2.stock>=o2.orderqty then o2.orderqty else sel2.stock end as usedstock,
case when sel2.stock>=o2.orderqty then sel2.stock-o2.orderqty else 0 end as stock,o2.rn
from sel2 join sel o2 on sel2.productno=o2.productno and sel2.rn+1=o2.rn
)
select orderno,productno,orderqty,usedstock,case when orderqty>usedstock then orderqty-usedstock else 0 end as lessqty from sel2