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

sql puzzle-当两列更改值时将值增加一

sql-server-2016 tsql sql-server sql

codingguy3000 · 技术社区 · 7 年前

我讨厌while循环,我喜欢尽可能避免它们。

我遇到了一个可能需要一个while循环来解决的场景。不过,在我编写一个while循环之前,让我先在网上问问我的朋友。

假设你有以下数据…

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)

你就这样结束了…

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

我想要一种更新priority列的方法,使它从1开始,每次widgettype或issecondarywidget更改值时,priority增加1。

数据应该是这样的…

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

谢谢你

2 回复 | 直到 7 年前

Larnu 7 年前

使用CTE和 ROW_NUMBER 要建立组:

WITH CTE AS(
    SELECT tv.recnum,
           tv.widgettype,
           tv.issecondarywidget,
           ROW_NUMBER() OVER (ORDER BY tv.recnum) - 
           ROW_NUMBER() OVER (PARTITION BY CONCAT(tv.widgettype,tv.issecondarywidget) ORDER BY tv.recnum) AS Grp
    FROM @tv_mytable tv)
SELECT CTE.recnum,
       CTE.widgettype,
       CTE.issecondarywidget,
       DENSE_RANK() OVER(ORDER BY CTE.Grp) AS priotity
FROM CTE;

如果你想 UPDATE 然后你会:

WITH CTE AS(
    SELECT tv.recnum,
           tv.widgettype,
           tv.issecondarywidget,
           [Priority],
           ROW_NUMBER() OVER (ORDER BY tv.recnum) - 
           ROW_NUMBER() OVER (PARTITION BY CONCAT(tv.widgettype,tv.issecondarywidget) ORDER BY tv.recnum) AS Grp
    FROM @tv_mytable tv),
New AS(
    SELECT CTE.recnum,
           CTE.widgettype,
           CTE.issecondarywidget,
           [Priority],
           DENSE_RANK() OVER(ORDER BY CTe.Grp) AS NewPriotity
    FROM CTE)
UPDATE New
SET [priority] = NewPriotity;


SELECT *
FROM @tv_mytable

Gordon Linoff 7 年前

在这种情况下,可以使用 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;