SqlServer和Oracle中一些常用的sql语句
[sql]
-- 根据表Adjustment中的记录删除Emp_Bak中对应的数据
DELETE Emp_Bak E
WHERE EXISTS
(
SELECT 'X'
FROM Adjustment A
WHERE E.Emp_Id = A.Emp_Id
)
DELETE Emp_Bak E
WHERE Emp_Id IN
(
SELECT A.Emp_Id
FROM Adjustment A
WHERE E.Emp_Id = A.Emp_Id
)
--103, ORACLE 中删除表Emp_Bak中重复数据
--JOIN
DELETE Emp_Bak D
WHERE ROWID >
(
SELECT MIN(ROWID)
FROM Emp_Bak R
WHERE D.Emp_Id = R.Emp_Id
)
--NOT IN
DELETE Emp_Bak D
WHERE ROWID NOT IN
(
SELECT MIN(ROWID)
FROM Emp_Bak R
GROUP BY R.Emp_Id
)
--104, SQL SERVER 删除重复数据 分三步 首先将非重复数据存储到临时表,然后清空原数据表,最后将临时表数据存回原数据表
--STEP01
SELECT DISTINCT Emp_id, Emp_Name
,Dept_id, Mobile, Ext
,Salary, Email, Date_Update
INTO #Emp_Bak
FROM Emp_Bak
--STEP02
TRUNCATE TABLE Emp_Bak
--STEP03
INSERT INTO Emp_Bak
SELECT Emp_id, Emp_Name
,Dept_id ,Mobile, Ext
,Salary, Email
, GETDATE() Date_Create --
FROM #Emp_Bak
------------------------------4.5------------------------------------
--105, ORACLE, 删除大量数据时 可能导致
数据库事务日志文件急剧扩展,甚至无法继续进行事务处理等问题,可用分批删除数据方法
DECLARE
n NUMBER;
BEGIN
LOOP
EXIT WHEN n=0;
DELETE EMP_BAK
WHERE ROWNUM<=5
AND Dept_Id Like 'I%';
n :=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE( n );
COMMIT;
END LOOP;
END;
--105, SQL SERVER,
WHILE (@@ROWCOUNT>0)
BEGIN
DELETE TOP(5)
FROM Emp_Bak
WHERE Dept_Id Like 'I%'
END
--------------------------------
------------------------------5.1------------------------------------
--117
SELECT
TO_CHAR(DATE'-4712-01-01', 'J') "JDay(Base)"
, TO_CHAR(DATE'2010-01-01', 'J') "JDay"
, (TO_CHAR(DATE'2010-01-01', 'J')
-TO_CHAR(DATE'-4712-01-01', 'J'))/365.25 Diff
FROM DUAL
--117
SELECT DATE'2010-03-17' Today
, TIMESTAMP '2010-3-17 8:28:40' Now
FROM DUAL
------------------------------5.4------------------------------------
--124, ORACLE
SELECT
NVL(A, '新值') "test1.A"
, COALESCE(A, '新值')"test1.B"
, COALESCE(A, B, C) "test2.A"
, NVL(A, NVL(B, C)) "test2.B"
FROM
(
SELECT NULL A
, NULL B
, '非NULL' C
FROM DUAL
)
--124, SQL SERVER
SELECT
ISNULL(A, '新值') "test1.A"
, COALESCE(A, '新值') "test1.B"
, COALESCE(A, B, C) "test2.A"
, ISNULL(A, ISNULL(B, C)) "test2.B"
FROM
(
SELECT NULL A
, NULL B
, '非NULL' C
) A
--125
--SQL SERVER
SELECT VAL
, NULLIF(VAL, 0) "NULLIF(VAL, 0)"
FROM
(
SELECT 0 VAL
--FROM DUAL
UNION ALL
SELECT 20 VAL
--FROM DUAL
) A
--ORACLE
SELECT VAL
, NULLIF(VAL, 0) "NULLIF(VAL, 0)"
FROM
(
SELECT 0 VAL
FROM DUAL
UNION ALL
SELECT 20 VAL
FROM DUAL
) A
--