Sqlite整數型態轉成實數型態之方法

Sqlite的count函數係整數值，如計算2個count次數之比率時，整數相除會得0，要怎麼變成實數，方法如下：

with a as (select count(*) as ct from 輔具評估日誌 where 接案日 = '0.0')
    ,b as (select count(*) as ct from 輔具評估日誌)
select a.ct, b.ct, a.ct*1.0/b.ct from a, b

以另存新表來簡化資料存取語法

有些表格通常只有代碼，為了取得名稱，需要先串接代碼以取得名稱，而串接語法往往造成SQL中包含冗長的串接語法，去處理代碼化名稱的動作，為了簡化資料存取語法，應先將串接代碼取得名稱的SQL結果另存成新表，可簡易運用以下 SQL達成：

create table new_table as select ... from old_table

釋例：

原本取代碼的 SQL：

select a.*, b.名稱 as 科目名稱
from 明細分類帳 a, 基本代碼檔 b
where 1=1
and a.機關名稱=b.機關名稱
and a.送審年 = b.送審年
and a.總帳科目 = b.代碼
and cast(a.送審月 as decimal) = cast(b.送審月 as decimal)

建立包含代碼名稱之新表：

create table 新明細分類帳 as
select a.*, b.名稱 as 科目名稱
from 明細分類帳 a, 基本代碼檔 b
where 1=1
and a.機關名稱=b.機關名稱
and a.送審年 = b.送審年
and a.總帳科目 = b.代碼
and cast(a.送審月 as decimal) = cast(b.送審月 as decimal)
之後原來的 SQL即簡化為：

select *
from 新明細分類帳

將數值表徵文字淨化成數值，以SQLite為例

有些原始資料儲存數值的文字表徵，例如"99,550"，包含有雙引號及千分位號，而無法進行數值運算，因此需要將數值表徵文字轉成數值的資料淨化程序。
首先要先把數值表徵文字轉成您所運用SQL系統型態轉換機制能處理的數值表徵文字，以SQLite為例，該系統無法自動去除「"」（雙引號）或「,」（千分位號）字元，只能辨識「99550」形式的數值表徵文字，所以我們先運用 replace 函數刪除字串內的雙引號及千分位號：

select 建議金額, replace(replace(建議金額, '"', ''), ',', '')  建議金額數值
from suggesions

承上，文字轉換成系統型態轉換機制能處理的數值表徵文字，就利用 cast 表示式求得數值：

select 建議金額, cast(replace(replace(建議金額, '"', ''), ',', '') as decimal) 建議金額數字

from suggesions

最後，以上式確認轉換無誤後，再利用 update 陳述式將表格內的數值表徵文字轉型成數值，完成資料淨化：

update suggesions
set 建議金額 = cast(replace(replace(建議金額, '"', ''), ',', '') as decimal)

分組次數分配的SQL

select max(建議金額), min(建議金額)
from suggesions

select round(建議金額/10000-0.5)*10000, count(*)
from suggesions
where 建議金額 != 0
group by round(建議金額/10000-0.5)*10000

表格欄位資料類型轉換

有時候匯入表格時欄位是文字型態的數字，造成該欄位是文字型態，以下的方法可以將表格欄位資料類型轉換，以 SQLite 為釋例：

1.原表格更名：

ALTER TABLE 支出傳票明細檔 RENAME TO tmp;

2.建立新表：

CREATE TABLE 支出傳票明細檔 ( `主管機關代碼` ,...,`傳票號碼` as integer,... )

3.原表資料經型態轉換插入至新表：

INSERT INTO 支出傳票明細檔( `主管機關代碼` ,...,`傳票號碼` as integer,...) 

SELECT `主管機關代碼` ,...,cast(`傳票號碼` as decimal),... FROM tmp;

4.刪除原表格：

DROP TABLE tmp;

以一般資料表運算式(Common Table Expression)分解SQL

