我有一個這樣的查詢：
select
case when sp.provider='prv1' and sp.status_code = 404 then 'failed'
when sp.provider='prv1' and sp.status_code = 200 then 'success'
when time_taken is null or sp.status_code in (503) or sp.status_code is null then 'unavailable'
else 'other'
end as result,
count(*)
from services_serviceprofile sp
where
service_type = 'type1' and
sp.provider = 'prv1' and
sp.start_time >= '2022-08-22 00:00'
group by
case when sp.provider='prv1' and sp.status_code = 404 then 'failed'
when sp.provider='prv1' and sp.status_code = 200 then 'success'
when time_taken is null or sp.status_code = 503 or sp.status_code is null then 'unavailable'
else 'other'
end
order by count(*) desc;
這給出了結果：
如何在 CASES 中為“所有”請求添加一行？如果我們添加when sp.provider='prv1'沒有更詳細條件的 a，那么所有案例都將成為一個“全部”案例，因為其他案例將被忽略。
uj5u.com熱心網友回復：
您不能在 內部執行此操作CASE，因為它的行為類似于一系列 IF.
您需要單獨計算總計并使用UNION或將總計計算委托給使用這些資料的任何工具。
WITH RequestStats
AS (
SELECT
CASE
WHEN sp.provider = 'prv1' AND sp.status_code = 404 THEN 'failed'
WHEN sp.provider = 'prv1' AND sp.status_code = 200 THEN 'success'
WHEN time_taken IS NULL OR sp.status_code IN (503) OR sp.status_code IS NULL THEN 'unavailable'
ELSE 'other'
END AS Result
,count(*) AS RequestCount
FROM services_serviceprofile sp
WHERE
service_type = 'type1'
AND sp.provider = 'prv1'
AND sp.start_time >= '2022-08-22 00:00'
)
SELECT
Result
,RequestCount
FROM RequestStats
UNION ALL
SELECT
'All' AS Result
,SUM(RequestCount) AS RequestCount
FROM RequestStats
uj5u.com熱心網友回復：
如果您使用的是 SQL Server 2008 或更高版本，則可以使用 ROLLUP() GROUP BY 函式添加最后一行總計：
select results = ISNULL(result, 'All'), Counts = sum(Counts) from (
select
case when sp.provider='prv1' and sp.status_code = 404 then 'failed'
when sp.provider='prv1' and sp.status_code = 200 then 'success'
when time_taken is null or sp.status_code in (503) or sp.status_code is null then 'unavailable'
else 'other'
end as result,
count(*) as Counts
from services_serviceprofile sp
where
service_type = 'type1' and
sp.provider = 'prv1' and
sp.start_time >= '2022-08-22 00:00'
group by
case when sp.provider='prv1' and sp.status_code = 404 then 'failed'
when sp.provider='prv1' and sp.status_code = 200 then 'success'
when time_taken is null or sp.status_code = 503 or sp.status_code is null then 'unavailable'
else 'other'
end
) as Final
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/529794.html
下一篇：SQL格式字串