oracle语句和sqlserver语句,SqlServer和Oracle中一些常用的sql语句2

--------------------------------------------------------------

WITH Emp

AS

(

SELECT E.Dept_Id

, Count(*) Emp_Count

FROM Employees E

GROUP BY E.Dept_Id

)

SELECT D.Dept_Name

, E.Emp_Count

FROM Departments D , Emp E

WHERE D.Dept_Id = E.Dept_Id

ORDER BY Emp_Count DESC

--------------------------------------------------------------

/************************************************************

打印99乘法表

************************************************************/

--把重复用到的SQL语句放在with as 里面，取一个别名可用做子查询部分，后面的查询就可以用它

WITH Tally(N) AS

(

SELECT 1 N

--FROM DUAL

UNION ALL

SELECT N + 1 N

FROM Tally

WHERE N < 9

)

SELECT CAST(B.N AS VARCHAR)

+ ' * '

+ CAST(A.N AS VARCHAR)

+ ' = '

+ CAST(A.N * B.N AS VARCHAR)

Result

FROM Tally A

CROSS JOIN Tally B

--------------------------------------------------------------

--SQL SERVER

--用table2中的数据 更新存在于table1的数据

UPDATE table1

SET

table1.UserName = table2.UserName,

table1.Pwd = table2.Pwd

FROM table2

WHERE table1.id=table2.id

---------------------------------------------------------------

--Oracle

UPDATE Emp_Bak E

SET (Salary , Dept_Id)=

(

SELECT A.Salary, A.Dept_Id

FROM Adjustment A

WHERE E.Emp_Id = A.Emp_Id

)

WHERE E.Emp_Id=

(

SELECT A.Emp_Id

FROM Adjustment A

WHERE E.Emp_Id =

A.Emp_Id

)

UPDATE

(

SELECT E.Salary , A.Salary New_Salary

, E.Dept_Id, A.Dept_Id

New_Dept_Id

FROM Emp_Bak E, Adjustment A

WHERE E.Emp_Id = A.Emp_Id

) --(INLINE-VIEW)

SET Salary = New_Salary

, Dept_Id = New_Dept_Id

---Oracle和SQLServer补齐字符串的方法

SELECT RIGHT(REPLICATE('0',10)+LTRIM(1234),10); --SQLServer

SELECT LPAD(1234,10,'0') FROM DUAL; --Oracle

--结果 0000001234

-----特殊字符 模糊搜索 % _相关处理

SELECT *

FROM

(

SELECT '5% Discount' VAL

--FROM DUAL

UNION ALL

SELECT '59_' VAL

--FROM DUAL

) D

WHERE VAL LIKE '5\%%' ESCAPE '\'

SELECT *

FROM

(

SELECT '5% Discount' VAL

--FROM DUAL

UNION ALL

SELECT '59_' VAL

--FROM DUAL

) D

WHERE VAL LIKE '%\_' ESCAPE '\'

--還有SQL SERVER特有的

SELECT *

FROM

(

SELECT '5% Discount' VAL

UNION ALL

SELECT '59_' VAL

) D

WHERE VAL LIKE '5[%]%'

--

SELECT *

FROM

(

SELECT '5% Discount' VAL

UNION ALL

SELECT '59_' VAL

) D

WHERE VAL LIKE '%[_]'

------------------------------------------

--正则表达式搜索

--145, ORACLE

SELECT Val

FROM

(

SELECT '123' Val

FROM DUAL

UNION ALL

SELECT '456' FROM DUAL

UNION ALL

SELECT 'ABC' FROM DUAL

UNION ALL

SELECT 'xyz' FROM DUAL

UNION ALL

SELECT '@789' FROM DUAL

UNION ALL

SELECT '789@' FROM DUAL

)

WHERE 1=1

AND REGEXP_LIKE(Val, '^[0-9]') --測試1

--AND REGEXP_LIKE( Val, '^[^0-9]')--測試2

--AND REGEXP_LIKE(Val, '^[A-Za-z]')--測試3

--145, SQL SERVER

SELECT Val

FROM

(

SELECT '123' Val

UNION ALL

SELECT '456'

UNION ALL

SELECT 'ABC'

UNION ALL

SELECT 'xyz'

UNION ALL

SELECT '@789'

UNION ALL

SELECT '789@'

) A

WHERE 1=1

AND Val LIKE '[0-9]%' --測試1, 數字(0-9)

--AND Val LIKE '[^0-9]%'--測試2, 非數字

--AND Val LIKE '[A-Z]%' --測試,3 英文(A-Z)

--------------中文Unicode区间----------------

---------------------------------------------

WITH Tally(N) AS

(

SELECT 19966 N

UNION ALL

SELECT N +1 N

FROM Tally

WHERE N<40892

)

SELECT N, NCHAR(N) Word

FROM Tally

OPTION (MAXRECURSION 32000)

------------------利用中文是两个字节的特性判断-------------------

--150, SQL SERVER

SELECT Word

, LEN(Word)

, DATALENGTH(Word)

, CASE WHEN LEN(Word) = DATALENGTH(Word) THEN '英文'

ELSE '中文'

END Judge

FROM

(

SELECT 'English' Word

--FROM DUAL

UNION ALL

SELECT '中文'

--FROM DUAL

UNION ALL

SELECT '堃'

--FROM DUAL

) A

--149, ORACLE 利用中文是两个字节的特性判断

SELECT Word

, LENGTH(Word) "Len"

, LENGTHB(Word) "LenB"

, CASE WHEN LENGTH(Word) = LENGTHB(Word) THEN '英文'

ELSE '中文'

END Judge

FROM

(

SELECT 'English' Word

FROM DUAL

UNION ALL

SELECT '中文'

FROM DUAL

UNION ALL

SELECT '堃'

FROM DUAL

)