SQL检索数据基础操作讲解

7.2.1 从表中查询所有行和列

需求:

检索表里所有行的数据。

解决方法:

通过SQL的关键字*来匹配到所有行和列,结合SELECT FROM 即可满足需求。

Oracle、Sql server、Mysql:

SELECT*

FROM emp;

等价于:

SELECT empno, ename, job, mgr, hiredate, sal, comm,deptnoFROM emp;

执行结果:

empno

ename

job

mgr

hiredate

sal

comm

deptno

7369

SMITH

CLERK

7902

1980/12/17 0:00:00

800.00

 

20

7499

ALLEN

SALESMAN

7698

1981/2/20 0:00:00

1600.00

300.00

30

7521

WARD

SALESMAN

7698

1981/2/22 0:00:00

1250.00

500.00

30

7566

JONES

MANAGER

7839

1981/4/2 0:00:00

2975.00

 

20

7654

MARTIN

SALESMAN

7698

1981/9/28 0:00:00

1250.00

1400.00

30

7698

BLAKE

MANAGER

7839

1981/5/1 0:00:00

2850.00

 

30

7782

CLARK

 

MANAGER

7839

1981/6/9 0:00:00

2450.00

 

10

7788

SCOTT

ANALYST

7566

1987/7/13 0:00:00

3000.00

 

20

7839

KING

PRESIDENT

 

1981/11/17 0:00:00

5000.00

 

10

7844

TURNER

SALESMAN

7698

1981/9/8 0:00:00

1500.00

0.00

30

7876

ADAMS

CLERK

7788

1987/7/13 0:00:00

1100.00

 

20

7900

JAMES

CLERK

7698

1981/12/3 0:00:00

950.00

 

30

7902

FORD

ANALYST

7566

1981/12/3 0:00:00

3000.00

 

20

7934

MILLER

CLERK

7782

1982/1/23 0:00:00

1300.00

 

10

7.2.2 从表中查询部分行

需求:

查询雇员表emp里员工号是7782的员工的详细信息。

解决方法:

先找出emp表的员工号的字段名,再结合SELECT FROM WHERE来实现。其中这里通过where关键字来限制检索的行。

Oracle、Sql server、Mysql:

SELECT*

FROM emp

WHERE empno=7782;

执行结果:

empno

ename

job

mgr

hiredate

sal

comm

deptno

7782

CLARK

MANAGER

7839

1981/6/9 0:00:00

2450.00

 

10

 

7.2.3 查询满足某个条件行

需求:

查询雇员表emp中所有的部门号等于10的行。

解决方法:

先找出emp表的部门号的字段名,再结合SELECT FROM WHERE来实现。其中的WHERE即是在加过滤条件,多部门表进行筛选,只取部门号为10的数据。

Oracle、Sql server、Mysql:

SELECT*

FROM emp

WHERE deptno=10;

执行结果:

empno

ename

job

mgr

hiredate

sal

comm

deptno

7782

CLARK

MANAGER

7839

1981/6/9 0:00:00

2450.00

 

10

7839

KING

PRESIDENT

 

1981/11/17 0:00:00

5000.00

 

10

7934

MILLER

CLERK

7782

1982/1/23 0:00:00

1300.00

 

10

注:

1 这里字段的判断支持诸如不等于(“<>”)、等于(“=”),大于(“>”)、小于(“<”) 、,

大于等于(“>=”)、小于等于(“<=”),不等(“!”)等关系比较符。

2 上述的比较符对表字段的类型有要求,一般适用于数值类型。

3 当然我们也可以在where之后结合其它条件再过滤,后面会有案例。

7.2.4 从表中查询部分列

需求:

查询雇员表emp中所有的员工编号、员工名称、职位情况。

解决方法:

先找出emp表的工编号、员工名称、职位对应的字段名,再结合SELECT FROM 即可满足需求。

Oracle、Sql server、Mysql:

SELECT empno, ename, job

FROM emp;

执行结果:

empno

ename

job

7369

SMITH

CLERK

7499

ALLEN

SALESMAN

7521

WARD

SALESMAN

7566

JONES

MANAGER

7654

