「SQL」修訂間的差異

出自ProgWiki
跳至導覽 跳至搜尋
→‎MsSQL
→‎MsSQL
行 10: 行 10:
 
     ORDER BY [排序欄位] [ASC/DESC]
 
     ORDER BY [排序欄位] [ASC/DESC]
 
</source>
 
</source>
===MsSQL===
+
===MS-SQL===
====Type A====
+
====MS-SQL Type A====
 
* 無排序,在關聯式查詢時,可能導致每次查詢同一分頁的資料時,出來的資料都不一樣。
 
* 無排序,在關聯式查詢時,可能導致每次查詢同一分頁的資料時,出來的資料都不一樣。
 
<source lang="tsql">
 
<source lang="tsql">
行 33: 行 33:
 
</source>
 
</source>
  
====Type B====
+
====MS-SQL Type B====
* 有排序,又稱【夾擠式分頁查詢】。
+
* 有排序,又稱【夾擠式分頁查詢】。MS-SQL 2000以前,常見的分頁用語法。
 
<source lang="tsql">
 
<source lang="tsql">
 
SELECT * FROM  
 
SELECT * FROM  
行 50: 行 50:
 
as [資料表别名2]  
 
as [資料表别名2]  
 
ORDER BY [排序欄位] [DESC/ASC]
 
ORDER BY [排序欄位] [DESC/ASC]
 +
</source>
 +
 +
====MS-SQL Type C====
 +
* MS-SQL 2005以後才有RANK()語法。
 +
<source lang="tsql">
 +
SELECT * FROM
 +
(
 +
    SELECT rank() over ( ORDER BY [排序欄位] [ASC/DESC] ) as RankNumber, * FROM
 +
    (
 +
        SELECT [欄位1, 欄位2, ...]
 +
        FROM [資料表]
 +
        WHERE [篩選條件]   
 +
    )
 +
    as [資料表别名1]
 +
) as [資料表别名2]
 +
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁]
 
</source>
 
</source>
  

於 2008年4月9日 (三) 09:26 的修訂

Connection Strings

SQL分頁select

分頁前SQL

SELECT [欄位1, 欄位2, ...] 
    FROM [資料表]
    WHERE [篩選條件] 
    ORDER BY [排序欄位] [ASC/DESC]

MS-SQL

MS-SQL Type A

  • 無排序,在關聯式查詢時,可能導致每次查詢同一分頁的資料時,出來的資料都不一樣。
SELECT [欄位1, 欄位2, ...] FROM [資料表]
    WHERE [主要索引欄位] IN
    (
        SELECT * FROM 
        (
            SELECT TOP [每頁長度*第幾頁] [主要索引欄位]
                FROM [資料表]
                WHERE [篩選條件]     
        )
        as [資料表别名1]
        WHERE [主要索引欄位] NOT IN
        (
            SELECT TOP [每頁長度] [主要索引欄位] 
                FROM [資料表]
                WHERE [篩選條件] 
        )
    )

MS-SQL Type B

  • 有排序,又稱【夾擠式分頁查詢】。MS-SQL 2000以前,常見的分頁用語法。
SELECT * FROM 
(
    SELECT TOP [每頁長度] * FROM 
    (
        SELECT TOP [每頁長度*第幾頁] [欄位1, 欄位2, ...] 
        FROM [資料表]
        WHERE [篩選條件]     
        ORDER BY [排序欄位] [DESC/ASC]
    )
    as [資料表别名1]
    ORDER BY [排序欄位] [ASC/DESC]
)
as [資料表别名2] 
ORDER BY [排序欄位] [DESC/ASC]

MS-SQL Type C

  • MS-SQL 2005以後才有RANK()語法。
SELECT * FROM 
(
    SELECT rank() over ( ORDER BY [排序欄位] [ASC/DESC] ) as RankNumber, * FROM
    (
        SELECT [欄位1, 欄位2, ...] 
        FROM [資料表]
        WHERE [篩選條件]     
    )
    as [資料表别名1]
) as [資料表别名2]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁]

MySQL

SELECT [欄位1, 欄位2, ...] 
        FROM [資料表]
        WHERE [篩選條件]     
        ORDER BY [排序欄位] [ASC/DESC]
        LIMIT [每頁長度] OFFSET [每頁長度*第幾頁]

跨資料庫轉換

SQL資料隱碼

FAQ

當Table或欄位的名稱衝到SQL預設的保留字時

  • MS-SQL 或 MS-Access 使用 [] 來將衝突的Table或欄位的名稱包起來。
  • Oracle 或 IBM-DB2 或 PostgreSQL 使用雙引號字元 " 來將衝突的Table或欄位的名稱包起來。
  • MySQL 使用反單引號字元 ` 來將衝突的Table或欄位的名稱包起來。

無法解析 equal to 作業中 "Chinese_Taiwan_Stroke_CI_AS" 與 "Latin1_General_CI_AI" 之間的定序衝突。

  • 在Select子句的後面加上 COLLATE Chinese_Taiwan_Stroke_CI_AS ,做字串定序的轉換。