這里是輸入和輸出。
撰寫一個查詢，其中匯率在這些日期之間沒有變化。
input | output
===========================|===========================================
date rate | startdate end date rate
2014-09-18 270 | 2014-09-18 2014-09-19 270
2014-09-19 270 | 2014-09-20 2014-09-22 310
2014-09-20 310 | 2014-09-23 2014-09-23 320
2014-09-21 310 | 2014-09-24 2014-09-24 310
2014-09-22 310 | 2014-09-25 2014-09-25 320
2014-09-23 320 | 2014-09-26 2014-09-26 270
2014-09-24 310 |
2014-09-25 320 |
2014-09-26 270 |
uj5u.com熱心網友回復：
這是一個缺口和孤島問題。一種解決方案使用行號差異方法：
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY date) rn1,
ROW_NUMBER() OVER (PARTITION BY rate ORDER BY date) rn2
FROM yourTable
)
SELECT MIN(date) AS startdate, MAX(date) AS enddate, rate
FROM cte
GROUP BY rate, rn1-rn2
ORDER BY startdate;
演示
uj5u.com熱心網友回復：
with data as
(
select 1 as Id, '2021/10/01' as date
union all
select 1 as Id, '2021/10/02' as date
union all
select 1 as Id, '2021/10/03' as date
union all
select 2 as Id, '2021/10/04' as date
union all
select 2 as Id, '2021/10/05' as date
union all
select 2 as Id, '2021/10/06' as date
)
SELECT
DISTINCT
Id,
FIRST_VALUE(date) OVER (PARTITION BY Id ORDER BY date ) as FirstValue,
LAST_VALUE (date) OVER (PARTITION BY Id ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as LastValue
from data
GROUP BY id, date
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/313269.html
標籤：sql sql-server 查询语句