MARTIN

SALESMAN

7698

BLAKE

MANAGER

7782

CLARK

MANAGER

7788

SCOTT

ANALYST

7839

KING

PRESIDENT

7844

TURNER

SALESMAN

7876

ADAMS

CLERK

7900

JAMES

CLERK

7902

FORD

ANALYST

7934

MILLER

CLERK

7.2.5 给字段取个有意义的名字

需求:

查询雇员表emp里部门编号是10的员工编号、员工名称、职位情况,并给员工编号起个别名“员工号”,给员工名称起个别名“员工名”,给职位起个名字“职位名”。

解决方法:

先找出emp表的工编号、员工名称、职位对应的字段名,再结合SELECT column as “别名”FROM table的方式解决。

Oracle、Sql server、Mysql:

SELECT empnoas”员工号”,enameas”员工名”,jobas”职位名”

FROM emp

WHERE deptno =10;

也可以省略掉as写成如下的:

SELECT empnoas”员工号”,enameas”员工名”,jobas”职位名”

FROM emp

WHERE deptno =10;

执行结果:

员工号

员工名

职位名

7782

CLARK

MANAGER

7839

KING

PRESIDENT

7934

MILLER

CLERK

注: 这里为了演示,将字段名起个别名是中文, 一般情况下不会这么做。因为表名或者字段是用英文定义的,如果名字比较长了或者不好理解,可以起个别名代替。比如有个表叫employee,可以起个别名emp,这个表里有个字段叫salary,可以起个别名叫sal。

一般我们这么写:

SELECT empnoas id,sal salary,comm commission

FROM emp

WHERE deptno=10;

7.2.6 结合where使用别名

需求:

查询雇员表emp里工资大于3000的员工编号、员工名称、职位、工资情况,并以工资的别名“salary”进行过滤。

解决方法:

先找出emp表的工编号、员工名称、职位、工资对应的字段名分别为empno、ename、job、sal,结合SELECT column as “别名”FROM table的方式给字段sal起个别名salary,这里需要用个只是,内敛表,即将查询的结果集用“()”包裹起来起个别名,也可以理解成用个虚拟的表。再在这个内敛表的基础上过滤salary大于3000的记录即可。

Oracle、Sql server、Mysql:

SELECT*FROM

(

SELECT empno,ename,job,salsalary

FROM emp

)X

WHERE salary>3000

执行结果:

empno

ename

job

salary

7839

KING

PRESIDENT

5000.00

7.2.7 拼接列的值

需求:

查询雇员表emp里部门编号是10员工信息,以员工名称“’job title is”职位的形式输出。示例:KING’s job title is PRESIDENT

解决方法:

首先定位到emp表和涉及到字段员工名称、职位,再考虑两列的拼接,选择字符串函数或则“+”完成,最后加上where条件过滤部门编号等于10。这里不同的数据库实现方法不同。以下分别来讲。

Mysql:

SELECT concat(ename,”‘s job title is:”,job)as EnameConn

FROM emp

WHERE deptno =10;

Sql server:

SELECT ename+”’s job title is:’+jobas EnameConn

FROM emp

Oracle:

SELECT ename||”’s job title is:’||jobas EnameConn

FROM emp

shenl

执行结果:

EnameConn

CLARK’s job title is:MANAGER

KING’s job title is:PRESIDENT

MILLER’s job title is:CLERK

7.2.8 查询语句里执行条件判断

需求:

查询雇员表emp里的员工名称、工资、工资分类状态。这里的工资分类状态按照以下规则显示:如果工资小于等于2000则显示UNDERPAID(即报酬比较低的),如果工资大于4000时显示OVERPAID(即报酬较高),其它情况显示NORMAL(正常水平)。

解决方法:

首先定位到emp表和涉及到字段员工名称、工资,再考虑考虑到工资状态是个计算出来的列,所以先梳理工资分类状态这个“列”的逻辑,跟表里的工资字段有关,这里是做 if else判断,注意到纯SQL里没有if else,但有case when,所以我们选取这个关键字。case when 条件1 then value1 case when 条件2 then value2 else value3 end as 别名。

Mysql、SQL server、Oracle:

