分享两条SQL语句
1,Oracle的SQL支持的REGEXP_REPLACE
SQL> select regexp_replace('fuck you','(.)(.)','/2/1') from dual;
REGEXP_REPLACE('FUCKYOU','(.)(
------------------------------
ufkcy uo
(呵呵)
字母两个两个一对,全倒过来了
/////////////////////////////////////////////////////////////////////////////
SQL> select regexp_replace('fuck you','(.)(.)','/2/3') from dual;
REGEXP_REPLACE('FUCKYOU','(.)(
------------------------------
ukyu
剔除了所有奇数位置上的字符,只留下偶数位置的字符,空格是奇数位置的字符
2,使用SQL语句模拟二维矩阵
思路:首先用Cross Join构造出一个完全二维表,然后进行排序,分组
用到的表:EMP表,至少有三个字段,DEPTNO,JOB,SAL
DEPTNO,JOB构成联合主键,SAL的值由DEPTNO和JOB两个字段所决定,SAL的值有可能是NULL,要求:当SAL值为空时,显示0
SELECT A.DEPTNO,B.JOB,
(
CASE
WHEN EXISTS (SELECT * FROM EMP WHERE DEPTNO=A.DEPTNO AND JOB=B.JOB) THEN
(
SELECT SUM(SAL)
FROM EMP
WHERE DEPTNO=A.DEPTNO AND JOB=B.JOB
GROUP BY DEPTNO,JOB
)
ELSE 0
END
) SUM_SAL
FROM
(
SELECT DISTINCT DEPTNO
FROM EMP
) A
CROSS JOIN
(
SELECT DISTINCT JOB
FROM EMP
) B
GROUP BY A.DEPTNO,B.JOB
ORDER BY A.DEPTNO,B.JOB