select查詢之六：別名與拼接

在查詢工作中，為了查詢資料的高可讀性，給欄位或組合欄位起一個別名非常重要。
根據人們讀取資料的習慣性，在查詢的資料中，連線符“||”也經常會用到。

1、別名：

1》直接 空格接別名：

SQL> select sal,comm,sal+comm income

  2  from emp

  3  where comm is not null;

       SAL       COMM     INCOME

---------- ---------- ----------

      1600        300       1900

      1250        500       1750

      1250       1400       2650

      1500          0       1500

2》As 別名：

SQL> select sal,comm,sal+comm as income

  2   from emp

  3  where comm is not null;

       SAL       COMM     INCOME

---------- ---------- ----------

      1600        300       1900

      1250        500       1750

      1250       1400       2650

      1500          0       1500

3》直接空格雙引號括起別名：

SQL> select sal,comm,sal+comm "shou ru"

  2  from emp

  3  where comm is not null;

       SAL       COMM    shou ru

---------- ---------- ----------

      1600        300       1900

      1250        500       1750

      1250       1400       2650

      1500          0       1500

4》As 雙引號括起別名：

SQL> select sal,comm,sal+comm as "shou ru"

  2   from emp

  3   where comm is not null;

       SAL       COMM    shou ru

---------- ---------- ----------

      1600        300       1900

      1250        500       1750

      1250       1400       2650

      1500          0       1500

2、 拼接：

拼接：“||”可以拼接多個成分，而函式 concat只能拼接兩個成分。

1》直接使用“||”:

SQL> select 'select * from hr_' || lower(tname) ||';'

  2  from tab;

'SELECT*FROMHR_'||LOWER(TNAME)||';'

------------------------------------------------

select * from hr_bonus;

select * from hr_dept;

select * from hr_emp;

select * from hr_employees1;

select * from hr_salgrade;

select * from hr_sl_base;

select * from hr_worker;

7 rows selected.

2》使用 q’[...]’ :

SQL> select last_name || q'['s Salary is:]' ||salary as income

  2  from employees;

INCOME

------------------------------------------------------------------------------

OConnell's Salary is:2600

Grant's Salary is:2600

Whalen's Salary is:4400

Hartstein's Salary is:13000

Fay's Salary is:6000

Mavris's Salary is:6500

Baer's Salary is:10000

Higgins's Salary is:12008

Gietz's Salary is:8300

King's Salary is:24000

Kochhar's Salary is:17000

107 rows selected.

3》使用concat:

SQL> select concat('select * from hr_',lower(tname)) ||';'

  2   from tab;

CONCAT('SELECT*FROMHR_',LOWER(TNAME))||';'

------------------------------------------------

select * from hr_bonus;

select * from hr_dept;

select * from hr_emp;

select * from hr_employees1;

select * from hr_salgrade;

select * from hr_sl_base;

select * from hr_worker;

7 rows selected.

附：
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

完畢，謝謝觀賞！