SELECT ENAME,SAL,

CASEWHEN SAL<=2000THEN’UNDERPAID’

WHEN SAL>2000THEN’OVERPAID’

ELSE’NORMAL’ENDAS STATUS

FROM emp

执行结果:

ENAME

SAL

STATUS

SMITH

800.00

UNDERPAID

ALLEN

1600.00

UNDERPAID

WARD

1250.00

UNDERPAID

JONES

2975.00

OVERPAID

MARTIN

1250.00

UNDERPAID

BLAKE

2850.00

OVERPAID

CLARK

2450.00

OVERPAID

SCOTT

3000.00

OVERPAID

KING

5000.00

OVERPAID

TURNER

1500.00

UNDERPAID

ADAMS

1100.00

UNDERPAID

JAMES

950.00

UNDERPAID

FORD

3000.00

OVERPAID

MILLER

1300.00

UNDERPAID

注:

case when还可以如下方式编写:

select ename,sal,job,

case jobwhen’CLERK’then’小职员’

when’SALESMAN’then’销售员’

when’MANAGER’then’经理’

when’ANALYST’then’分析师’

when’PRESIDENT’then’董事长’

else”其它”

endas jobtitle

from emp

执行结果:

ename

sal

job

jobtitle

SMITH

800.00

CLERK

小职员

ALLEN

1600.00

SALESMAN

销售员

WARD

1250.00

SALESMAN

销售员

JONES

2975.00

MANAGER

经理

MARTIN

1250.00

SALESMAN

销售员

BLAKE

2850.00

MANAGER

经理

CLARK

2450.00

MANAGER

经理

SCOTT

3000.00

ANALYST

分析师

KING

5000.00

PRESIDENT

董事长

TURNER

1500.00

SALESMAN

销售员

ADAMS

1100.00

CLERK

小职员

JAMES

950.00

CLERK

小职员

FORD

3000.00

ANALYST

分析师

MILLER

 

 

小职员

这种case 字段A when value1的方式适合字段有可穷举的情况,而case when 字段条件A则比这种更灵活。

7.2.9 返回的字段在某个范围内的数据

需求:

查询雇员表emp里部门编号在10和30的部门编号、雇员名称、职位名称。

解决方法:

使用数据库里IN关键字来限制员工表里的deptno。

Mysql、Sql server、Oracle:

SELECT ename,job

FROM emp

WHERE deptnoIN (20,30)

执行结果:

ename

job

deptno

SMITH

CLERK

20

ALLEN

SALESMAN

30

WARD

SALESMAN

30

JONES

MANAGER

20

MARTIN

SALESMAN

30

BLAKE

MANAGER

30

SCOTT

ANALYST

20

TURNER

SALESMAN

30

ADAMS

CLERK

20

JAMES

CLERK

30

FORD

ANALYST

20

7.2.10 通过多条件组合返回数据

需求:

查询雇员表emp里部门编号在10和30且职位是经理“MANAGER”的的雇员名称、职位名称。

解决方法:

使用数据库里IN关键字来限制员工表里的deptno。

Mysql、Sql server、Oracle:

SELECT ename,job

FROM emp

WHERE deptno IN (20,30)AND job=’MANAGER’

执行结果:

ename

job

JONES

MANAGER

BLAKE

MANAGER

注: 这里字段的逻辑条件里常用的有and、or,注意这里的优先级,如果想改变优先级,可以通过“()”来改变。

7.2.11 限制返回的行数

需求:

查询5条雇员表emp里的雇员信息。

解决方法:

使用数据库里内置的限制行数返回的函数来解决。

Mysql:

SELECT*

FROM emplimit5;

Sql server:

SELECT TOP 5 *

FROM emp;

Oracle:

SELECT *

FROM emp

WHERErownum <=5

执行结果:

empno

ename

job

mgr

hiredate

sal

comm

deptno

7369

SMITH

CLERK

7902

1980/12/17 0:00:00

800.00

 

20

7499

ALLEN

SALESMAN

7698

1981/2/20 0:00:00

1600.00

300.00

30

7521

WARD

SALESMAN

7698

