iT邦幫忙

0

如何將mysql中表的某個數字欄位更新為有序的號,例如:1,5,10,15,20

  • 分享至 

  • xImage

例如:php 範例,使用 sql 語法。這是一個table原始樣子

從php的sql中添加一條資料,id為6,table變成如下

然後我將id=6的itorder以sql更改為 2,目的把這篇文章往前排序

現在我如何以sql語法把tabel的所有資料itsorder更新成這樣,我該怎麼做呢?

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
4
一級屠豬士
iT邦大師 1 級 ‧ 2022-10-03 23:16:53
最佳解答
create table it221003 (
  id int
, article text
, itsorder int
);

insert into it221003 values
(1, 'A12', 1),
(2, 'A15', 5),
(3, 'A25', 10),
(4, 'A28', 15),
(5, 'A31', 20),
(6, 'A35', 25);

update it221003
   set itsorder = 2
 where id = 6;

update it221003 i
   set itsorder = case 
                     when a.rn = 1 then 1
                     else (a.rn - 1) * 5
                   end
  from (select id
             , rank() over(order by itsorder) rn
          from it221003) a
 where i.id = a.id;
 
 select *
  from it221003;

id 	article itsorder
1 	A12 	1
2 	A15 	10
3 	A25 	15
4 	A28 	20
5 	A31 	25
6 	A35 	5

Demo

今日最正解
/images/emoticon/emoticon32.gif

1
海綿寶寶
iT邦大神 1 級 ‧ 2022-10-03 10:39:55

假設「從php的sql中添加一條資料,id為6」這裡面的id和itsorder是自動計算而不是手動指定,設為 f1(article)

則步驟如下:
1.SELECT article FROM table WHERE itsorder >=2 ORDER BY itsorder
2.DELETE table WHERE itsorder >=2
3.依序套用(呼叫 f1(article))步驟1所有 article

簡單說
就是「用 delete+insert 取代 update 的做法」

另外提個題外話
考慮系統效能
一般情形下是不會做這個「重排序號」的動作
只有在「要新增時發現找不到位置可以新增」(例:要新增 3 發現 12345 都已使用)時才會做這件事

0
sky940811
iT邦研究生 4 級 ‧ 2022-10-03 12:05:45

假設你的資料表預留了4個位置做文章排序的插入
你的sql可以作..

UPDATE table set itsorder=你想插入的值 WHERE id=文章ID
UPDATE table set itsorder+5 WHERE itsorder>=你想插入的值 AND id!=文章ID

套用你的範例:
UPDATE table set itsorder=5 WHERE id=6
UPDATE table set itsorder+5 WHERE itsorder>=5 AND id!=6

範例二:
假設有一筆文章id=7,想放在A25和A28的中間
UPDATE table set itsorder=16 WHERE id=7
if(itsorder % 5==0){  //當itsorder不為5的倍數時,不更新原有排序
    UPDATE table set itsorder+5 WHERE itsorder>=16 AND id!=7
}

這是初步的判斷,但還有一些其它的考慮點
就要依你的實際狀況作調整了

0
ckp6250
iT邦好手 1 級 ‧ 2022-10-03 14:49:09

理論上可行,實務上不可行,
萬一Table中有一千萬筆資料,這下怎麼辦?

0
wayne0127
iT邦新手 5 級 ‧ 2022-10-07 00:01:08
SET @i := -1;
UPDATE `[table_name]` SET `itsorder` = IF((@i := @i + 1) = 0, 1, @i * 5) ORDER BY `itsorder`;

我要發表回答

立即登入回答