1. 在WHERE子句中參考別名列

1.1. 當表里的某些列沒有被恰當命名的時候，這個技巧尤其有用

1.2. sql

select sal as salary, comm as commission
  from emp
 where salary ＜ 5000

1.3. 內嵌視圖

1.3.1.  sql

select *
   from (
 select sal as salary, comm as commission
   from emp
        ) x
  where salary ＜ 5000

1.3.2. 聚合函式

1.3.3. 標量子查詢

1.3.4. 視窗函式

1.3.5. 別名

2. CONCAT函式

2.1. 串聯多列的值

2.2. 在DB2、Oracle和PostgreSQL 中，“||”是CONCAT函式的快捷方式

2.3. 在SQL Server中則為“+”

3. 隨機回傳若干行記錄

3.1. 如果ORDER BY子句使用數值常量，那么就需要按照SELECT串列里的順序來排序

3.2. 如果ORDER BY子句使用了函式，那么就需要按照該函式的回傳值來排序，而函式回傳的值是根據結果集里的每一行計算而來的

3.3. MySQL

3.3.1.  sql

  select ename,job
 from emp
  order by rand() limit 5

3.4. PostgreSQL

3.4.1.  sql

 select ename,job
   from emp
  order by random() limit 5

3.5. Oracle

3.5.1.  sql

 select *
   from (
  select ename, job
    from emp
   order by dbms_random.value()
        )
   where rownum ＜= 5

3.6. SQL Server

3.6.1.  sql

select top 5 ename,job
   from emp
  order by newid()

4. 把Null值轉換為實際值

4.1. COALESCE函式

4.1.1.  sql

select coalesce(comm,0)
   from emp

4.1.2. 更方便、更簡潔

4.2. CASE

4.2.1. sql

 select case
       when comm is not null then comm
       else 0
       end
  from emp

4.3. 適用于所有的資料庫

5. 依據子串排序

5.1. 按照職位欄位的最后兩個字符對檢索結果進行排序

5.2. DB2、MySQL、Oracle和PostgreSQL

5.2.1. sql

select ename,job
  from emp
 order by substr(job,length(job)-2)

5.3. SQL Server

5.3.1. sql

select ename,job
  from emp
 order by substring(job,len(job)-2,2)

6. 排序時對Null值的處理

6.1. Oracle能夠讓你在無須修改非Null值資料的情況下方便地把Null值排到最前面或者最后面，其他資料庫得添加一個輔助列

6.1.1. Oracle 9i及后續版本NULLS FIRST和NULLS LAST來決定Null值應該排到前面還是后面

6.2. 輔助列

6.2.1. 只存在于查詢陳述句里，而不存在于表中

6.2.2. 目的是讓你能夠識別出Null值，并控制其排在最前面還是最后面

6.3. 對于非Oracle解決方案的查詢陳述句，其內嵌視圖X會回傳如下結果集

6.4. sql

select ename,sal,comm,
       case when comm is null then 0 else 1 end as is_null
  from emp

6.5. ENAME    SAL       COMM    IS_NULL

SMITH    800                           0
ALLEN   1600          300          1
WARD    1250         500          1
JONES   2975                          0
MARTIN  1250       1400         1
BLAKE   2850                          0
CLARK   2450                          0
SCOTT   3000                          0
KING    5000                           0
TURNER  1500          0           1
ADAMS   1100                       0
JAMES    950                          0
FORD    3000                         0
MILLER  1300                        0

7.  依據條件邏輯動態調整排序項

7.1. 如果JOB等于SALESMAN，就要按照COMM來排序；否則，按照SAL排序

7.2.  sql

select ename,sal,job,comm
   from emp
  order by case when job = 'SALESMAN' then comm else sal end

7.3. sql

select ename,sal,job,comm,
       case when job = 'SALESMAN' then comm else sal end as ordered
 from emp
order by 5

7.4. ENAME             SAL JOB             COMM    ORDERED

TURNER           1500 SALESMAN           0          0
ALLEN            1600 SALESMAN         300        300
WARD             1250 SALESMAN         500        500
SMITH             800 CLERK                                800
JAMES             950 CLERK                                950
ADAMS            1100 CLERK                             1100
MARTIN           1250 SALESMAN        1300    1300
MILLER           1300 CLERK                               1300
CLARK            2450 MANAGER                        2450
BLAKE            2850 MANAGER                        2850
JONES            2975 MANAGER                        2975
SCOTT            3000 ANALYST                          3000
FORD             3000 ANALYST                           3000
KING             5000 PRESIDENT                        5000

標籤：其他

上一篇：Spark中RDD的Transformation算子

下一篇：返回列表