1981/2/22 0:00:00

1250.00

500.00

30

7566

JONES

MANAGER

7839

1981/4/2 0:00:00

2975.00

 

20

7654

MARTIN

SALESMAN

7698

1981/9/28 0:00:00

1250.00

1400.00

30

 

7.2.12 从表中随机返回N条记录

需求:

从雇员表emp里随机查询5条雇员的名称和工资信息。

解决方法:

使用数据库里内置的限制行数函数结合随机函数来解决。

Mysql:

SELECT ename,sal

FROM emp

orderbyrand()limit5;

Sql server:

SELECT TOP 5ename,sal

FROM emp

ORDER BYNEWID()

注: newid是SQL Server里的内置函数,因为newid()返回的是uniqueidentifier类型的唯一值,而且每次生成的值都不一样,所以能达到随机的效果。

Oracle:

SELECT *FROM

(

SELECT ename,sal

FROM emp

ORDERBYDbms_Random.value()

)

WHERErownum <=5

注: dbms_random是一个可以生成随机数值或者字符串的程序包。这个包有initialize()、seed()、terminate()、value()、normal()、random()、string()等几个函数,但返回随机值的函数value()是最常用的。

你可以直接访问这个随机数字的函数,它返回个0到1之间的一个小数。

SELECT Dbms_Random.value()AS RAND_VALUE

FROM DUAL;

执行结果:

RAND_VALUE

0.151493981662762

如果想生成1-100之间的随机数,你可以这么做:

SELECTTRUNC(Dbms_Random.value()*100)ASRAND_VALUE

FROM DUAL;

执行结果:

RAND_VALUE

42

7.2.13 从表中查询空值

需求:

从雇员表emp里查询所有奖金为空的员工名称、工资信息和奖金信息。

解决方法:

首先找到该段逻辑的涉及到的表名和字段名,emp表,字段ename,sal,comm,使用IS NULL关键字来来解决,这里的NULL是描述数据的特殊值,即不明确该值是什么,区别于空字符串“””,空字符串不等于NULL。

Mysql、Sql server、Oracle:

SELECT ename,sal,comm

FROM emp

WHERE commIS NULL

执行结果:

ename

sal

comm

SMITH

800.00

 

JONES

2975.00

 

BLAKE

2850.00

 

CLARK

2450.00

 

SCOTT

3000.00

 

KING

5000.00

 

ADAMS

1100.00

 

JAMES

950.00

 

FORD

3000.00

 

MILLER

1300.00

 

7.2.14 返回不重复的记录

需求:

从雇员表emp里查询所有不重复的职位信息。

解决方法:

这里用到SQL的DISTINCT关键字,即对重复的记录进行去重。

Mysql、Sql server、Oracle:

SELECTDISTINCT JOB

FROM emp

执行结果:

JOB

CLERK

SALESMAN

MANAGER

ANALYST

PRESIDENT

7.2.15 将空值转成其它值

需求:

从雇员表emp里查询所有奖金为空的员工名称、工资信息和奖金信息。这里如果奖金为空则转为0。

解决方法:

首先找到该段逻辑的涉及到的表名和字段名,emp表,字段ename,sal,comm,使用IS NULL关键字来来解决,如果comm为null则转为0 如果不是null则返回本身。

Mysql、Sql Server、Oracle:

SELECT ename,sal,

caseWHEN commISNULLTHEN0

ELSE comm

ENDAS comm

FROM emp

WHERE comm IS NULL

执行结果:

ename

sal

comm

SMITH

800.00

0

JONES

2975.00

0

BLAKE

2850.00

0

CLARK

2450.00

0

SCOTT

3000.00

0

KING

5000.00

0

ADAMS

1100.00

0

JAMES

950.00

0

FORD

3000.00

0

MILLER

1300.00

0

或者用下面的方法,这里每个数据库里的函数名各不同:

Mysql:

SELECT ename,sal,

ROUND(IFNULL(comm,0))as comm

FROM emp

WHERE comm IS NULL

Sql Server:

SELECT ename,sal,

ISNULL(comm,0)AS comm

FROM emp

WHERE comm IS NULL

Oracle:

