借鉴地址：http://www.cnblogs.com/kissazi2/archive/2013/02/27/2935307.html
DECLARE @temp TABLE
(
[C_ClientID] NVARCHAR(50) ,
[Integral] NVARCHAR(50)
)
DECLARE @ClientID NVARCHAR(50),
@Integral NVARCHAR(50)
INSERT INTO @temp
SELECT newT2.C_ClientID,CONVERT(DECIMAL,newT2.ReceivableMoney)+CONVERT(DECIMAL,newt2.FactMoney)+CONVERT(DECIMAL,newt2.OrderCount) Integral
FROM (SELECT newT.C_ClientID,'ReceivableMoney'= CASE WHEN newT.ReceivableMoney<=1100 AND newT.ReceivableMoney>=0
THEN '8.75' WHEN newT.ReceivableMoney>1100 AND newT.ReceivableMoney<=2200 THEN '17.5'
WHEN newT.ReceivableMoney>2200 AND newT.ReceivableMoney<=3300 THEN '26.25' ELSE '35' END ,
'FactMoney'= CASE WHEN newT.FactMoney>=0 AND FactMoney<=1100 THEN '8.75'
WHEN newT.FactMoney>1100 AND FactMoney<=2200 THEN '17.5' WHEN newT.FactMoney>2200 AND FactMoney<=3300 THEN '26.5' ELSE '35' END,
'OrderCount'=CASE WHEN newT.OrderCount>0 AND newt.OrderCount<=2 THEN '7.5' WHEN newT.OrderCount>2 AND newt.OrderCount<=4 THEN '15'
WHEN newT.OrderCount>4 AND newt.OrderCount<=6 THEN '22.5' ELSE '30' END
FROM (SELECT co.C_ClientID,
SUM(C_ReceivableMoney)-ISNULL((SELECT SUM(C_Cash) FROM LMS_Cancel lc WHERE lc.C_ClientID=co.C_ClientID AND lc.C_CreateTime BETWEEN DATEADD(YEAR,-1,GETDATE()) AND GETDATE()),0) ReceivableMoney,
SUM(C_FactMoney) -ISNULL((SELECT SUM(C_ReturnCash) FROM LMS_Cancel lc WHERE lc.C_ClientID=co.C_ClientID AND lc.C_CreateTime BETWEEN DATEADD(YEAR,-1,GETDATE()) AND GETDATE()),0) FactMoney,
COUNT(*) OrderCount
FROM CRM_Order co
WHERE co.C_CreateTime BETWEEN DATEADD(YEAR,-1,GETDATE()) AND GETDATE() AND co.C_AuditTime IS NOT NULL AND co.C_Status!='31'
GROUP BY co.C_ClientID ) newT ) newT2
WHILE EXISTS ( SELECT [C_ClientID]
FROM @temp )
BEGIN
SELECT TOP 1 @ClientID = [C_ClientID] ,
@Integral = [Integral]
FROM @temp
delete from @temp where [C_ClientID] =@ClientID
PRINT '输出:----' + @ClientID+' ：'+@Integral
END
