MSSQL MYSQL ORACLE

特殊好用 SQL 語法

李建毅 (Joe Lee) 2019/10/17 10:34:42
234

透過簡易 SQL 語法呈現 rownum 技術

       

應該會有跟我一樣的朋友有這樣的需求,就是需要對select查詢的集合先進行一次排序(如:按新增時間排序)然後讓生成的rownum的順序與排序後的順序一致,但是用原始的rownum無法完成先排序後按排序後的順序分頁的需求

 

MySQL 實現方法

使用MySQL變量;在某些情況下,無法通過修改程序來實現時,可以考慮這種方法。

缺點,@x 變量是connection 級的,再次查詢的時候需要初始化。 一般來說PHP等B/S應用沒有這個問題。

但C/S如果connection一直保持則要考慮set @x=0

 

SELECT @rownum:=ifnull(@rownum,0)+1 as rownum,
t.* 
FROM mysql.test t

 

 

 

MSSQL 實現方法、Oracle 實現方法

若要在每個資料列前面新增資料列號碼資料行,請新增有 ROW_NUMBER 函數的資料行,在此情況下名為 rownum。 

您必須將 ORDER BY 子句移到 OVER 子句。

 

SELECT ROW_NUMBER() OVER(ORDER BY sm_docno) AS rownum,
         a.sm_docno, a.sm_description ,a.sm_message_en
    FROM aaa a ;
 

 

透過簡易 SQL 語法呈現 STRING 轉 LIST

 

MSSQL 實現方法

SQL2016以前常會有一個需求就是將一個字串用分隔符號拆解後轉成Table來運用,而這個需求在SQL2016中內建了

String_Split函數是分割使用指定的分隔符號的字元運算式,以前大多得自己寫Function,作法如下

 

select value from STRING_SPLIT('7322,7323,7324,7325', ',')
 

 

透過簡易 SQL 語法呈現  LIST 轉 STRING

 

MSSQL 實現方法

SQL Server 2017則推出了將資料集組成字串的串接功能(STRING_AGG)

 

select STRING_AGG(value,',') value, group_name from (
    select '1234' value, 'a' group_name
    union all 
    select '5678' value, 'a' group_name
    union all 
    select '9012' value, 'b' group_name
    union all 
    select '3456' value, 'b' group_name
    )a group by group_name

 

 

MSSQL 實現方法

MySQL GROUP_CONCAT函數將分組中的字符串與各種選項進行連接

 

select group_concat(value) value, group_name from (
    select '1234' value, 'a' group_name
    union all 
    select '5678' value, 'a' group_name
    union all 
    select '9012' value, 'b' group_name
    union all 
    select '3456' value, 'b' group_name
    )a group by group_name

 

參考

ROW_NUMBER (Transact-SQL)

https://docs.microsoft.com/zh-tw/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017

ROW_NUMBER

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

STRING_SPLIT (Transact-SQL)

https://docs.microsoft.com/zh-tw/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

STRING_AGG (Transact-SQL)

https://docs.microsoft.com/zh-tw/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

GROUP_CONCAT(expr)

https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat

李建毅 (Joe Lee)