SELECT ename,sal,

NVL(comm,0)AS comm

FROM emp

WHERE commISNULL

7.2.16 按照某个模式搜索

需求:

从雇员表emp里查询部门号是10或者20的员工名称,职位信息,他们要么员工名称里含有“I”要么他们的职位以“ER”结尾。

解决方法:

这里用到SQL的关键字“%”,注意如果字段里以“%匹配关键字%”则会匹配“匹配关键字”在字段里的任何地方,同理如果是“匹配关键字%”则是以“匹配关键字”开头来匹配字段,而“%匹配关键字”则是匹配以“匹配关键字”结尾来匹配字段。

Mysql、Sqlserver、Oracle:

SELECT ename,job

FROM emp

WHERE deptnoIN(10,20)

AND (enameLIKE’%I%’OR jobLIKE’%ER’)

执行结果:

ename

job

SMITH

CLERK

JONES

MANAGER

CLARK

MANAGER

KING

PRESIDENT

MILLER

CLERK

7.2.17 按照指定的次序返回查询结果

需求:

从雇员表emp里查询所有的员工名称,职位,奖金信息,这里要求奖金不为空的排在前面。

解决方法:

这里用到SQL的关键字 ORDER BY 以及ASC和DESC,order by是指定以某个或者某些字段排序,asc是指代以字母顺序排列而desc则是以字母降序排列。

Mysql、SqlServer:

SELECT ename,job,comm

FROM emp

orderby comm

执行结果:

ename

job

comm

SMITH

CLERK

 

JONES

MANAGER

 

BLAKE

MANAGER

 

CLARK

MANAGER

 

SCOTT

ANALYST

 

KING

PRESIDENT

 

ADAMS

CLERK

 

JAMES

CLERK

 

FORD

ANALYST

 

MILLER

CLERK

 

TURNER

SALESMAN

0.00

ALLEN

SALESMAN

300.00

WARD

SALESMAN

500.00

MARTIN

SALESMAN

1400.00

Oracle:

SELECT ename,job,comm

FROM emp

order by comm

执行结果:

ENAME

JOB

COMM

TURNER

SALESMAN

0.00

ALLEN

SALESMAN

300.00

WARD

SALESMAN

500.00

MARTIN

SALESMAN

1400.00

SCOTT

ANALYST

 

KING

PRESIDENT

 

ADAMS

CLERK

 

JAMES

CLERK

 

FORD

ANALYST

 

MILLER

CLERK

 

BLAKE

MANAGER

 

JONES

MANAGER

 

SMITH

CLERK

 

CLARK

MANAGER

 

注:因为Sql Server和Mysql默认以NULL的排在前面,而Oralce则默认以NULL的排在后面。

7.2.18 按照多个字段排序查询结果

需求:

从雇员表emp里查询所有的部门编号,员工名称,职位,工资信息,这里要求先以部门编号排序再以工资从高到低排序显示。

解决方法:

这里用到SQL的关键字 ORDER BY 以及ASC和DESC,order by是指定以字段deptno字符排序,以工资降序排列。

Mysql、Sql server、Oracle:

SELECT deptno,ename,job,sal

FROM emp

orderby deptno,commdesc

执行结果:

deptno

ename

job

sal

10

CLARK

MANAGER

2450.00

10

KING

PRESIDENT

5000.00

10

MILLER

CLERK

1300.00

20

SMITH

CLERK

800.00

20

JONES

MANAGER

2975.00

20

SCOTT

ANALYST

3000.00

20

ADAMS

CLERK

1100.00

20

FORD

ANALYST

3000.00

30

MARTIN

SALESMAN

1250.00

30

WARD

SALESMAN

1250.00

30

ALLEN

SALESMAN

1600.00

30

TURNER

SALESMAN

1500.00

30

BLAKE

MANAGER

2850.00

30

JAMES

CLERK

950.00

 

7.2.19 按照字符串对结果排序

需求:

从雇员表emp里查询所有的部门编号,员工名称,职位,工资信息,职位的后两位信息,这里要求截取job字段里的最后两位来排序。

解决方法:

这里用到SQL的字符串截取函数再结合order by来对结果进行排序。

