sql="select data1 From test where data2='123' AND data3 IN ('No','2001','2002','2003','2004','2005','2006','2007','2008','2009')"
原本以上這樣寫輸出的排序沒問題...
更改後加上 GROUP_CONCAT & GROUP BY
sql="select GROUP_CONCAT(data1) From test where data2='123' AND data3 IN ('No','2001','2002','2003','2004','2005','2006','2007','2008','2009')GROUP BY YY"
後第一筆資料排序就出錯了
變成 2009,2008,2007,2006,2004,2003,2002,2001,NO 這樣
其他筆資料也都跑錯位了
不知道是不是語法寫錯? 還是我不了解語法執行順序?
已邀請的邦友 {{ invite_list.length }}/5
CREATE TABLE test
(
id int,
name nvarchar(10),
data nvarchar(10)
);
insert into test
values
(1,'lili','456'),
(2,'lili','123'),
(3,'lulu','789'),
(4,'jam','222'),
(5,'lili','777'),
(6,'jam','111');
-- group_concat : case 1
select name,group_concat(data order by data SEPARATOR ',' ) as combdata
from test
group by name
order by name
-- group_concat : case 2
select name,group_concat(data order by data SEPARATOR ',' ) as combdata
from test
group by name
order by name desc
-- group_concat : case 3
select name,group_concat(data order by data desc SEPARATOR ',' ) as combdata
from test
group by name
order by name
-- group_concat : case 4
select name,group_concat(data order by data desc SEPARATOR ',' ) as combdata
from test
group by name
order by name desc
-- group_concat : case 5
select name,group_concat(data SEPARATOR ',' ) as combdata
from test
group by name
Demo 看看差異在哪,
就知要不要排序了
謝謝大大的範例!!讓我受益良多!!
但想再問如果我的data那欄如果是 > data-01 有 (-)符號
這樣該如何寫?
我給他分號資料會變成 data-01 data-01 並無法順利產出資料
EX
select name,group_concat('data-01' order by data SEPARATOR ',' ) as combdata
from test
group by name
order by name desc
非常感謝你!
select name,group_concat(`data-01` order by `data-01` SEPARATOR ',' ) as combdata
from test
group by name
order by name desc
data-01 左右邊的 ` 用鍵盤左邊的 Tab 按鍵上方那個鍵
data-01 建議改成 data_01 以免之後下 SQL 時出現不可預期的錯誤,
還要再花時間除錯
感謝!!!成功了!!
排序為什麼不用ORDER BY呢?
https://www.mysqltutorial.org/mysql-order-by/
When you use the SELECT statement to query data from a table, the result set is not sorted. It means that the rows in the result set can be in any order.
當你沒有下 order by 時, 查詢的結果會是 無序 的. 這是關聯式資料庫都這樣,不只MySQL.
所以我們通常會有一個 遞增 (或是遞減) 的欄位,或是有時間戳的欄位,以方便做 order by.
iThome鐵人賽
看影片追技術