iT邦幫忙

0

SQL 字串分割 with phpmyadmin

各位大大想請教關於SQL的問題~
小弟是用 xampp 的 phpmyadmin 來執行資料庫的SQL

問題1

最近在做將上課時間的字串進行分割時,有查了一些資料
發現SUBSTRING_INDEX 可以達到我想要的分割!!

SELECT time , SUBSTRING_INDEX(time,'/',1) FROM class

//time = 上課時間(星期/節次/教室)

以下 表1 為 原資料表格式(舉例)

表1

開課序號 課程代碼 上課時間(星期/節次/教室) 上課時間(星期) 上課時間(節次) 上課時間(教室)
0001 E0123 一 / 1,2 / A 123
正課 E0123 二 / 3 / A 123

結果為

圖1 結果

https://ithelp.ithome.com.tw/upload/images/20200422/20124377MSgCcsTzDX.png

但是問題來了!!
我想將查詢出來的結果放入原資料表的另一個欄位中
有用過利用變數存查詢到的結果 => DECLARE 變數
但會一直報error,查過資料好像因為phpmyadmin版本問題,所以不能使用!!QQ
找過其他方法 => INSERT INTO 但也不行QQ

想問各位大大~還可以怎麼寫,來避開版本問題的嗎?

問題2

若完成問題一後,想將課程放入網頁中對應table格子內(像新增課至課表一樣)
一天有14堂課,1周有7天,總共98堂,每格有相對應的編號(課程編碼)

但是有甚麼方法可以讓資料表的指定資料【字串】變成指定的【值】呢?
EXAMPLE:
星期:
一(字串) = 0(INT),二 = 14,
節次:
2(為TEXT非INT) = 2(INT可做運算相加)

例如:一 1 = 1 = 0(一)+1(第一節)
二 3 = 17 = 14(二)+3(第三節)

找過CAST CONVERT

SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table

但只是將 100(字串)轉成100(INT)
參考至 https://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/
想問各位大大還有甚麼方法可以解決嗎? QQ

以下 表2 為 最後想呈現的格式

表2

開課序號 課程代碼 上課時間(星期/節次/教室) 上課時間(星期) 上課時間(節次) 上課時間(教室) 課表編碼
0001 E0123 一 / 1,2 / A 123 1,2 A 123 1,2
正課 E0123 二 / 3 / A 123 3 A 123 17

在這邊謝謝大大們閱讀我的提問!!!感謝您~

0
rogeryao
iT邦大師 5 級 ‧ 2020-04-23 18:24:38
最佳解答

假設最多有三個節次

create table class(A varchar(30), B varchar(30), C varchar(30));            
insert into class values ('0001','E0123','一 / 1,2 / A 123');
insert into class values ('正課','E0123','二 / 3 / A 123');
insert into class values ('正課','E0124','二 / 4,5 / A 123');
insert into class values ('正課','E0124','二 / 6,7,8 / A 123');
insert into class values ('正課','F0123','三 / 3 / B 123');
insert into class values ('正課','F0124','三 / 4,5 / B 123');
insert into class values ('正課','F0124','三 / 6,7,8 / B 123');
insert into class values ('正課','G0123','四 / 3 / C 123');
insert into class values ('正課','G0124','四 / 4,5 / C 123');
insert into class values ('正課','G0124','四 / 6,7,8 / C 123');
insert into class values ('正課','H0123','五 / 3 / D 123');
insert into class values ('正課','H0124','五 / 4,5 / D 123');
insert into class values ('正課','H0124','五 / 6,7,8 / D 123');
insert into class values ('正課','K0123','六 / 3 / E 123');
insert into class values ('正課','K0124','六 / 4,5 / E 123');
insert into class values ('正課','K0124','六 / 6,7,8 / E 123');
insert into class values ('正課','M0123','日 / 9 / F 123');
insert into class values ('正課','M0124','日 / 10,11 / F 123');
insert into class values ('正課','M0124','日 / 12,13,14 / F 123');
select 
A as '開課序號',
B as '課程代碼',
C as '上課時間(星期/節次/教室)',
C1 as '上課時間(星期)',
C2 as '上課時間(節次)',
C3 as '上課時間(教室)',
case 
when num=0 then C2
when num<>0 and
     -- 一個節次
     LENGTH(REPLACE(C2,' ','')) - LENGTH( REPLACE(REPLACE(C2,' ',''),',','') )=0
