在SQL Server中创建行号列

Question

如下表

tbody> <<tr>101010

ContactID	FaaliyetTipi	Sonuc	Yil
10	1	Klavye	1999
	2	Klavye	1999
	4	Klavye	1999
	5	鼠标	1999
100	6	鼠标	1999
100	7	Klavye	1999
100	7	鼠标	2000
100	9	鼠标	1999
100	10	鼠标	1999

row_number()可以做到这一点。

将row_number按ContactID, FaaliyetTipi分组，按Yil排序，则取除rn = 2以外的所有记录，因为rn = 2是Yil最大的记录。

with cte as (
select *, row_number() over (partition by ContactID, FaaliyetTipi order by Yil) as rn
from mytable
)
select *
from cte
where rn = 1;

演示

我的第一个答案:

WITH CTE AS (
SELECT 
t1.ContactID, 
t1.FaaliyetTipi, 
t1.Sonuc, 
t1.Yil,
t2.Sonuc AS Sonuc2,
t2.Yil AS Yil2,
ROW_NUMBER() OVER (PARTITION BY t1.ContactID, t1.FaaliyetTipi ORDER BY t1.Yil) AS RowNum
FROM table_name t1
JOIN table_name t2 
ON t1.ContactID = t2.ContactID 
AND t1.FaaliyetTipi = t2.FaaliyetTipi 
AND t1.Sonuc != t2.Sonuc 
AND t1.Yil != t2.Yil
AND t1.Yil > t2.Yil
)
SELECT 
ContactID, 
FaaliyetTipi, 
Sonuc, 
Yil, 
CASE 
WHEN Yil - Yil2 > 0 THEN Yil - Yil2 
ELSE Yil2 - Yil 
END AS Difference
FROM CTE
WHERE RowNum = 1;

然后，我更改代码:

SELECT 
ContactID, 
FaaliyetTipi, 
Sonuc, 
Yil, 
MIN(Yil) OVER (PARTITION BY ContactID, FaaliyetTipi) - Yil AS Difference
FROM your_table
WHERE (ContactID, FaaliyetTipi) IN (
SELECT ContactID, FaaliyetTipi
FROM your_table
GROUP BY ContactID, FaaliyetTipi
HAVING COUNT(DISTINCT Sonuc) > 1
)

因为我们实际上不需要自连接或者ROW_NUMBER()