如何將此表拆分為：
| 年份范圍 | 車輛制造 | 車型 |
|---|---|---|
| 2014-2018 | 日產 | GT-R |
顯示為：
| 年份范圍 | 車輛制造 | 車型 |
|---|---|---|
| 2014 | 日產 | GT-R |
| 2015 | 日產 | GT-R |
| 2016 年 | 日產 | GT-R |
| 2017 | 日產 | GT-R |
| 2018 | 日產 | GT-R |
uj5u.com熱心網友回復：
使用臨時計數/數字表與 CROSS APPLY 相結合的另一種選擇
例子
Select [Year] = R1 N
,[Vehicle Make]
,[Vehicle Model]
From YourTable A
Cross Apply ( values ( convert(int,left([Year Range] ,4))
,convert(int,right([Year Range],4))
)
)B(R1,R2)
Join (Select Top 100 N=-1 Row_Number() Over (Order By (Select NULL))
From master..spt_values n1 ) C on N<=R2-R1
結果
Year Vehicle Make Vehicle Model
2014 Nissan GT-R
2015 Nissan GT-R
2016 Nissan GT-R
2017 Nissan GT-R
2018 Nissan GT-R
uj5u.com熱心網友回復：
您還可以使用遞回：
With MyTable as (
select YearRange='2014-2018', Make='Nissan', Model='GT-R' union all
select YearRange='2015-2020', Make='Ford', Model='Kuga'
),
AllYears as (
select ThisYear=cast(left(YearRange,4) as integer), LastYear=cast(right(YearRange,4) as integer), Make, Model from MyTable
union all
select ThisYear=ThisYear 1, LastYear=LastYear, Make, Model from AllYears where ThisYear < LastYear
)
select ThisYear, Make, Model from AllYears
order by Make, Model, ThisYear
uj5u.com熱心網友回復：
您可以使用遞回 CTE 來生成年份串列，然后使用 BETWEEN 子句將其與表連接。
DECLARE @table table(YearRange varchar(20), VehicleMake varchar(20), VehicleModel varchar(20))
insert into @table values
('2014-2018','Nissan','GT-R');
;WITH Cte_year as
(
SELECT 2014 as y
UNION ALL
SELECT Y 1 as Y
from Cte_year
where y < 2017
), cte_rangesplit as
(
SELECT left(yearrange,4) as startRange, right(yearrange,4) as endRange
,VehicleMake, VehicleModel
from @table)
SELECT cy.y, c.VehicleMake, c.VehicleModel FROM Cte_year as cy
inner join cte_rangesplit as c
on cy.y between c.startRange and c.endRange
| 是的 | 車輛制造 | 車輛型號 |
|---|---|---|
| 2014 | 日產 | GT-R |
| 2015 | 日產 | GT-R |
| 2016 年 | 日產 | GT-R |
| 2017 | 日產 | GT-R |
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/447906.html