添加行sql時在列中重新編號-有解無憂

對于像這樣的表

create table Stations_in_route
(
ID_station_in_route int primary key,
ID_route int,
ID_station int,
Number_in_route int not null
)

有以下觸發器在路由添加新行后更改 Number_in_route 列中的值。路由中的號碼串列必須保持一致。

create trigger stations_in_route_after_insert on Stations_in_route
after insert
as

if exists
(select *from Stations_in_route
where Stations_in_route.ID_station_in_route not in (select ID_station_in_route from inserted)
and Stations_in_route.ID_route in (select ID_route from inserted) 
and Stations_in_route.Number_in_route in (select Number_in_route from inserted))

begin
update Stations_in_route
set Number_in_route = Number_in_route   1
where Stations_in_route.ID_station_in_route not in (select ID_station_in_route from inserted)
and Stations_in_route.ID_route in (select ID_route from inserted) 
and Stations_in_route.Number_in_route >= (select Number_in_route from inserted where Stations_in_route.ID_route = inserted.ID_route)
end

如果執行插入一個 ID_route，此觸發器將引發錯誤：

子查詢回傳了 1 個以上的值。當子查詢跟隨 =、!=、<、<=、>、>= 或當子查詢用作運算式時，這是不允許的。

例如，

Insert into Stations_in_route values(25, 4, 11, 3),(26, 4, 10, 5)

怎么修？

ID_station_in_route	ID_route	站號	Number_in_route
1	4	1	1
2	4	2	2
3	4	3	3
4	4	4	4
5	4	5	5
6	4	6	6
7	4	7	7
8	4	8	8

我希望添加后的串列會變成這樣

ID_station_in_route	ID_route	站號	Number_in_route
1	4	1	1
2	4	2	2
25	4	11	3
3	4	3	4
26	4	10	5
4	4	4	6
5	4	5	7
6	4	6	8
7	4	7	9
8	4	8	10

這不是整張桌子，因為還有其他路線

uj5u.com熱心網友回復：

根據要求，當您向路徑添加新停靠點時，您需要將它們正確插入到所需的序列中，并將所有現有停靠點從該點向前推，以保持連續的序列。當您插入一行時，這并不是很難（只是number_in_route 1 where number_in_route > new_number_in_route），但是當您插入更多行時，您基本上需要為每個新行將整個后續停靠點推入 1 。為了說明，假設你從這個開始：

如果我們插入兩個新行，例如：

INSERT dbo.Stations_in_route
(
  ID_station_in_route,
  ID_route,
  ID_station,
  Number_in_route
)
VALUES (25, 4, 11, 3),(26, 4, 10, 5);
-- add a stop at 3 ^              ^
----------------- add a stop at 5 ^

我們可以通過將其放慢為單獨的步驟來說明這一點。首先，我們需要在位置 #3 添加這一行：

我們通過將所有 > 3 的行向下推 1 來做到這一點：

但是現在當我們在位置 #5 添加這一行時：

That's the new position #5, after the previous shift, so it looks like this:

We can do this with the following trigger, which is possibly a little more complicated than it has to be, but is better IMHO than tedious loops which might otherwise be required.

CREATE TRIGGER dbo.tr_ins_Stations_in_route ON dbo.Stations_in_route
FOR INSERT AS
BEGIN
    ;WITH x AS 
    (
      SELECT priority = 1, *, offset = ROW_NUMBER() OVER 
        (PARTITION BY ID_route ORDER BY Number_in_route)
      FROM inserted AS i
      UNION ALL 
      SELECT priority = 2, s.*, offset = NULL FROM dbo.Stations_in_route AS s
        WHERE s.ID_route IN (SELECT ID_route FROM inserted)
    ), 
    y AS 
    (
      SELECT *, rough_rank = Number_in_route 
            COALESCE(MAX(offset) OVER (PARTITION BY ID_Route 
            ORDER BY Number_in_route ROWS UNBOUNDED PRECEDING),0) 
          - COALESCE(offset, 0),
        tie_break = ROW_NUMBER() OVER 
          (PARTITION BY ID_route, ID_station_in_route ORDER BY priority)
      FROM x
    ),
    z AS 
    (
      SELECT *, new_Number_in_route = ROW_NUMBER() OVER 
        (PARTITION BY ID_Route ORDER BY rough_rank, priority) 
      FROM y WHERE tie_break = 1
    )
    UPDATE s SET s.Number_in_route = z.new_Number_in_route
      FROM dbo.Stations_in_route AS s
      INNER JOIN z ON s.ID_route = z.ID_route
        AND s.ID_station_in_route = z.ID_station_in_route;
END

Working example db<>fiddle

I've mentioned a couple of times that you might want to handle ties for new rows, e.g. if the insert happened to be:

Insert into Stations_in_route values(25, 4, 11, 3),(26, 4, 10, 3)

For that you can add additional tie-breaking criteria to this clause:

  new_Number_in_route = ROW_NUMBER() OVER 
    (PARTITION BY ID_Route ORDER BY rough_rank, priority)

e.g.:

  new_Number_in_route = ROW_NUMBER() OVER 
    (PARTITION BY ID_Route ORDER BY rough_rank, priority, 
     ID_station_in_route DESC)

uj5u.com熱心網友回復：

我無法使用問題中的測驗代碼/資料重現例外，但是我猜想問題出在觸發器中的這段代碼上：

AND Stations_in_route.Number_in_route >= 
(
SELECT Number_in_route
FROM inserted
WHERE Stations_in_route.ID_route = inserted.ID_route
)

那里的引擎會隱式地期望 >= 運算子右側的子查詢回傳標量結果（單行，單列結果），但是插入的表實際上是一個表......它可能包含多條記錄（就像您的示例中概述的多行插入/更新/等型別陳述句中的情況一樣）。鑒于該子查詢中的過濾器（即 WHERE 子句）不能保證是唯一的（ID_route 似乎不是唯一的，在您的示例中，您有一個插入陳述句，實際上插入了具有相同 ID_route 值的多行），那么查詢肯定有可能回傳非標量結果。

要解決這個問題，您需要調整該子查詢以保證標量值（單行單列）的結果。您已經使用選擇器保證了單個列……現在您還需要添加邏輯來保證單個結果/記錄。這可能包括以下一項或多項（或也可能包括其他內容）：

將選定的 Number_in_route 列包裝在聚合函式中（即 MAX() 也許？）
添加帶有 ORDER BY 的 TOP 1 以獲取要與之比較的記錄
向 WHERE 子句添加額外的過濾器以確保回傳單個結果

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/356352.html

標籤：sql sql-server 数据库触发器

上一篇：當first_name列為空時，sql查詢將值從second_names列移動到first_name列

下一篇：使用scrapy決議專案并提高代碼速度/整體性能