各位大大想請教關於SQL的問題~
小弟是用 xampp 的 phpmyadmin 來執行資料庫的SQL
最近在做將上課時間的字串進行分割時，有查了一些資料
發現SUBSTRING_INDEX 可以達到我想要的分割!!
SELECT time , SUBSTRING_INDEX(time,'/',1) FROM class
//time = 上課時間(星期/節次/教室)
以下 表1 為 原資料表格式(舉例)
開課序號 | 課程代碼 | 上課時間(星期/節次/教室) | 上課時間(星期) | 上課時間(節次) | 上課時間(教室)
------------- | ------------- | ------------- | ------------- | ------------- |
0001 | E0123 | 一 / 1,2 / A 123 | | | |
正課 | E0123 | 二 / 3 / A 123 | | | |
結果為
但是問題來了!!
我想將查詢出來的結果放入原資料表的另一個欄位中
有用過利用變數存查詢到的結果 => DECLARE 變數
但會一直報error，查過資料好像因為phpmyadmin版本問題，所以不能使用!!QQ
找過其他方法 => INSERT INTO 但也不行QQ
想問各位大大~還可以怎麼寫，來避開版本問題的嗎?
若完成問題一後，想將課程放入網頁中對應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 為 最後想呈現的格式
開課序號 | 課程代碼 | 上課時間(星期/節次/教室) | 上課時間(星期) | 上課時間(節次) | 上課時間(教室) | 課表編碼
------------- | ------------- | ------------- | ------------- | ------------- | ------------- |
0001 | E0123 | 一 / 1,2 / A 123 | 一 | 1,2 | A 123 | 1,2 |
正課 | E0123 | 二 / 3 / A 123 | 二 | 3 | A 123 | 17 |
在這邊謝謝大大們閱讀我的提問!!!感謝您~
已邀請的邦友 {{ invite_list.length }}/5
假設最多有三個節次
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
謝謝大大精闢的解答!!
完全達到我的需求!!我也會將其修飾~
還有想問大大您，
我把REVERSE拔掉，在做一些SUBSTRING的修飾也可以跑出一樣的結果!!
大大REVERSE的用意是甚麼阿?~ 因為一行就用了好幾次!!
我查過資料後REVERSE是倒轉的用法，對嗎??
1.使用 REVERSE 是參考 :
https://www.iteye.com/blog/wddpwzzhao123-2423608
取出第幾個字串較直覺.
2.SUBSTRING 的定義 : https://www.w3schools.com/sql/func_mysql_substring.asp
用 SUBSTRING 解決這個問題可能不太容易 , 因為"節次"可能是變動的長度 ,
"節次"內有幾個逗號是不確定的 , 每個值要取出後判斷星期幾在做運算 , 太複雜了.
3.去除 REVERSE 的解法如下 :
Demo
問題一
DECLARE 是在程序化用的語法。用在你這邊根本就不對。
如果你要單純去更新的你新欄位值。
可以用如下的sql
UPDATE 資料庫 SET 新欄位值=SUBSTRING_INDEX(time,'/',1)
這樣就可以一次更新了。
問題二
說真的，我看不懂你的需求。
問題二我猜你可能是要由左至右由上到下編號
讓值可以順利的呈現成課表的樣子?
星期一 | 星期二 | 星期三 | 星期四 | 星期五|...
--------------|:-----:|-----:| ----:|------------------------|...
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.星期
不過這會有個問題，因為你有些節次是多值，要額外在 上課時間(節次) 的地方做點處理
更好的做法應該是不應有多值，資料庫要正規化
iThome鐵人賽
看影片追技術