سوال در خصوص کوئری نویسی MS-SQL
سلام و عرض ادب
من برنامه نویسی رو خیلی کم یادم هست، توی MS-SQL دارم کوئری میزنم به یک سرور این قطعه کد رو میزنم که اگر مقدار FinalStatus مثلاً مساوی 48 بود توی ستونی تحت عنوان 48 بنویسه، حالا من میخوام یه ستون دیگه اضافه کنم که هر مقداری توی فیلد FinalStat بود توی بخش شمارنده محاسبه کنه (یعنی یک ستون اضافه بشه تحت عنوان All و هر وضعیتی که بود رو هم در نظر بگیره و توی All محاسبه کنه و بگذاره):
--Part1
;WITH Top1000Records AS (
SELECT TOP 1000000 *
FROM [dbo].[ImportantDB] WITH (NOLOCK)
WHERE
[TimeStamp] BETWEEN dateadd(minute,-1,getdate()) AND getdate()
ORDER BY [TimeStamp] DESC
)
INSERT INTO [dbo].[zabbix]
([DestAddr]
,[FinalStatus]
,[Count]
,[Percentage])
SELECT
DestAddr,
FinalStatus,
COUNT(*) AS Count,
(COUNT(*) * 100.0) / (SELECT COUNT(*) FROM Top1000Records WHERE DestAddr = tr.DestAddr) AS Percentage
FROM Top1000Records tr
GROUP BY DestAddr, FinalStatus
ORDER BY DestAddr, Percentage DESC
option(maxdop 1); --max_grant_percent = 0.1
--Part2
select [destaddr],
sum(isnull([48] ,0)) as [48],
sum(isnull([48%],0)) as [48%],
sum(isnull([52] ,0)) as [52],
sum(isnull([52%],0)) as [52%],
sum(isnull([53] ,0)) as [53],
sum(isnull([53%],0)) as [53%],
sum(isnull([54] ,0)) as [54],
sum(isnull([54%],0)) as [54%],
sum(isnull([55] ,0)) as [55],
sum(isnull([55%],0)) as [55%],
sum(isnull([58] ,0)) as [58],
sum(isnull([58%],0)) as [58%],
sum(isnull([60] ,0)) as [60],
sum(isnull([60%],0)) as [60%],
sum(isnull([61] ,0)) as [61],
sum(isnull([61%],0)) as [61%],
sum(isnull([65] ,0)) as [65],
sum(isnull([65%],0)) as [65%] from
(select
[destaddr],
[48] as [48],
[480] as[48%],
[52]as [52],
[520]as [52%],
[53]as [53],
[530]as [53%],
[54]as [54],
[540]as [54%],
[55]as [55],
[550]as [55%],
[58]as [58],
[580]as [58%],
[60]as [60],
[600]as [60%],
[61]as [61],
[610]as [61%],
[65]as [65],
[650] as[65%]
from
(
SELECT [DestAddr]
,[FinalStatus] as [FinalStatus]
,[FinalStatus]*10 as [FinalStatus2]
,[Count]
,[Percentage]
FROM [dbo].zabbix with(nolock)
) p
pivot
(sum([count]) for [FinalStatus] in ([48],[52],[53],[54],[55],[58],[60],[61],[65])
)as pvt
pivot
(sum([Percentage]) for [FinalStatus2] in ([480],[520],[530],[540],[550],[580],[600],[610],[650])
)as pvts
--order by destaddr
) pp
group by destaddr
با تشکر
پاسخ با نقل قول
