在查询工作中，为了查询数据的高可读性，给字段或组合字段起一个别名非常重要。
根据人们读取数据的习惯性，在查询的数据中，连接符“||”也经常会用到。

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;

完毕，谢谢观赏！