;WITH cte AS
(
SELECT '1' id,'10' qty,'2014-02-09' times UNION ALL
SELECT '1','20','2014-02-16' UNION ALL
SELECT '1','30','2014-02-23' UNION ALL
SELECT '1','a','2014-03-09' UNION ALL
SELECT '1','50','2014-03-23' UNION ALL
SELECT '1','60','2014-04-06' UNION ALL
SELECT '1','70','2014-04-20' UNION ALL
SELECT '1','a','2014-04-27' UNION ALL
SELECT '1','90','2014-05-11' UNION ALL
SELECT '1','100','2014-05-18'
)
SELECT * FROM cte
需求:
以a为节点,取a前面的第一个值,效果如下:
1 10 2014-02-09
1 a 2014-03-09
1 50 2014-03-23
1 a 2014-04-27
1 90 2014-05-11
------解决方案--------------------
with cte AS
(
SELECT '1' id,'10' qty,'2014-02-09' times UNION ALL
SELECT '1','20','2014-02-16' UNION ALL
SELECT '1','30','2014-02-23' UNION ALL
SELECT '1','a','2014-03-09' UNION ALL
SELECT '1','50','2014-03-23' UNION ALL
SELECT '1','60','2014-04-06' UNION ALL
SELECT '1','70','2014-04-20' UNION ALL
SELECT '1','a','2014-04-27' UNION ALL
SELECT '1','90','2014-05-11' UNION ALL
SELECT '1','100','2014-05-18'),
t as
(select row_number() over(order by getdate()) 'rn',
id,qty,times
from cte)
select id,qty,times
from t a
where a.qty='a' or a.rn=1
or exists(select 1 from t b where b.rn=a.rn-1 and b.qty='a')
/*
id qty times
---- ---- ----------
1 10 2014-02-09
1 a 2014-03-09
1 50 2014-03-23
1 a 2014-04-27
1 90 2014-05-11
(5 row(s) affected)
*/
------解决方案--------------------
WITH cte
AS ( SELECT '1' id ,
'10' qty ,
'2014-02-09' times
UNION ALL
SELECT '1' ,
'20' ,
'2014-02-16'
UNION ALL
SELECT '1' ,
'30' ,
'2014-02-23'
UNION ALL
SELECT '1' ,
'a' ,
'2014-03-09'
UNION ALL
SELECT '1' ,
'50' ,
'2014-03-23'
UNION ALL
SELECT '1' ,
'60' ,
'2014-04-06'