说起WITH 语句,除了那些第一次听说WITH 语句的人,大部分人都觉得它是用来做
递归查询的。其实那只是它的一个用途而已,它的本名正如我们标题写的那样,叫做:公共
表表达式(Common Table Expression),从字面理解,大家觉得它是用来干嘛的呢?
其实,它是用来定义临时集合的。
Sql代码
WITH TEMP(ID,USER) AS(
SELECT ‘01’,’SAM’ FROM DUAL
UNION ALL
SELECT ‘02’,’MIKE’ FROM DUAL
UNION ALL
SELECT ‘03’,’TOM’ FROM DUAL
UNION ALL
SELECT ‘04’,’JANE’ FROM DUAL
)
SELECT * FROM TEMP;
WITH TEMP(ID,USER) AS(
SELECT ‘01’,’SAM’ FROM DUAL
UNION ALL
SELECT ‘02’,’MIKE’ FROM DUAL
UNION ALL
SELECT ‘03’,’TOM’ FROM DUAL
UNION ALL
SELECT ‘04’,’JANE’ FROM DUAL
)
SELECT * FROM TEMP;
WITH可以定义多个结果集,中间用逗号分隔。
(这种写法更加符合普通思维的逻辑,写出来的代码更加容易理解)
WITH 语句是为复杂的查询为设计的,的确是这样的,下面我们举个复杂的例子,想提高技术的朋友可千万不能错过。考虑下面的情况:
Sql代码
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,--姓名
DEGREE INTEGER NOT NULL,--学历(1、专科2、本科3、硕士4、博士)
STARTWORKDATE date NOT NULL,--入职时间
SALARY1 FLOAT NOT NULL,--基本工资
SALARY2 FLOAT NOT NULL--奖金
);
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,--姓名
DEGREE INTEGER NOT NULL,--学历(1、专科2、本科3、硕士4、博士)
STARTWORKDATE date NOT NULL,--入职时间
SALARY1 FLOAT NOT NULL,--基本工资
SALARY2 FLOAT NOT NULL--奖金
);
假设现在让你查询一下那些1、学历是硕士或博士2、学历相同,入职年份也相同,但是工资(基本工资+奖金)却比相同条件员工的平均工资低的员工。(哈哈,可能是要涨工资),不知道你听明白问题没有?该怎么查询呢?我们是这样想的:
1、查询学历是硕士或博士的那些员工得到结果集1,如下:
Sql代码
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4);
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN