sql - SQL Puzzle - Increase the value by one when two columns change value

Question

I hate while loops and I like to avoid them when possible.

I have come across a scenario that might require a while loop to solve. However, let me ask my friends on the internet before I code a while loop.

Imagine you have the following data...

declare @tv_mytable table(recnum int identity(1,1) primary key, widgettype char(3), issecondarywidget bit default(0), priority int)

    insert @tv_mytable(widgettype, issecondarywidget) values('BAT', 0)
    insert @tv_mytable(widgettype, issecondarywidget) values('BAT', 0)
    insert @tv_mytable(widgettype, issecondarywidget) values('ANT', 0)
    insert @tv_mytable(widgettype, issecondarywidget) values('ANT', 0)
    insert @tv_mytable(widgettype, issecondarywidget) values('ANT', 1)
    insert @tv_mytable(widgettype, issecondarywidget) values('ANT', 1)
    insert @tv_mytable(widgettype, issecondarywidget) values('BAT', 1)
    insert @tv_mytable(widgettype, issecondarywidget) values('BAT', 1)

You wind up with this...

recnum      widgettype issecondarywidget priority
----------- ---------- ----------------- -----------
1           BAT        0                 NULL
2           BAT        0                 NULL
3           ANT        0                 NULL
4           ANT        0                 NULL
5           ANT        1                 NULL
6           ANT        1                 NULL
7           BAT        1                 NULL
8           BAT        1                 NULL

I want a way to update the priority column so that it starts at 1 and each time widgettype or issecondarywidget changes value the priority increases by 1.

The data would look like this...

recnum      widgettype issecondarywidget priority
----------- ---------- ----------------- -----------
1           BAT        0                 1
2           BAT        0                 1
3           ANT        0                 2
4           ANT        0                 2
5           ANT        1                 3
6           ANT        1                 3
7           BAT        1                 4
8           BAT        1                 4

Thank you

标签： sqlsql-servertsqlsql-server-2016

Answer 1

在这种情况下，您可以使用dense_rank(). 对于唯一值：

select t.*, dense_rank() over (order by widgettype issecondarywidget) as priority
from t;

如果要保持原来的顺序，就需要多一层窗函数：

select t.*, dense_rank() over (order by min_id) as priority
from (select t.*, min(id) over (order by widgettype issecondarywidget) as min_id
      from t
     ) t

将其放入update：

with toupdate as (
      select t.*, dense_rank() over (order by min_id) as new_priority
      from (select t.*, min(id) over (order by widgettype issecondarywidget) as min_id
            from t
           ) t
     )
update toupdate
     set priority = new_priority;