MSSQL MYSQL ORACLE

特殊好用 SQL 語法

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

2 21487

透過簡易 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