Learning Scenarios for SQL Server (about Row_number () and coalesce () use)

BEGIN TRAN

CREATE TABLE [dbo]. [Cardata] (
[Carid] [INT] Null
[Mileage] [INT] Null
[M_year] [INT] Null
[M_month] [INT] Null
[M_day] [INT] Null
) on [PRIMARY]
GO
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (1, 10, 2015, 1, 1)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (1, 15, 2015, 1, 2)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (1, 15, 2015, 1, 5)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (1, 20, 2015, 1, 6)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (1, 26, 2015, 1, 9)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (1, 30, 2015, 1, 10)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (1, 35, 2015, 1, 11)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (2, 20, 2015, 1, 5)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (2, 22, 2015, 1, 8)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (2, 40, 2015, 1, 10)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (2, 45, 2015, 1, 11)
INSERT [dbo]. [Cardata] ([Carid], [mileage], [m_year], [M_month], [M_day]) VALUES (3, 50, 2015, 1, 11)

ROLLBACK

--Use the SQL statement to count the mileage per car (not the total mileage)
--Key points: How to get on a record, do you have a function like rownum? Yes! Row_number ()
--After sorting according to Carid, the rownum
SELECT row_number () over (order by C.carid) as rownum,c.* from Cardata C

--After grouping by Carid
SELECT C.carid, Row_number () over (PARTITION by C.carid ORDER by C.m_year,c.m_month,c.m_day) as RowNum, c.mileage from Car Data C

--Bo Friends answer
With both as (
SELECT row_number () over (PARTITION by Carid ORDER by Carid, M_year, M_month, M_day) as NodeId
, C.carid
, C.mileage
, C.m_year
, C.m_month
, C.m_day
From Cardata as C
)
SELECT a.*
, A.mileage-coalesce (b.nextmileage, 0) as ' incremental '
From both as A
OUTER APPLY (SELECT mileage as nextmileage from both as b WHERE B.nodeid = a.nodeid-1 and B.carid = A.carid) as B;

---supplementary knowledge function coalesce
--Indicates that at least one parameter should be of the NULL type. That returns the value of the first non-null type
Select COALESCE (null,null);
SELECT COALESCE (Null,null,getdate ());
SELECT COALESCE (null,0);

Learning Scenarios for SQL Server (about Row_number () and coalesce () use)