Mysql:

SELECT deptno,ename,job,sal,substring(job,length(job)-1)AS last2word

FROM emp

orderby last2word

Oracle:

SELECTdeptno,ename,job,sal,substr(job,length(job)-1)ASlast2word

FROM emp

orderbylast2word

Sql serer:

SELECT deptno,ename,job,sal,substring(job,len(job)-1,2)AS last2word

FROM emp

order by last2word

执行结果:

deptno

ename

job

sal

last2word

30

ALLEN

SALESMAN

1600.00

AN

30

WARD

SALESMAN

1250.00

AN

30

MARTIN

SALESMAN

1250.00

AN

30

TURNER

SALESMAN

1500.00

AN

20

JONES

MANAGER

2975.00

ER

30

BLAKE

MANAGER

2850.00

ER

10

CLARK

MANAGER

2450.00

ER

10

KING

PRESIDENT

5000.00

NT

20

SMITH

CLERK

800.00

RK

20

ADAMS

CLERK

1100.00

RK

30

JAMES

CLERK

950.00

RK

10

MILLER

CLERK

1300.00

RK

20

SCOTT

ANALYST

3000.00

ST

20

FORD

ANALYST

3000.00

ST

7.2.20 按照字符串数字组合的排序

需求:

假设我们从雇员表emp里创建个视图,这里仅有一个字段,该字段叫data由员工名称和部门号拼接而成,我们想实现一个查询可以按照原来的部门编号逆序排序筛选出数据。

解决方法:

这里没有真正创建视图,因为用户权限问题,而是建了个表叫做tmp_v。字段拼接生成新字段见上面章节。这里要通过数据库里的translate和repacle结合来实现这个功能。

注:SQL Server、Mysql里参照网上实现了translate函数,详细见下。

Oracle:

createtable tmp_v

asSELECT ename||’ ‘|| deptnoasdata

from emp

SELECTdata,replace(translate(data,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’,’@@@@@@@@@@@@@@@@@@@@@@@@@@’),’@’,”)as deptnoFROM tmp_v

orderbyreplace(translate(data,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’,’@@@@@@@@@@@@@@@@@@@@@@@@@@’),’@’,”)desc

执行结果:

DATA

DEPTNO

BLAKE 30

30

TURNER 30

30

ALLEN 30

30

MARTIN 30

30

WARD 30

30

JAMES 30

30

SCOTT 20

20

JONES 20

20

SMITH 20

20

ADAMS 20

20

FORD 20

20

KING 10

10

MILLER 10

10

注:

1因为我们的ename里的名字都是大写的,所以这里translate函数里的第二个参数都是大写的,如果data里是小写的这里自然是小写。

2 translate函数有三个参数,第一个一般是字段名或则字符串的值,第二个是要匹配的字符组合,第三个是要装换成的字符。

3 replace函数一般有三个参数,第一个一般是字段名或则字符串的值,第二个要替换的字段,第三个是要替换成的值。

SQL Server:

