7.6.1 生成自增值
需求:
通过SQL生成一个1到1000条记录.
解决方法:
通过CTE的递归来实现该需求.
SQLServer:
?
1
2
3
4
5
6
7
8
9
10
11
DECLARE @startINT, @endINT
SELECT @start=1, @end=1000
;WITH NumberSequence( Number) AS
(
SELECT @start ASNumber
UNION ALL
SELECT Number + 1
FROM NumberSequence
WHERE Number <@end
)
SELECT * FROM NumberSequence OPTION(MaxRecursion 1000)
执行结构:
Number
1
2
3
4
5
6
7
8
9
10
…
Oracle:
?
1
2
3
4
5
6
7
WITH t(num)AS (
SELECT 1 FROM DUAL
UNION ALL
SELECT t.num+1
FROM t WHERE t.num<100
)
SELECT * FROM t;
执行结果:
Num
1
2
3
4
5
6
7
8
9
10
…
Mysql(8.0及以上版本):
?
1
2
3
4
5
6
7
WITH RECURSIVE cte (num) AS
(
SELECT 1
UNION ALL
SELECT num+1 FROM cte WHERE num <100
)
SELECT * FROM cte;
执行结果:
Num
1
2
3
4
5
6
7
8
9
10
…
7.6.2 遍历字符串里的每个值
需求:
打印出ename为’King’的名字里每一个字母,每个字母占一行.
解决方法:
通过自增表和emp表先cross join(笛卡尔积),然后再通过ename的len(ename的长度)进行过滤,最终得到显示每个字母的结果.
SQLServer:
?
1
2
3
4
SELECT SUBSTRING (e.ENAME,seq.pos,1) AS ename_Split
FROM (SELECT ENAME FROM emp WHERE ename= 'KING' ) e,
(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P' AND number>0)seq
WHERE seq.pos <= LEN(e.ename)
执行结果:
ename_Split
K
I
N
G
注:
1: 这里master.[dbo].[spt_values]是一张特殊的系统视图,里面存了从0到2047总2048条自增序列.
2: 如果不明白,可以分段来看.
Step1:
?
1
2
3
SELECT e.*,seq.* FROM
(SELECT ENAME FROM emp WHERE ename= 'KING' ) e,
(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P' AND number>0)seq
执行结果:
ENAME
pos
KING
1
KING
2
KING
3
KING
4
KING
5
KING
6
KING
7
KING
8
…
…
Sept2:
?
1
2
3
SELECT SUBSTRING(e.ENAME,seq.pos,1)AS ename_Split
FROM .. e, ..seq
WHERE seq.pos<=LEN(e.ename)
这里通过SUBSTRNG函数,每次的开始位置不断调整,每次仅取一个字符,再通过LEN函数过滤.所以得到最终结果.如果不熟悉SUBSTRING的语法,这里简单介绍下.
SUBSTRING ( expression, start, length )
1) 参数expression是要截取的原始字符串,比如这里的” KING”
2) 参数start是要截取的位置,比如从第2个位置开始,那应该从” I”往后数.
3) 参数length是要截取的长度,沿用上一行的例子,如果长度定义为2,则最终截取字符串是”IN”
Oracle:
?
1
2
3
4
5
6
7
8
9
10
WITH t(num) AS (
SELECT 1 FROM DUAL
UNION ALL
SELECT t.num+1
FROM t WHEREt.num<100
)
SELECT SUBSTR(e.ENAME,seq.num,1) AS ename_Split FROM
(SELECT ENAME FROM emp WHERE ename ='KING' ) e,
(SELECT num FROM t)seq
WHERE seq.num <= LENGTH(e.ename)
Mysql8.0:
?
1
2
3
4
5
6
7
8
9
10
WITH RECURSIVE cte (num)AS
(
SELECT 1
UNION ALL
SELECT num+1 FROM cte WHERE num <100
)
SELECT SUBSTRING(e.ENAME,seq.num,1) AS ename_Split FROM
(SELECT ENAME FROM emp WHERE ename ='KING' ) e,
(SELECT num FROM cte)seq
WHERE seq.num <= LENGTH(e.ename)
7.6.3 处理含引号的字符串
需求:
往dept表里插入dname为Test’s,loc为Beijing,deptno为100的数据.
解决方法:
这里有位引号是特殊符号,所以需要特殊处理,比如如果双引号包裹起来.
Mysql:
?
1
INSERT INTO dept VALUES(100,'Test\'s','Beijing');
Sql Server:
?
1
2
3
4
5
6
7
8
BEGIN TRAN
SET IDENTITY_INSERTdeptON;
GO
INSERT INTO dept(deptno,dname,loc) VALUES (100,'Test''s','Beijing');
SELECT * FROM dept WHERE deptno=100;
SET IDENTITY_INSERTdeptOFF;
GO
ROLLBACK TRAN
执行结果:
deptno
dname
loc
100
Test's
Beijing
7.6.4 计算某个字符出现的次数
需求:
查询emp表emptno是7499的用户的job里S出现的次数.
解决方法:
这里length(len)结合replace函数算出字符串出现的次数.
Sql Server:
?
1
2
SELECT empno,job,(LEN(JOB) - LEN(REPLACE(JOB,'S','')))/LEN('S') AS StrFreq
FROM emp WHERE empno=7499;
empno
job
StrFreq
7499
SALESMAN
2
Mysql:
?
1
2
SELECT empno,job,ROUND((LENGTH(JOB) - LENGTH(REPLACE(JOB,'S','')))/LENGTH('S')) AS StrFreq
FROM emp WHERE empno=7499;
注:这里除以LENGTH('S')是为了考虑传入的字符串是2位以及以上的情况,比如’SS’