SQL Server常用腳本大全：表復制、分頁、外連接、定時提醒等

1. 僅復制表結構

SELECT * INTO b FROM a WHERE 1<>1;

2. 複製表數據

INSERT INTO b(a, b, c) SELECT d, e, f FROM a;

3. 顯示文章、提交人、最後回覆

SELECT a.title, a.username, b.adddate
FROM table a,
     (SELECT MAX(adddate) adddate FROM table WHERE table.title = a.title) b;

4. 外連接查詢

SELECT a.a, a.b, a.c, b.c, b.d, b.f
FROM a LEFT OUTER JOIN b ON a.a = b.c;

5. 日程提前5分鐘提醒

SELECT * FROM schedule
WHERE DATEDIFF(minute, start_time, GETDATE()) > 5;

6. 刪除主表中副表無對應的數據

DELETE FROM info
WHERE NOT EXISTS (SELECT * FROM infobz WHERE info.infid = infobz.infid);

7. 當月與上月數據對比

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
     (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
      FROM (SELECT NUM, UPD_DATE FROM TABLE2 
            WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE,'YYYY/MM')) X,
           (SELECT NUM, UPD_DATE FROM TABLE2
            WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = 
                  TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')||'/01','YYYY/MM/DD')-1,'YYYY/MM')) Y
      WHERE X.NUM = Y.NUM) B
WHERE A.NUM = B.NUM;