sql語句格式化時間格式「sql在線格式化快捷鍵」

今天是SQL系列的第三講，我們會講解條件查詢，文本處理，百分比，行數限制，格式化以及子查詢。

條件查詢

IF條件查詢

#if的語法
IF(expr1,expr2,expr3)
#示例
SELECT IF(sva=1,"男","女") AS s FROM table_name 
WHERE sva != '';

CASE WHEN條件查詢

case when 可以實現if函數的功能，同時也可以聯合各類聚合函數使用。

# case when也可以實和if一樣的功能
SELECT CASE 
WHEN sva=1 THEN '男' 
　　ELSE '女' 
END AS s 
FROM table_name
WHERE sva != '';


#case when可以聯合聚合函數等使用
SELECT count(DISTINCT CASE
                          WHEN sva=1 THEN 'id'
                          ELSE 'null'
                      END) AS s
FROM TABLE_NAME
WHERE sva != '';

文本處理

SUBSTR()字符串截取

substr語法詳解：

substr(strings|express,m,[n])

strings|express ：被截取的字符串或字符串表達式

m 從第m個字符開始截取

n 截取後字符串長度為n

示例：
  select substr('abcdefg',3,4) from dual;
  # 結果是cdef


  select substr('abcdefg',-3,4) from dual;
  # 結果efg
  
  select substr('abcde',2),substr('abcde',-2),substr('abcde',2,3),substr('abcdewww',-7,3) from dual;
  # 結果是bcde、de、bcd、bcd

字符串拼接

1.使用特殊操作符拼接

#ACESS和SQL Serve使用+
SELECT vend_name + ' (' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;


#DB2,Oracle, PostgreSQL,SQLite ,Open Office Base使用||
SELECT vend_name || ' (' || vend_country || ')' 
FROM Vendors
ORDER BY vend_name;

2.CONCAT()函數拼接

SELECT Concat(vend_name, ' (', vend_country, ')') 
FROM Vendors
ORDER BY vend_name; 

SPLIT()字符串分割

語法結構

split(str, regex) – Splits

str：需要分割的字符

regex：以什麼符號進行分割

1.基本用法

split('a,b,c,d',',')
# 得到的結果：
["a","b","c","d"]

2.截取字符串中的某個值

當然，我們也可以指定取結果數組中的某一項

split('a,b,c,d',',')[0]


# 得到的結果：
a

3.特殊字符的處理

特殊分割符號

regex 為字符串匹配的參數，所以遇到特殊字符的時候需要做特殊的處理

# 例3: "." 點
split('192.168.0.1','.')


# 得到的結果：
[]
# 正確的寫法：
split('192.168.0.1','\.')
# 得到的結果：
["192","168","0","1"]

LENGTH()返回字符串長度

SELECT length(vend_name) vend_len
FROM Vendors
ORDER BY vend_name;

LOWER()/UPPER()將字符串轉換為小寫或大寫

SELECT vend_name, 
LOWER(vend_name) AS vend_name_lowcase,
UPPER(vend_name) AS vend_name_upercase 
FROM Vendors
ORDER BY vend_name;

REPLACE()字符串替換

#將adress字段中的區替換為”嘔“
select *,replace(address,'區','嘔') AS rep
from test_tb

LEFT()/RIGHT()返回字符串左邊或右邊的字符

select left(CONTRACT_NAME,2)
from
gb_t_contract 
where 1=1;
#從字符表達式最左邊一個字符開始返回指定數目的字符.
#若 b 的值大於 a 的長度,則返回字符表達式的全部字符a.如果 b 為負值或 0,則返回空字符串.




select left('2323232',9) ;
# 返回值為空

LTRIM()/RTRIM()/TRIM()去掉字符串左邊/右邊或全部空格

select ltrim('   sample ') from table;
# 返回結果：'sample '
select rtrim('   sample ') from table;
# 返回結果：'   sample'
select trim('   sample ') from table;


# 返回結果：'sample'

SOUNDEX() 返回字符串SOUNDEX值

#近似匹配
SELECT cust_name, cust_contact
 FROM Customers
 WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

CAST數據類型轉換

# 將str類型的dt字段轉換為int類型的
select cast(dt as int)  dt
from table

取百分比

percentile()

語法格式：

percentile_approx(DOUBLE col, p ,[B])) 近似中位數函數

percentile(DOUBLE col, p ) 中位函數

前者多了一個參數B，後者無參數，其餘語法一致。

求近似的第pth個百分位數，p必須介於0和1之間，返回類型為double，但是col字段支持浮點類型。參數B控制內存消耗的近似精度，B越大，結果的準確度越高。默認為10,000。當col字段中的distinct值的個數小於B時，結果為準確的百分位數。

select percentile(mmr,0.3) as 30_percentile,
percentile_approx(mmr,0.5) 50_percentile
from match_table

限制行數

LIMIT的用法

select account_id,account_name
from table
limt 100

格式化顯示

FORMAT()數據格式化

FORMAT() 函數用於對字段的顯示進行格式化。

SQL FORMAT() 語法：

SELECT FORMAT(column_name,format) FROM table_name;

FORMAT（X,D）：強制保留D位小數，整數部分超過三位的時候以逗號分割，並且返回的結果是string類型的。

 SELECT FORMAT(100.3465,2),FORMAT(100,2),FORMAT(,100.6,2);
 # 結果分別：100.35,100.00，100.60

子查詢

1.子查詢條件過濾

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');

2.子查詢作為計算字段

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
         FROM Orders
         WHERE Orders.cust_id = Customers.cust_id) AS orders 
FROM Customers
ORDER BY cust_name;