可以在SQLServer里实现个自定义个函数叫translate, 参考至https://blog.sina.com.cn/s/blog_95cfa64601018akj.html具体实现见下:

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATE FUNCTION [dbo].[translate](

@string VARCHAR(MAX),

@from_str VARCHAR(MAX),

@to_str VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS

BEGIN

— 返回将(所有出现的)from_str中的每个字符替换为to_str中的相应字符以后的string。

— TRANSLATE是 REPLACE 所提供的功能的一个超集。

— 如果 from_str比 to_str 长,那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除,因为它们没有相应的替换字符。

— to_str不能为空。

— Oracle将空字符串解释为 NULL,并且如果TRANSLATE 中的任何参数为NULL,那么结果也是 NULL。

IF @string IS NULL OR @from_strIS NULL OR @to_str IS NULL

BEGIN

RETURN NULL;

END;

— 源长度与目标长度

DECLARE @FromLen INT, @ToLen INT;

SET @FromLen = LEN(@from_str);

SET @ToLen = LEN(@to_str);

— 准备用于返回的数值.

DECLARE @resultVal VARCHAR(MAX);

SET @resultVal =@string;

— 用于存储 本次需要替换的字符信息.

DECLARE @thisTimeReplace CHAR(1);

— 从后向前依次替换.

WHILE @FromLen > 0

BEGIN

— 取得本次即将要替换的字符.

SET @thisTimeReplace =SUBSTRING(@from_str, @FromLen, 1);

IF CHARINDEX(@thisTimeReplace,@from_str)<@FromLen

BEGIN

— 假如当前这个要替换的字符,在前面还有,那么这里就不替换了

— 原因,为了支持

— SELECT TRANSLATE(‘2KRW229′,’1234567890’ || ‘2KRW229’, ‘1234567890’)

— 这样的效果.

— 向前处理上一个

/*

补充说明:

1 理论上TRANSLATE函数的@from_str参数和@to_str参数的长度要一致,即一一映射。比如@from_str=’0123′,@to_str=’abcd’

2 该步骤旨在找到在@from_str参数里要替换的的字符重复指定了,比如’0123XYZ23’,这里23是重复指定了,对于重复指定的要舍弃所以需要在该步时需要将@FromLen锁定到字符Z的位置即7

*/

SET @FromLen =@FromLen – 1;

CONTINUE;

END

IF @FromLen >@ToLen

BEGIN

–from_str 比 to_str长,那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除,因为它们没有相应的替换字符。

SET @resultVal =REPLACE(@resultVal,SUBSTRING(@from_str, @FromLen, 1),”);

END

ELSE

BEGIN

— from_str中的每个字符替换为to_str中的相应字符以后的string

–SELECT dbo.TRANSLATE(‘2KRW229’, ‘12345678902KRS229′,’12345678902’)这里用S替换时其实无效

SET @resultVal =REPLACE(@resultVal, @thisTimeReplace,SUBSTRING(@to_str, @FromLen, 1));

END;

— 处理完当前字符后,向前处理上一个.

SET @FromLen =@FromLen – 1;

END;

— 依次处理完毕后,返回结果.

RETURN @resultVal;

END

create view tmp_v

AS SELECT ename+’ ‘+cast(deptno as varchar)as data

from emp

SELECT data,replace(dbo.translate(data,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’,’@@@@@@@@@@@@@@@@@@@@@@@@@@’),’@’,”)as deptno FROM tmp_v

order byreplace(dbo.translate(data,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’,’@@@@@@@@@@@@@@@@@@@@@@@@@@’),’@’,”)desc

执行结果:

DATA

DEPTNO

ALLEN 30

30

WARD 30

30

MARTIN 30

30

BLAKE 30

30

TURNER 30

30

JAMES 30

30

FORD 20

20

SMITH 20

20

ADAMS 20

20

SCOTT 20

20

JONES 20

20

CLARK 10

10

KING 10

10

Mysql:

可以在Mysql里实现个自定义个函数叫translate,具体实现见下:

DROP FUNCTIONIFEXISTS shenl.translate;

CREATE FUNCTION shenl.`translate`(

stringToTranslate VARCHAR(256),

from_str VARCHAR(256),

to_str VARCHAR(256)) RETURNSvarchar(256)CHARSET utf8

BEGIN

DECLARE resultValVARCHAR(256);

DECLARE FromLenINT;

DECLARE ToLenINT;

DECLARE thisTimeReplaceVARCHAR(1);

SET FromLen=LENGTH(from_str);

SET ToLen=LENGTH(to_str);

SET resultVal= stringToTranslate;

IF stringToTranslateISNULLOR from_strISNULLOR to_strISNULLTHEN

RETURNNULL;

END IF;

loop_label: LOOP

#因为是从后往前替换,所以FromLen=0时循环要结束了。

IF FromLen<=0THEN

LEAVE loop_label;

END IF;

#from_str里从后往前每次取1个字符

SET thisTimeReplace=SUBSTRING(from_str, FromLen,1);

#如果在from_str字符串里重复指定了要替换的字符,则舍弃。即如果from_str的值是1234567890fat29,则29是要舍弃掉的。

#即FromLen要锁定到13

IFINSTR(from_str,thisTimeReplace)< FromLenTHEN

SET FromLen= FromLen -1;

ITERATE loop_label;

ELSE

#如果from_str的长度大于ToLen则以ToLen的长度为标准,此时from_str里多提供的字符会被替换为”

IF FromLen> ToLen THEN

SET resultVal=REPLACE(resultVal,SUBSTRING(from_str, FromLen,1),”);

#如果from_str的长度等于ToLen则以ToLen对应的字符替换from_str里多的字符

ELSE

SET resultVal=REPLACE(resultVal, thisTimeReplace,SUBSTRING(to_str, FromLen,1));

END IF;

SET FromLen= FromLen -1;

END IF;

END LOOP;

RETURN resultVal;

END;

create view tmp_v

ASSELECTCONCAT(ename,’ ‘,deptno)as data

from emp

SELECT data,replace(translate(data,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’,’@@@@@@@@@@@@@@@@@@@@@@@@@@’),’@’,”)as deptno

FROM tmp_v

orderbyreplace(translate(data,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’,’@@@@@@@@@@@@@@@@@@@@@@@@@@’),’@’,”)desc

执行结果:

data

deptno

ALLEN 30

30

MARTIN 30

30

WARD 30

30

BLAKE 30

30

JAMES 30

30

TURNER 30

30

SCOTT 20

20

ADAMS 20

20

SMITH 20

20

JONES 20

20

FORD 20

20

MILLER 10

10

KING 10

10

CLARK 10

10

7.2.21 处理空值的排序

需求:

查询雇员表emp里的员工编号、员工名、奖金信息,这里需要将comm字段为空的排在前面。

解决方法:

需要对comm字段进行特殊处理,比如进行转换,然后再排序,而有的数据库可以在排序时指定null的顺序。如oracle里字段排序时可以指定nulls first或则nulls last

Oracle:

SELECTempno,ename,comm

FROM emp

ORDERBY commnullsfirst

执行结果:

EMPNO

ENAME

COMM

7369

SMITH

 

7782

CLARK

 

7902

FORD

 

7900

JAMES

 

7876

ADAMS

 

7566

JONES

 

7698

BLAKE

 

7934

MILLER

 

7788

SCOTT

 

7839

KING

 

7844

TURNER

0.00

7499

ALLEN

300.00

7521

WARD

500.00

7654

MARTIN

1400.00

Sql Server:

SELECT empno,ename,CASEWHEN comm IS NULL THEN -1ELSE comm END AS comm

FROM emp

ORDER BY comm

Mysql:

SELECT empno,ename,CASEWHEN comm ISNULLTHEN-1ELSE comm ENDAS comm

FROM emp

ORDERBY comm

Sqlserver和Mysql的执行结果见下:

empno

ename

comm

7369

SMITH

-1.00

7566

JONES

-1.00

7698

BLAKE

-1.00

7782

CLARK

-1.00

7788

SCOTT

-1.00

7839

KING

-1.00

7876

ADAMS

-1.00

7900

JAMES

-1.00

7902

FORD

-1.00

7934

MILLER

-1.00

7844

TURNER

0.00

7499

ALLEN

300.00

7521

WARD

500.00

7654

MARTIN

1400.00

7.2.22 根据数据项的值排序

需求:

查询雇员表emp里的员工编号、员工名、工作信息、奖金信息,这里需要对工作是“MANAGER”和“SALESMAN”的按照comm降序排序其他员工按照员工编号降序排列。

解决方法:

这里需要在order by后接case when之类的条件判断以调整排序规则。order by可以跟多个字段,排在前面的规则会被优先应用。

Oracle:

SELECTempno,ename,job,comm

FROM emp

ORDERBYCASEWHEN job IN (‘SALESMAN’,’MANAGER’)THEN commELSE empnoENDDESC

执行结果:

EMPNO

ENAME

JOB

COMM

7782

CLARK

MANAGER

 

7698

BLAKE

MANAGER

 

7566

JONES

MANAGER

 

7934

MILLER

CLERK

 

7902

FORD

ANALYST

 

7900

JAMES

CLERK

 

7876

ADAMS

CLERK

 

7839

KING

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