then 
     CONVERT(REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C2,' ',''), ',', 1)), ',', 1)),signed)+14*num 
when num<>0 and
     -- 二個節次
     LENGTH(REPLACE(C2,' ','')) - LENGTH( REPLACE(REPLACE(C2,' ',''),',','') )=1
then 
     CONCAT_WS(',',
     CONVERT(REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C2,' ',''), ',', 1)), ',', 1)),signed)+14*num,
     CONVERT(REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C2,' ',''), ',', 2)), ',', 1)),signed)+14*num 
     )
when num<>0 and
     -- 三個節次
     LENGTH(REPLACE(C2,' ','')) - LENGTH( REPLACE(REPLACE(C2,' ',''),',','') )=2
then 
     CONCAT_WS(',',
     CONVERT(REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C2,' ',''), ',', 1)), ',', 1)),signed)+14*num,
     CONVERT(REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C2,' ',''), ',', 2)), ',', 1)),signed)+14*num,
     CONVERT(REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C2,' ',''), ',', 3)), ',', 1)),signed)+14*num 
     )
else '0' 
end as '課表編碼'   

from (
select A,B,C,
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 1)), '/', 1)) as C1,
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 2)), '/', 1)) as C2,
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 3)), '/', 1)) as C3,
case 
when 
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 1)), '/', 1))='一' 
then 0
when 
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 1)), '/', 1))='二' 
then 1
when 
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 1)), '/', 1))='三' 
then 2
when 
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 1)), '/', 1))='四' 
then 3
when 
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 1)), '/', 1))='五' 
then 4
when 
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 1)), '/', 1))='六' 
then 5
when 
REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(REPLACE(C,' ',''), '/', 1)), '/', 1))='日' 
then 6 end as num
from class
where 1=1
) as TempX
order by A,B,C

Demo

謝謝大大精闢的解答!!
完全達到我的需求!!我也會將其修飾~
還有想問大大您,
我把REVERSE拔掉,在做一些SUBSTRING的修飾也可以跑出一樣的結果!!
大大REVERSE的用意是甚麼阿?~ 因為一行就用了好幾次!!
我查過資料後REVERSE是倒轉的用法,對嗎??

rogeryao iT邦大師 5 級 ‧ 2020-04-24 20:54:31 檢舉

1.使用 REVERSE 是參考 :
https://www.iteye.com/blog/wddpwzzhao123-2423608
取出第幾個字串較直覺.

2.SUBSTRING 的定義 : https://www.w3schools.com/sql/func_mysql_substring.asp
用 SUBSTRING 解決這個問題可能不太容易 , 因為"節次"可能是變動的長度 ,
"節次"內有幾個逗號是不確定的 , 每個值要取出後判斷星期幾在做運算 , 太複雜了.

3.去除 REVERSE 的解法如下 :
Demo

0

問題一

DECLARE 是在程序化用的語法。用在你這邊根本就不對。
如果你要單純去更新的你新欄位值。
可以用如下的sql

UPDATE 資料庫 SET 新欄位值=SUBSTRING_INDEX(time,'/',1)

這樣就可以一次更新了。

問題二

說真的,我看不懂你的需求。

謝謝大大!!! 已成功更新欄位了~

0
ice bear
iT邦新手 4 級 ‧ 2020-04-23 09:48:34

問題二我猜你可能是要由左至右由上到下編號
讓值可以順利的呈現成課表的樣子?

星期一 星期二 星期三 星期四 星期五 ...
0 14 28 42 56 ...
1 15 29 43 57 ...
... ... ... ... ... ...

如果是的話,建議你用個temp table,存放你星期對應到的數字是多少,用JOIN的方式mapping
例如temp table可能長這樣

星期 對應值
0
14
28
42
56
70
84

語法可能像這樣

UPDATE A.課表編碼 = B.對應值 + A.上課時間(節次)
FROM 資料表 A
JOIN Temp Table B ON A.上課時間(星期) = B.星期

不過這會有個問題,因為你有些節次是多值,要額外在 上課時間(節次) 的地方做點處理
更好的做法應該是不應有多值,資料庫要正規化

謝謝大大提供這個方法~我會將這個方法好好學起來~沒有想到可以這樣做!!

我要發表回答

立即登入回答