代码之家 › 专栏 › 技术社区 › jackstraw22

SQL窗口函数的rank或rownum出现问题

rank function sql-server

0

jackstraw22 · 技术社区 · 5 年前

我希望以下数据的输出如下所示:

   id   empid   question            type    rownum
  1      75     How old are you     SSS       1
  2      75     NULL                LLL       2
  3      88     How old are you     SSS       1
  4      88     NULL                LLL       2
  5      99     How old are you     SSS       1
  6      99     How old are you     LLL       1
  7      99     NULL                LLL       2

    declare @t table(id int, empid int, question varchar(250), type char(3))
    insert into @t values( 1, 75, 'How old are you', 'SSS'),
                  (2, 75, NULL, 'LLL'),
                  (3, 88, 'How old are you', 'SSS'),
                  (4, 88, NULL, 'LLL'),
                  (5, 99, 'How old are you', 'SSS'),
                  (6, 99, 'How old are you', 'LLL'),
                  (7, 99, NULL, 'LLL')

我编写了下面的查询,结果很接近,但是我无法得到我想要的确切结果,因为它为empid=99的两种“LLL”类型都分配了rownum=2。

   select *,
   rank()over(partition by empid order by type desc,
   case when question is not null and type = 'LLL' then 1 else 2 end desc) as rownum
   from @t

0 回复 | 直到 5 年前

1

0

jackstraw22 5 年前

     select *,
     dense_rank()over(partition by empid, case when question is not null and type = 
     'LLL' then 1 else 2 end order by type desc
      ) as rownum
       from @t