我需要在不使用循环和相关子查询的情况下使用sql生成下表中的“required”列.这在sql 2008中是否可行？
Date Customer Value required Rule 20100101 1 12 12 20100101 2 0 If no value assign 0 20100101 3 32 32 20100101 4 42 42 20100101 5 15 15 20100102 1 12 Take last known value 20100102 2 0 Take last known value 20100102 3 39 39 20100102 4 42 Take last known value 20100102 5 16 16 20100103 1 13 13 20100103 2 24 24 20100103 3 39 Take last known value 20100103 4 42 Take last known value 20100103 5 21 21 20100104 1 14 14 20100104 2 24 Take last known value 20100104 3 39 Take last known value 20100104 4 65 65 20100104 5 23 23
基本上我正在为该客户填充具有最后知道值的空“值”单元格.请记住,最后一行可能没有有效值,因此您必须使用有效值从之前的行中选择它.
解决方法
法伊兹,
如果以下查询怎么样,它根据我的理解做你想做的事情.评论解释了每一步.看看联机丛书上的CTE.甚至可以更改此示例以使用sql 2008的新MERGE命令.
/* Test Data & Table */
DECLARE @Customers TABLE
(Dates datetime,Customer integer,Value integer)
INSERT INTO @Customers
VALUES ('20100101',1,12),('20100101',2,NULL),3,32),4,42),5,15),('20100102',39),16),('20100103',13),24),21),('20100104',14),65),23) ;
/* CustCTE - This gives us a RowNum to allow us to build the recursive CTE CleanCust */
WITH CustCTE
AS (SELECT Customer,Value,Dates,ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Dates) RowNum
FROM @Customers),/* CleanCust - A recursive CTE. This runs down the list of values for each customer,checking the Value column,if it is null it gets the prevIoUs non NULL value.*/
CleanCust
AS (SELECT Customer,ISNULL(Value,0) Value,/* Ensure we start with no NULL values for each customer */
Dates,RowNum
FROM CustCte cur
WHERE RowNum = 1
UNION ALL
SELECT Curr.Customer,ISNULL(Curr.Value,prev.Value) Value,Curr.Dates,Curr.RowNum
FROM CustCte curr
INNER JOIN CleanCust prev ON curr.Customer = prev.Customer
AND curr.RowNum = prev.RowNum + 1)
/* Update the base table using the result set from the recursive CTE */
UPDATE trg
SET Value = src.Value
FROM @Customers trg
INNER JOIN CleanCust src ON trg.Customer = src.Customer
AND trg.Dates = src.Dates
/* Display the results */
SELECT * FROM @Customers
