查詢拆分-有解無憂

ID     times            fdate                              ndate                                   tdate                                  etype
100       1               2020-01-01                   2020-12-31
101       2               2019-01-01                   2019-12-31                          2020-12-31
103       3               2018-01-01                   2018-12-31                          2019-12-31                       long
......

如何實作下述結果
id          times                fdate                       ndate                                  etype
100         1                     2020-01-01          2020-12-31

101         1                     2019-01-01          2019-12-31
101         2                     2019-12-31          2020-12-31

103         1                     2018-01-01          2018-12-31
103         2                     2018-12-31          2019-12-31
103         3                     2019-12-31                                                         long
......

uj5u.com熱心網友回復：

;with tb(ID, times, fdate, ndate, tdate, etype) as
(
select 100, 1, '2020-01-01', '2020-12-31', null, null union all
select 101, 2, '2019-01-01', '2019-12-31', '2020-12-31', null union all
select 103, 3, '2018-01-01', '2018-12-31', ' 2019-12-31', 'long'
)
--select * from tb
, tb2 as (select ID, times, fdate, ndate, tdate, etype from tb)
, tb3 as (select ID, times, fdate, ndate, tdate, etype from tb)
select tb3.ID, tb2.times, tb2.fdate, tb2.ndate, tb2.tdate, tb2.etype from tb2 cross join tb3
where tb2.times <= tb3.times
order by tb3.ID, tb3.times

uj5u.com熱心網友回復：

參考 1 樓 shoppo0505 的回復:

;with tb(ID, times, fdate, ndate, tdate, etype) as
(
select 100, 1, '2020-01-01', '2020-12-31', null, null union all
select 101, 2, '2019-01-01', '2019-12-31', '2020-12-31', null union all
select 103, 3, '2018-01-01', '2018-12-31', ' 2019-12-31', 'long'
)
--select * from tb
, tb2 as (select ID, times, fdate, ndate, tdate, etype from tb)
, tb3 as (select ID, times, fdate, ndate, tdate, etype from tb)
select tb3.ID, tb2.times, tb2.fdate, tb2.ndate, tb2.tdate, tb2.etype from tb2 cross join tb3
where tb2.times <= tb3.times
order by tb3.ID, tb3.times

結果中的times與fdate和ndate不匹配啊.

uj5u.com熱心網友回復：

參考 2 樓 weixin_42074072 的回復:

Quote: 參考 1 樓 shoppo0505 的回復:
;with tb(ID, times, fdate, ndate, tdate, etype) as
(
select 100, 1, '2020-01-01', '2020-12-31', null, null union all
select 101, 2, '2019-01-01', '2019-12-31', '2020-12-31', null union all
select 103, 3, '2018-01-01', '2018-12-31', ' 2019-12-31', 'long'
)
--select * from tb
, tb2 as (select ID, times, fdate, ndate, tdate, etype from tb)
, tb3 as (select ID, times, fdate, ndate, tdate, etype from tb)
select tb3.ID, tb2.times, tb2.fdate, tb2.ndate, tb2.tdate, tb2.etype from tb2 cross join tb3
where tb2.times <= tb3.times
order by tb3.ID, tb3.times

結果中的times與fdate和ndate不匹配啊.

那你怎么拆分的？看不懂了

uj5u.com熱心網友回復：

參考 3 樓 shoppo0505 的回復:

Quote: 參考 2 樓 weixin_42074072 的回復:
Quote: 參考 1 樓 shoppo0505 的回復:
;with tb(ID, times, fdate, ndate, tdate, etype) as
(
select 100, 1, '2020-01-01', '2020-12-31', null, null union all
select 101, 2, '2019-01-01', '2019-12-31', '2020-12-31', null union all
select 103, 3, '2018-01-01', '2018-12-31', ' 2019-12-31', 'long'
)
--select * from tb
, tb2 as (select ID, times, fdate, ndate, tdate, etype from tb)
, tb3 as (select ID, times, fdate, ndate, tdate, etype from tb)
select tb3.ID, tb2.times, tb2.fdate, tb2.ndate, tb2.tdate, tb2.etype from tb2 cross join tb3
where tb2.times <= tb3.times
order by tb3.ID, tb3.times

結果中的times與fdate和ndate不匹配啊.

那你怎么拆分的？看不懂了

d          times                fdate                       ndate                                  etype
100         1                     2020-01-01          2020-12-31
101         1                     2019-01-01          2019-12-31
101         2                     2019-12-31          2020-12-31
103         1                     2018-01-01          2018-12-31
103         2                     2018-12-31          2019-12-31
103         3                     2019-12-31                                                         long
tims 1到3對應的Fdate是從早到晚有順序才是對的.

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/16169.html

標籤：基礎類

上一篇：各位大神想請假一下遍歷英文標點符號時最后的冒號怎么單獨分割出來？

下一篇：求大神幫助