时间戳

首页 > 解决方案 > SQL：如何在选择查询中添加基于另一列的 ID 列？

sql - SQL：如何在选择查询中添加基于另一列的 ID 列？

问题描述

下面是我的数据集的近似值

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。让我知道您是否需要 mroe 详细信息？

标签： sqlsql-serverrow-number

解决方案

为什么不是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

推荐阅读