分類: 資料庫

[SQL SERVER][TSQL]如何計算累加值

[SQL SERVER][Memo]再談 Clustered Index

[SQL SERVER][Memo]再談 NonClustered Index

這是我在測試面試人員出的考題，SQL要有一定程度才答的出來，
寫出來的記得找我領獎品….

附檔如下(含PDF、Create及Insert的SQL)

考題

SQL檔

原文網址：http://kareslion.blogspot.com/2011/08/ssms.html

請將下面路徑中的「Sqlstudio.bin」更名或刪除掉

• WIN7：C:\Users\’Your_Account_Name‘\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell
• WINXP：C:\Documents and Settings\’Your_Account_Name‘\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell
• WinServer 2003 + SQL2005：C:\Documents and Settings\’Your_Account_Name\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell
• WinServer 2008：C:\Users\’Your_Account_Name‘\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell

SQL結構如下：

SELECT * FROM A
UNION ALL
SELECT * FROM B

若執行過久，有可能是因為在UNION ALL比對資料花了太多效能，
或許可以改用以下的寫法

select *
into #temp
from A

insert into #temp
select * from B

select * from #temp

http://msdn.microsoft.com/zh-tw/library/ms177410.aspx

簡單來說就是把報表由直轉橫。

PIVOT的語法分三層，用三個步驟來使用。
第一步驟：先把要PIVOT的原始資料查詢(Query)好。
第二步驟：設定好PIVOT的欄位與方式。
第三步驟：依PIVOT好了的資料，呈現結果。

EX：

LogTime  StatusCode Cnt
-------- ---------- -----------
06:40:05 200        5
06:40:05 302        1
06:40:06 304        1
06:40:06 200        10
06:40:07 200        5
06:40:07 500        3
06:40:08 200        11
06:40:08 404        1

變成

LogTime  200         302         304         401         404         500
-------- ----------- ----------- ----------- ----------- ----------- -----------
06:40:05 5           1           NULL        NULL        NULL        NULL
06:40:06 10          NULL        1           NULL        NULL        NULL
06:40:07 5           NULL        NULL        NULL        NULL        3
06:40:08 11          NULL        NULL        NULL        1           NULL

另外附上相關的討論：
http://blog.darkthread.net/post-2007-07-20-tips-using-pivot-in-sql-2005.aspx

ROW_NUMBER：

兩個TABLE，T1為Master，T2為Detail，取出同一PK在T2的第N筆資料

SELECT *
FROM Table_1 T1
   INNER JOIN (
      SELECT *
             , ROW_NUMBER() OVER (PARTITION BY PK ORDER BY PK) RID
      FROM Table_2) T2
   ON T1.PK = T2.PK
      AND T2.RID = 取第N筆

參考：ROW_NUMBER (Transact-SQL)

RANK：

很類似ROW_NUMBER的用法，但相同的資料相同排名，下一個不同會【跳脫】

SELECT *
FROM Table_1 T1
   INNER JOIN (
      SELECT *
             , RANK() OVER (PARTITION BY PK ORDER BY PK) RID
      FROM Table_2) T2
   ON T1.PK = T2.PK
      AND T2.RID = 取第N筆

參考：RANK (Transact-SQL)

其他參考：[SQL]為查詢的結果加上序號(ROW_NUMBER,RANK,OVER)

原文網址

1. 刪除 SQL Server 2008 / SQL Server 2008 R2 的 Management Studio 的歷史清單

請在 命令提示字元 或 [開始] / [執行] 下執行以下指令即可清空歷史清單：

DEL "%appdata%\Microsoft\Microsoft SQL Server0\Tools\Shell\SqlStudio.bin"

2. 刪除 SQL Server 2005 的 Management Studio 的歷史清單

請在 命令提示字元 或 [開始] / [執行] 下執行以下指令即可清空歷史清單：

DEL "%appdata%\Microsoft\Microsoft SQL Server\Tools\Shell\mru.dat"

原文網址

---#### 設定每周的開始是星期幾
SET DATEFIRST 1

DECLARE @STARTDATE DATETIME,
@ENDDATE DATETIME;

---#### 設定要產生日期的區間
SELECT @STARTDATE = SUBSTRING(CONVERT(varchar(8),GETDATE(),112),1,6) + '01',
@ENDDATE = CONVERT(VARCHAR(8),DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0)),112);
---#### 產生日期跟星期

;WITH CTETABLE AS (
SELECT [Date] = @STARTDATE
UNION all
SELECT [Date] + 1 FROM CTETABLE WHERE ([Date] < @ENDDATE)
)
SELECT
CONVERT(VARCHAR(8),[Date],112) AS [Date],
CASE datepart(DW, [DATE])
WHEN '1' THEN '星期一'
WHEN '2' THEN '星期二'
WHEN '3' THEN '星期三'
WHEN '4' THEN '星期四'
WHEN '5' THEN '星期五'
WHEN '6' THEN '星期六'
WHEN '7' THEN '星期日' END AS [WeekDay]
FROM CTETABLE

已習慣抓出null的資料，要下where 某某欄位 is null了，
今天臨時被USER問到「is null」、「= null」這兩者的差異在哪？

以下截取MSDN的說明：

http://technet.microsoft.com/zh-tw/library/ms188795.aspx

若要判斷運算式是否為 NULL，請利用 IS NULL 或 IS NOT NULL 來取代比較運算子 (如 = 或 !=)。當兩個引數或其中一個引數是 NULL 時，比較運算子會傳回 UNKNOWN。

http://technet.microsoft.com/zh-tw/library/ms191270.aspx

比較 Null 值時需要特別注意。比較的行為會根據 SET ANSI_NULLS 選項的設定而有所不同。

當 SET ANSI_NULLS 是 ON 時，比較一個或多個 Null 運算式不會產生 TRUE 或 FALSE 的結果，而會產生 UNKNOWN。這是因為未知的值無法與任何值進行邏輯比較。如果運算式與常值 NULL 比較，或是兩個運算式互相比較但其中有一個是 NULL 值，就會發生此狀況。