1.一般資料表運算式可作為暫存表

一個包含許多子查詢的複雜查詢，邏輯上相當難以分析，若先分解成數個邏輯較簡單的暫存資料表再行串接出結果查詢式，則較易分析，但是其缺點是無法以一個查詢表示；而一般資料表運算式(CTE, Common Table Expression)可對查詢結果作命名，等價於暫存資料表之效果，又兼具分解子查詢的效果。

範例1：單一的 CTE 用法

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
)
Select *
From OrdersTable

範例二：2兩個 CTE 作 Join

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
),
OrderDetailsTable as
(
Select OrderID as '訂單ID', ProductID as '產品ID', UnitPrice as '價格'
from dbo.[Order Details]
)

Select *
From OrdersTable A inner join OrderDetailsTable B
on A.訂單ID = B.訂單ID

遞迴表格

範例三：CTE 遞迴 (參考：黑暗大的部落格文章)

資料清單


Code：

--如果CTE的WITH不在第一列, 前方要加上;
;WITH 公司組織結果(部門名稱, 直屬部門, 層級, 排序欄位)
AS
(
--Recursive CTE分為兩個部分, 第一部分為Anchor Member
--指不會被遞迴呼叫到的部分
SELECT 部門名稱, 直屬部門, 0, CONVERT(nvarchar(128), 部門ID)
FROM dbo.公司組織 WHERE 直屬部門=N'ROOT'

UNION ALL

--UNION ALL後方的部分稱為Recursive Member, 會在遞迴過程中反覆執行,
--直到無任何查詢結果為止
SELECT P.部門名稱, P.直屬部門, B.層級+1,
CONVERT(nvarchar(128), B.排序欄位 + '-' + CONVERT(nvarchar(128), P.部門ID))
FROM dbo.公司組織 P, 公司組織結果 B
WHERE P.直屬部門=B.部門名稱
)
SELECT (REPLICATE(' ', 層級) + 部門名稱) as '部門名稱', 層級, 排序欄位
FROM 公司組織結果
ORDER BY 排序欄位




另一個 CTE 遞迴範例 (參考：Journey to SQL Authority with Pinal Dave)

Code：

