發表文章

目前顯示的是 九月, 2015的文章

T-SQL 計算佔全部結果的比率

圖片
在 T-SQL 中欲計算某一個數值相對於查詢結果中的比率,基本的作法是第一次查詢先計算總和,再進行第二次查詢計算各筆資料的比率。
提供另一種作法,透過 SQL Server 視窗函式 ( Window Function ) 可以一次完成查詢,要計算總合用以下的語法:
sum(column) over (partition by 0) partition by 後面原本接欄位名稱時,會計算以此欄位群組後的合計,
我嘗試放上任一個數字,就變成計算全部,這點沒找到相關文件說明。

完整範例
create table #T (ORDERNO int, Amount numeric(12,4)); insert into #T values(1, 100); insert into #T values(2, 200); insert into #T values(3, 300); select * from #T; WITH CTE as ( select ORDERNO, Amount from #T ) select ORDERNO, Amount, convert(numeric(12,4), Amount/sum(Amount) over(partition by 0)) as Percentage from CTE ; drop table #T;
參考文件
OVER Clause (Transact-SQL) – SQL Server 技術文件

Remove Creative Cloud Files

圖片
參考 - Adobe與Google合推全新中文免費字型思源黑體下載教學,安裝了 Creative Cloud 才發現程式得常駐執行,二話不說立馬閃退 - 移除它。但在我的 Windows 10 上將軟體移除後,檔案總管卻遺留一個 Creative Cloud Files 目錄,在檔案總管介面上是無法刪除它,得進入 Registry 工具修改,步驟如下:1. 開啟 Registry 工具按下 [Windows]  + [R] ,輸入 : regedit2. 搜尋 Creative Cloud Files 按下 [Ctrl] + [F]  ,輸入 Creative Cloud Files ,按下 [Find Next]3. 修改 System.IsPinnedToNameSpaceTree 的值為 0在 [System.IsPinnedToNameSpaceTree ] 右鍵 [Modify] 開啟編輯視窗,在 [Value data] 將 1 修改為 0,點擊 [OK]關閉檔案總管,重新開啟就看不到 Creative Cloud Files 目錄。
參考文件Remove "Creative Cloud Files" from File Explorer  - Reddit

T-SQL Update with join table

(A) T-SQL 更新資料的最基本語法格式
UPDATE dbo.Table1 SET CalculatedColumn='New Value' WHERE t1.BatchNo = '110';
(B) 當遇到更新的值需要查詢來取得,原本我只會這樣:
UPDATE dbo.Table1 SET CalculatedColumn= (SELECT [Calculated Column] FROM dbo.Table2 WHERE dbo.Table1.CommonField=dbo.Table2.[Common Field] ) WHERE t1.BatchNo = '110';
(C) 前一陣子,偶然+赫然發現原來可以先 Join 再更新
UPDATE t1 SET t1.CalculatedColumn = t2.[Calculated Column] FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t1.CommonField = t2.[Common Field] WHERE t1.BatchNo = '110';
(D) 另一個採用 CTE (COMMON TABLE EXPRESSION) 的花式應用
;WITH t2 AS ( SELECT [key], CalculatedColumn = SUM(some_column) FROM dbo.table2 GROUP BY [key] ) UPDATE t1 SET t1.CalculatedColumn = t2.CalculatedColumn FROM dbo.table1 AS t1 INNER JOIN t2 ON t1.[key] = t2.[key]; 使用心得
當遇到需要先 JOIN 兩個資料表再做更新時,上述 (B) (C) (D) 三種語法,在實際試過幾種不同狀況,發現針對效能考量上並無一種絕對的作法,得視資料量大小與串聯邏輯去設計與嘗試找到最佳解。

參考文章Update a table using JOIN in SQL Server? – stackoverflow

資料庫專案加入參考

圖片
前文<SQL Server Schema Version Control> 介紹在 Visual Studio 2015 / 2013/ … 中建立資料庫專案,透過 Schema Compare 從現有資料庫產生所有項目。
照理此刻整個專案應可以發行到其他資料庫,就在進行建置時發現有 200 項錯誤 (如上圖),主要原因是我有幾個預存程序會讀取系統資料表,例如:
sys.tables
sys.indexes
sys.allocation_units

這些系統資料表的結構不會在專案中,這點也很合理,不然每一個資料庫專案都複製一份 SQL Server 系統資料表,也很累贅的。

解決作法 若有讀取系統資料表的話,可以加入資料庫參考,選取 master  資料庫,原本因引用系統資料表的錯誤則消失了。


在資料庫參考方式有三種選項,使用時機分別為:
1. 若有跨資料庫讀取資料時,可以選擇另一個資料庫專案,事先要先加入方案中。
2. 讀取系統資料則選擇 [System database] –> master
3. 若有跨資料庫,也可以參考該資料庫匯出的 Data-tier Application

參考文件Using References in Database Projects – MSDN

組合多筆資料為一個字串

圖片
在 SQL Server 中如何將多筆資料列中屬於同一群組的字串組合起來 ?
在 stackoverflow 找到這一則討論 Optimal way to concatenate/aggregate strings ,文中有兩個解法分別是:
CTE (COMMON TABLE EXPRESSION)XML PATH 解決方法 將其範例調整可以直接執行在暫存資料表中執行如下
CTE (COMMON TABLE EXPRESSION) 範例

create table #t (id int, name varchar(20)); insert into #t values (1, 'Matt'), (1, 'Rocks'), (2, 'Stylus'); WITH Partitioned AS ( SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber, COUNT(*) OVER (PARTITION BY ID) AS NameCount FROM #t ), Concatenated AS ( SELECT ID, CAST(Name AS nvarchar) AS FullName, Name, NameNumber, NameCount FROM Partitioned WHERE NameNumber = 1 UNION ALL SELECT P.ID, CAST(C.FullName + ', ' + P.Name AS nvarchar), P.Name, P.NameNumber, P.NameCount FROM Partitioned AS P INNER JOIN Concatenated AS C ON P.ID = C.ID AND P.NameNumber = C.NameNumber + 1 ) SELECT ID, FullName FROM Concatenated WHERE NameNumbe…