其实想根据订单，自动结算库存,然后判断哪些单可以出库，标记 #TMP_TRADE.Flag 字段
可是我这样写出来，3千订单，要跑2分钟才能结算出来， 再次请高手指点下，
----下面我先贴上我的SQL 语句
-------- 定义订单列表 -------
CREATE TABLE #TMP_TRADE
(
ID INT,
Oid VARCHAR(50),
UserId VARCHAR(100),
Country VARCHAR(100),
StateOrProvince VARCHAR(100),
City VARCHAR(100),
PostalCode VARCHAR(20),
ShopID INT,
ShippingCost DECIMAL(16,2),
TotalPrice DECIMAL(16,2),
SaleDate VARCHAR(20),
PaidDate VARCHAR(20),
ShippedDate VARCHAR(20),
Flag INT --是否可出库(有库存) 0 无库存，1有库存
)
-- 构造订单数据(实际要处理的有3千条数据)
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
------ 库存表(动态结算，暂无数据) ------
CREATE TABLE #TMP_SkuNum
(
SKU_ID INT,
STOCK_ID INT,
NUM INT
)
--- SKU 产品关联表 ----
CREATE TABLE #SKU_PRODUCT_MTM
(
ID INT,
PRODUCT_CODE VARCHAR(20),
SKU_CODE VARCHAR(20),
SKU_QTY INT
)
INSERT INTO #SKU_PRODUCT_MTM SELECT 1 'SP001','SKU001',2
CREATE TABLE #Calc_Sku
(
Id INT,
Num INT
)
DECLARE @T_ID INT
DECLARE @Oid VARCHAR(20)
DECLARE @Calc_Num INT
SET @T_ID=0
SET @Oid=''
DECLARE MY_CURSOR CURSOR FOR SELECT Id,Oid FROM #TMP_TRADE
OPEN MY_CURSOR
FETCH FROM MY_CURSOR INTO @T_ID,@Oid
WHILE (@@FETCH_STATUS=0)
BEGIN
IF EXISTS(SELECT 1 FROM tEBay_TradeProduct tetp JOIN #SKU_PRODUCT_MTM spm ON tetp.SKU=spm.PRODUCT_CODE WHERE tetp.Oid=@Oid ) --判断产品有无关联的SKU
BEGIN
INSERT #Calc_Sku
SELECT tssn.Num-tetp.Num*spm.SKU_QTY
FROM tEBay_TradeProduct tetp --tEBay_TradeProduct 订单产品表，此表实际中只有20W数据
JOIN #SKU_PRODUCT_MTM spm ON tetp.SKU=spm.PRODUCT_CODE
JOIN #TMP_SkuNum tssn ON tss.Id=tssn.SKU_ID AND tssn.STOCK_ID=1
WHERE tetp.Oid=@Oid
IF EXISTS(SELECT 1 FROM #Calc_Sku WHERE Num<0)
BEGIN
PRINT '无库存Oid:'+@Oid
END
ELSE
BEGIN
UPDATE #TMP_TRADE SET Flag = 1 WHERE ID=@T_ID
UPDATE #TMP_SkuNum SET #TMP_SkuNum.NUM = #Calc_Sku.Num FROM #TMP_SkuNum,#Calc_Sku WHERE #TMP_SkuNum.SKU_ID=#Calc_Sku.Id AND #TMP_SkuNum.STOCK_ID=1
END
DELETE FROM #Calc_Sku
END
FETCH FROM MY_CURSOR INTO @T_ID,@Oid
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
SELECT * FROM #TMP_TRADE ORDER BY Flag DESC,PaidDate
DROP TABLE #TMP_TRADE
DROP TABLE #TMP_SkuNum
DROP TABLE #SKU_PRODUCT_MTM
DROP TABLE #Calc_Sku
