英文:
i have requirement in which column value need to break into two column rangefrom and rangeto
问题
我有一个需求，需要将msn列拆分成rangefrom和rangeto表格
num msn date
1ab 5 25/2/2019
1ab 7 25/2/2019
1ab 8 25/2/2019
1ac 8 25/4/2019
1ad 9 25/5/2019
1ad 10 25/5/2019
1ad 11 25/5/2019
输出应该如下，如果msn不连续，则相同的值将作为rangefrom和rangeto，如果连续，则根据日期和num，最小值将作为rangefrom，最大值将作为rangeto
num rangefrom rangeto date
1ab 5 5 25/2/2019
1ab 7 8 25/2/2019
1ad 9 11 25/4/2019
英文:
I have requirement in which msn column need to break into rangefrom and rangeto
table
num msn date
1ab 5 25/2/2019
1ab 7 25/2/2019
1ab 8 25/2/2019
1ac 8 25/4/2019
1ad 9 25/5/2019
1ad 10 25/5/2019
1ad 11 25/5/2019
output should like this if msn is not continuous then same value goes to rangefrom and rangeto if continuous then min value will go to rangefrom and max value will go rangeto based on date and num
num rangefrom rangeto date
1ab 5 5 25/2/2019
1ab 7 8 25/2/2019
1ad 9 11 25/4/2019
答案1
得分: 1
以下是代码的翻译部分：
SELECT NUM, MIN(MSN), MAX(MSN), DTE
FROM (SELECT NUM, TRIM(MSN) MSN, DTE, TRIM(MSN)-ROW_NUMBER() OVER(ORDER BY DTE, TRIM(MSN)) SEQNUM
FROM T)
GROUP BY NUM, SEQNUM, DTE
ORDER BY NUM;
英文:
You may try below query -
SELECT NUM, MIN(MSN), MAX(MSN), DTE
FROM (SELECT NUM, TRIM(MSN) MSN, DTE, TRIM(MSN)-ROW_NUMBER() OVER(ORDER BY DTE, TRIM(MSN)) SEQNUM
FROM T)
GROUP BY NUM, SEQNUM, DTE
ORDER BY NUM;
答案2
得分: 1
使用LAG()和LEAD()窗口函数以及一个CTE：
with cte as (
select t.*,
case when "msn" - lag("msn") over (partition by "num", "date" order by "msn") = 1 then 0 else 1 end isfrom,
case when lead("msn") over (partition by "num", "date" order by "msn") - "msn" = 1 then 0 else 1 end isto
from tablename t
)
select c1."num", c1."msn" rangefrom, min(c2."msn") rangeto, c1."date"
from cte c1 inner join cte c2
on c2."num" = c1."num" and c2."date" = c1."date"
and (
(c1.isfrom = 1 and c2.isto = 1 and c1."msn" < c2."msn")
or
(c1.isfrom = 1 and c1.isto = 1 and c1."msn" = c2."msn")
)
group by c1."num", c1."msn", c1."date"
order by c1."num", c1."msn", c1."date"
查看演示。
结果：
> num | RANGEFROM | RANGETO | date
> :-- | --------: | ------: | :--------
> 1ab | 5 | 5 | 25/2/2019
> 1ab | 7 | 8 | 25/2/2019
> 1ac | 8 | 8 | 25/4/2019
> 1ad | 9 | 11 | 25/5/2019
英文:
With LAG() and LEAD() window functions and a CTE:
with cte as (
select t.*,
case when "msn" - lag("msn") over (partition by "num", "date" order by "msn") = 1 then 0 else 1 end isfrom,
case when lead("msn") over (partition by "num", "date" order by "msn") - "msn" = 1 then 0 else 1 end isto
from tablename t
)
select c1."num", c1."msn" rangefrom, min(c2."msn") rangeto, c1."date"
from cte c1 inner join cte c2
on c2."num" = c1."num" and c2."date" = c1."date"
and (
(c1.isfrom = 1 and c2.isto = 1 and c1."msn" < c2."msn")
or
(c1.isfrom = 1 and c1.isto = 1 and c1."msn" = c2."msn")
)
group by c1."num", c1."msn", c1."date"
order by c1."num", c1."msn", c1."date"
See the demo.<br/>
Results:
> num | RANGEFROM | RANGETO | date
> :-- | --------: | ------: | :--------
> 1ab | 5 | 5 | 25/2/2019
> 1ab | 7 | 8 | 25/2/2019
> 1ac | 8 | 8 | 25/4/2019
> 1ad | 9 | 11 | 25/5/2019
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论