SQL:如何在选择查询中基于另一个列添加 ID 列?

Question

下面是我的数据集的近似

值

DataSetName Date        Sname    Level     Frequency    SetId   ScenarioId  FrequencyId
Set A    01/31/1980  Base     64,007     Monthly       49    1           2
Set A    02/29/1980  Base     64,014     Monthly       49    1           2
Set A    03/31/1980  Stress   64,015     Monthly       49    2           2
Set A    04/30/1980  Stress   64,008     Monthly       49    2           2
Set B    05/31/1980  Storm    63,993     Monthly       54    5           2
Set B    06/30/1980  Raptor   63,972     Monthly       54    24          2
Set B    07/31/1980  Agile    63,788     Monthly       54    25          2
Set B    08/31/1980  Pond     63,868     Monthly       54    27          2
Set B    07/31/1980  Agile    63,212     Monthly       54    25          2

对于字段 Sname，我尝试在每次运行 select 语句时创建一个唯一但动态的 Id。

我尝试使用row_number函数

Select a.*,ROW_NUMBER() OVER(PARTITION BY Sname Order by DataSetName) 
as S_row_id from Table1

我还尝试使用子查询

Select Top 100 a.*,b.S_Row_Id
from Table1 a
Left join
( Select Distinct Sname,ROW_NUMBER() OVER(PARTITION BY Sname 
Order by Sname) as Scenario_Row_Id from Table1)b
on a.ScenarioName=b.ScenarioName

这些都没有给我正在寻找的输出，看起来很像这样

DataSetName Date        Sname    Level     Frequency    SetId   S_row_id    FrequencyId
Set A    01/31/1980  Base     64,007     Monthly       49    1           2
Set A    02/29/1980  Base     64,014     Monthly       49    1           2
Set A    03/31/1980  Stress   64,015     Monthly       49    2           2
Set A    04/30/1980  Stress   64,008     Monthly       49    2           2
Set B    05/31/1980  Storm    63,993     Monthly       54    3           2
Set B    06/30/1980  Raptor   63,972     Monthly       54    4           2
Set B    07/31/1980  Agile    63,788     Monthly       54    5           2
Set B    08/31/1980  Pond     63,868     Monthly       54    6           2
Set B    07/31/1980  Agile    63,212     Monthly       54    5           2
Set B    07/31/1980  Pond     63,457     Monthly       54    6           2

我该怎么做才能实现这个结果集，其中 Sname 每次运行时都会以增量顺序自动分配 scenarioId。如果您需要更多详细信息，请告诉我？

Answer 1

类似

SELECT T2.*, D2.TheID FROM Table1 T2 JOIN 
(
SELECT D1.Sname, row_number() OVER (ORDER BY D1.sname) TheID FROM 
(SELECT DISTINCT t1.Sname From Table1 t1) D1
) D2
ON T2.sname = D2.sname

Answer 2

您可以使用apply：

select a.*, t2.S_row_id
from Table1 t1 cross apply 
( select count(distinct t2.sname) as S_row_id
from table1 t2
where t2.sname = t1.sname and
t2.Date <= t1.date
) t2;

Answer 3

为什么不是DENSE_RANK函数？

with
t as(
select * from (values
('Set A', '01/31/1980', 'Base', 64.007, 'Monthly', 49, 1, 2),
('Set A', '02/29/1980', 'Base', 64.014, 'Monthly', 49, 1, 2),
('Set A', '03/31/1980', 'Stress', 64.015, 'Monthly', 49, 2, 2),
('Set A', '04/30/1980', 'Stress', 64.008, 'Monthly', 49, 2, 2),
('Set B', '05/31/1980', 'Storm', 63.993, 'Monthly', 54, 5, 2),
('Set B', '06/30/1980', 'Raptor', 63.972, 'Monthly', 54, 24, 2),
('Set B', '07/31/1980', 'Agile', 63.788, 'Monthly', 54, 25, 2),
('Set B', '08/31/1980', 'Pond', 63.868, 'Monthly', 54, 27, 2),
('Set B', '07/31/1980', 'Agile', 63.212, 'Monthly', 54, 25, 2),
('Set B', '07/31/1980', 'Pond', 63.457, 'Monthly', 54, 6, 2)
)v(DataSetName, "Date", Sname, Level, Frequency, SetId, ScenarioId, FrequencyId)
)
select *,
dense_rank() over(order by DataSetName, Sname) S_row_id
from t;

DataSetName Date        Sname   Level   Frequency   SetId   ScenarioId  FrequencyId S_row_id
Set A   02/29/1980  Base    64,014  Monthly     49      1           2           1
Set A   01/31/1980  Base    64,007  Monthly     49      1           2           1
Set A   04/30/1980  Stress  64,008  Monthly     49      2           2           2
Set A   03/31/1980  Stress  64,015  Monthly     49      2           2           2
Set B   07/31/1980  Agile   63,788  Monthly     54      25          2           3
Set B   07/31/1980  Agile   63,212  Monthly     54      25          2           3
Set B   07/31/1980  Pond    63,457  Monthly     54      6           2           4
Set B   08/31/1980  Pond    63,868  Monthly     54      27          2           4
Set B   06/30/1980  Raptor  63,972  Monthly     54      24          2           5
Set B   05/31/1980  Storm   63,993  Monthly     54      5           2           6