學習紀錄

最近在做統計報表，大家都是這樣做的。稍微練習一下 ..

可以把WITH當成一種子查詢，其實我解讀她更像是TempTABLE 可以反覆的讀取內容的資料

並且提高SQL的可讀性

with MC(促銷名稱) as
(
select MB004 NA from POSMC
LEFT JOIN POSMB ON MB001=MC001 AND MB002 = MC002 AND MB003 = MC003
where MC004 = '4000005'
UNION ALL

select MI004 NA from POSMJ
LEFT JOIN POSMI ON MI001=MJ001 AND MI002 = MJ002 AND MI003 = MJ003
where MJ004 = '5000001'
UNION ALL

select MO004 NA from POSMP
LEFT JOIN POSMO ON MO001=MP001 AND MO002 = MP002 AND MO003 = MP003
where MP005 = '5000001'
)

select * from MC

這樣看起來SQL的閱讀性更佳了 ^Q^

這幾天剛好在看一些SQL的相關統計的用法。發現在2005的時候就有增加了一個非常好用的語法

有空來改掉某幾隻的SQL語法，長到讓人不知道怎麼維護

統計 時段品號加總 ..

SELECT TB010 AS 品號, MB002 AS 品名,
ISNULL(Q1, 0) as '第一季', ISNULL(Q2, 0) as '第二季', ISNULL(Q3, 0) as '第三季', ISNULL(Q4, 0) as '第四季'
FROM (
SELECT
Case
  when SUBSTRING(TB001, 5,2) ='01' then 'Q1'
  when SUBSTRING(TB001, 5,2) ='02' then 'Q1'
  when SUBSTRING(TB001, 5,2) ='03' then 'Q1'
  when SUBSTRING(TB001, 5,2) ='04' then 'Q2'
  when SUBSTRING(TB001, 5,2) ='05' then 'Q2'
  when SUBSTRING(TB001, 5,2) ='06' then 'Q2'
  when SUBSTRING(TB001, 5,2) ='07' then 'Q3'
  when SUBSTRING(TB001, 5,2) ='08' then 'Q3'
  when SUBSTRING(TB001, 5,2) ='09' then 'Q3'
  when SUBSTRING(TB001, 5,2) ='10' then 'Q4'
  when SUBSTRING(TB001, 5,2) ='11' then 'Q4'
  when SUBSTRING(TB001, 5,2) ='12' then 'Q4'
else null End as N'季',
TB010, MB002, SUM(TB033) as '季加總'
FROM dbo.POSTB
LEFT JOIN INVMB ON MB001 = TB010
GROUP BY TB001,TB010, MB002
) as GroupTable
PIVOT
(
Sum(季加總)
FOR 季 IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable

呈現結果

透過SUM() OVER 可以省略掉很多子查詢的寫法 .. 且可以很簡易的達到需求

SELECT DISTINCT  B.TB010, MB002,
SUM(B.TB019) over(PARTITION BY B.TB010) Qty,    --計算數量
SUM(B.TB019) over(ORDER BY B.TB010) Acc_Qty ,   --累積數量
SUM(B.TB019) over() Total_Qty                   --總數量
FROM POSTB AS  A
INNER JOIN  POSTB AS B ON A.TB001= B.TB001 AND
A.TB002= B.TB002 AND
A.TB003= B.TB003 AND
A.TB006= B.TB006
INNER JOIN  INVMB ON MB001 = B.TB010
WHERE A.TB010 = '5000001' AND B.TB010  '5000001' --AND B.TB001 ='20180731'
ORDER BY Qty desc

結果

下面是我做的幾個SQL  VIEW的測試。

效能等同於TABLE實際查詢。

利用資料庫A 查看資料庫B的資料並呈現及實現查詢等等的動作

以下是SQL圖

用一個有40萬筆資料的來呈現

在SQL2008 之後多了這個功能讓我覺得非常的貼心。

當客戶反映他的SQL在某一個時間點一直很慢，連線逾時的時候你就能打開這功能來分析它到底是怎麼了

網路有很多文章，教大家怎麼將資料收集的資料庫設定好以及啟動。我這邊純粹就案件分享了

   我目前的狀態是關閉的。右鍵即可啟動

啟動後收集一陣子的資訊就能看到下圖

訊息 8921，層級 16，狀態 1，行 1
檢查結束。收集事實時偵測到失敗。可能是由於 tempdb 空間用完或系統資料表不一致。請檢查先前的錯誤。

理論上應該是要可用DBCC CHECKDB( 修復指令 )  

SQL 好像遇到這個問題。如果DBCC CHECKDB不能下的狀況下。基本上只勝下重裝一個資料庫一途了....

轉載於http://www.cc.ntu.edu.tw/chinese/epaper/0026/20130920_2609.html

我要介紹的是他的第一組檢測cpu佔用的SQL在對於查問題上，非常的有幫助。

如何檢測 SQL Server CPU是否遭遇瓶頸

作者：陳淑萍 / 計算機及資訊網路中心程式組程式設計師

本文將探討在使用SQL Server時有那些原因可能會造成過度消耗CPU資源，若CPU使用率管理不善或過度使用CPU資源的話，可能會對SQL Server有明顯的影響，建議您需要增加或更換CPU。。

一般來說檢測資料庫伺服器CPU是否遭遇瓶頸很容易觀察，在使預SQL Server時，若 CPU持續15分鐘維持在70~80%以上的使用率(排除突發性狀況)，且後效能逐漸下降，那麼就可以判定資料庫伺服器可能遭遇CPU瓶頸。本文章提供您參考有那些原因可能會造成過度消耗CPU資源，若CPU使用率管理不善或過度使用CPU資源的話，可能會對SQL Server有明顯的影響，建議您需要增加或更換CPU了。

主要常被使用來檢測的工具有兩種，第一種『效能監視器 (Performance Monitor)』，第二種是DMV。

一、 效能監視器(Performance Monitor)
只要在資料庫伺服器機器上，在開始命令框中輸入perfmon按確認鍵即可打開效能監視器，如下圖1所示。