USE AdventureWorks
GO
WITH Emp_CTE AS (
--找出 ManagerID 為 NULL 的資料當作 Anchor Member
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL

UNION ALL

--之後以 Anchor Member 為依據遞迴查詢
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

ORACLE SQL的字串處理

字元類型是用來表示現實世界自然語言及文字的資料類型，
電腦大部份的運算資源一半以上是用來字串處理。

本章的編排先來認識字元及字串在電腦上是如何表示，
之後再來研究字串的基本操作，
然後獨立一個章節來說明字串模式比對。

抽取子字串

ORACLE語法:

substr(string str, int start, int length)

start 表示起算點，length 是子字串的長度。

T-SQL:

SUBSTRING (value_expression ,start_expression ,length_expression )

位置起算點是 1。

抽取子字串

substring('thomas' from 2 for 3) => hom

substring(string from pattern) => text

抽取匹配 posix 正則表示式的子字串

substring('thomas' from '...$') => mas

substring(string from pattern for escape) => text

抽取匹配SQL正則表示式的子字串

substring('thomas' from '%#"o_a#"_' for '#') => oma



字元(char)
~~~~~~~~~~
在電腦和電信領域的術語中，字元是一個資訊單位。
在使用字母系統或音節文字等的文字的自然語言中，
它大約對應為一個音位或符號。

字元的例子有字母、數字系統或標點符號。

字元通常也包含控制字元，
控制字元並不對應到自然語言中的特定符號，
而是對應到語言中一些用來處理文句的東西。
例如列印機或顯示器的命令，如 return 或 tab。

字串(String)
~~~~~~~~~~~~
字串是由零或多個字元所組成的有限序列，
通常是程式語言中表示文字的資料類型。
其最基本的儲存方式是陣列和鏈結。

字串相等通常定義為長度相等，
並且各個對應位置上的字元都相等。

模式匹配是指找出字串 p 在字串 q 中首次出現的位置。

我們很少操作單一字元，
通常以字串整體作為操作單位，
例如：在字串中尋找某個子字串、求取一個子字串、
在字串的某個位置上插入一個子字串以及刪除一個子字串等。

字元類型(char)
~~~~~~~~~~~~~~
SQL 定義了兩種基本的字元類型：

table.字元類型
名稱                             描述
================================ ======================
character varying(n), varchar(n) 可變長度，有長度限制
character(n), char(n)            固定長度，不足補空白
text                             可變長度，無長度限制
================================ ======================

對 character varying(n) 和 character(n) 而言，
n 是一個正整數，指類型最多儲存 n 字元長字串。
若嘗試儲存更長的字串到此類型的字串裡則會產生一個錯誤，

除非超出長度的字元都是空白，這種情況下該字串將被截斷為最大長度。

若儲存字串的長度比 n 短，
類型 character 將會用空白填滿不足的長度；
而類型為 character varying 的數值將儲存較短的字串，
所以 character varying 比較節省空間。

若強制將字串轉換成 character(n) 或者 character varying(n)，
那麼超過長度的字串將被截斷成 n 個字元，而不會拋出錯誤。

char(n) 和 varchar(n) 分別是 character(n) 和 character varying(n) 的別名，

沒有長度定義 n 的 character 等於 character(1)；

若不帶長度定義 n 的 character varying，
則該類型接受任何長度的字串。

text 類型，它可以儲存任何長度的字串。
儘管類型 text 不是 SQL 標準，但被很多 SQL 資料庫系統支援。

character 字串都用空白填充到指定的長度 n，
並且以這種方式儲存和顯示。

填充空白在語意上並不重要。

在比較兩個 character 字串時，填充空白會被忽略，
在轉型成其它字串類型時，character 字串空白會被刪除。
但在 character varying 和 text 字串中，
結尾填充空白語意上是有含義的。

這些類型的儲存需求是 4 字元加上實際的字串，
若是 character 的話再加上填充空白。

長字串會自動被系統壓縮，
因此實際儲存空間需求可能會更少些。
長字串也會儲存在後台資料表裡面，
這樣就不會干擾對短字串值的快速訪問。
允許儲存的最長字串大概是 1 GB。

若想儲存沒有特定上限之長字串，
那麼使用 text 或者沒有長度定義詞的 character varying，
而不要選擇一個任意長度限制。

這三種類型之間沒有性能差別，
只不過 character 類型會增加填充空白儲存長度，
沒特別需要的話應該使用 text 或者 character varying，
而不要使用 character。

[string_constant]字串常數
~~~~~~~~~~~~~~~~~~~~~~~~~
SQL 裡的一個字串常數是用單引號「'」包圍的任意字串，
其中包括英數字元 (a-z、A-Z 和 0-9) 以及符號，
如驚歎號 「!」、記號「@」 和數字符號「#」。
一個字串常數如下：

'色不異空空不異色'

空字串用不含任何東西的兩個單引號「''」來表示。

單引號由兩個連續單引號表示，
如敝人女兒蘋果的玩具，其英文句如下：

'Apple''s toy'

任何其它跟在反斜線後面的特殊字元其原來作用會跳脫而只有字面意義，
所以單引號可用反斜線「\\」也來跳脫，因此上例可改寫如下：

'Apple\\'s toy'

其它跳脫字元如下：
\\b 表退格字元，\\f 表進紙，\\n 表換行，\\r 表回車，\\t 表分欄。

反斜線字元由兩個反斜線表「\\\\」表示。

八進制數表示語法為 \\xxx，xxx 是 ascii 碼的八進位代碼。

編碼為零的字元不能出現在字串常數中。

兩個至少有一個換行空白分隔字串常數會被連接，並當成一個常數處理。
比如：

select 'foo'
'bar';

等效於

select 'foobar';

而

select 'foo'      'bar';

是非法語法。

table[escape_chars].SQL 跳脫字元表
字串  符號        例句
===== =========== =========================
''    單引號      'Apple''s toy'
/'    單引號      'Apple\'s toy'
\b    退格字元
\f    進紙
\n    換行
\r    回車
\t    分欄
\\    反斜線
\xxx  八進位代碼
===== =========== =========================

字串函數和運算子
~~~~~~~~~~~~~~~~
字串包括 character， character varying，和 text 類型值。
除非另外說明，所有下面列出的函數都可以處理這些類型，

不過要小心的是，在使用 character 類型的時候，
填充空白的潛在影響。

通常這裡描述的函數也能用於非字串類型，
我們只要先把那些資料轉化為字串資料表現形式就可以了。
有些函數還可以處理位串類型。

SQL 定義了一些字串函數，
它們有指定的語法，
裡面是用某種特定的關鍵字，
而不是逗號來分隔參數。

字串連接(concatenation)
~~~~~~~~~~~~~~~~~~~~~~~
字串串接，又稱字串相加、連接、串連、相連，
指將兩個字串作首尾相接。

例如「色不異空」和「空不異色」串接後便成了「色不異空空不異色」。

SQL 使用「||」作為字串連接運算子，
如下例：

code[concatenation.sql].
select '色不異空' || '空不異色' => '色不異空空不異色'
::

bit_length
~~~~~~~~~~
字串所佔的 byte 長度，如下例：

select bit_length('jose') => 32

char_length
~~~~~~~~~~~
char_length(string) 或 character_length(string)
 
字串中的字元個數，如下例：  

char_length('jose') => 4

octet_length
~~~~~~~~~~~~
octet_length(string) => integer

字串中的字元數

octet_length('jose') => 4

convert
~~~~~~~
convert(string using conversion_name)

使用指定的轉換名字改變編碼。轉換可以透過 create conversion 定義。
當然系統裡有一些預定義的轉換名字。
參閱 table 9-7 獲取可用的轉換名。  

convert('sql tutorial' using iso_8859_1_to_utf_8)

傳回 unicode (utf-8) 編碼的 'sql tutorial'

lower
~~~~~
lower(string)
 
把字串轉化為小寫

lower('Tom') => tom

upper
~~~~~
upper(string) => text

把字串轉化為大寫。

upper('tom') => TOM

position
~~~~~~~~
position(substring in string) => integer

定義的子字串的位置

position('om' in 'thomas') => 3

overlay
~~~~~~~
overlay(string placing string from integer \[for integer\])

替換子字串  

overlay('txxxxas' placing 'hom' from 2 for 4) => thomas

replace(char, str1, str2)

position
~~~~~~~~
position(substring in string) => integer

指定的子字串的位置  

position('om' in 'thomas') => 3

trim
~~~~
trim(\[leading | trailing | both\] \[characters\] from string) => text

從字串 string 的開頭、結尾或兩邊
刪除只包含 characters 「預設是一個空白」的最長的字串。

trim(both 'x' from 'xtomxx')i => tom

substring
~~~~~~~~~
substring(string \[from integer\] \[for integer\]) => text  

substr(str, int, int) => text

抽取子字串  

substring('thomas' from 2 for 3) => hom

substring(string from pattern) => text

抽取匹配 posix 正則表示式的子字串  

substring('thomas' from '...$') => mas

substring(string from pattern for escape) => text  

抽取匹配SQL正則表示式的子字串

substring('thomas' from '%#"o_a#"_' for '#') => oma

T-SQL:

SUBSTRING (value_expression ,start_expression ,length_expression )

位置起算點是 1。

其它
~~~~
set sqlblanklines on
讓 SQL\*plus 可辨識含空白行的字串值。