我有一個查詢，它給了我輸出，如下面的螢屏截圖所示。
select
a.storeId, b.district, b.region,
count (case when a.photoAppImage is null then 1 end) as via_u,
count (case when a.photoAppImage =1 then 1 end) as via_p
from
used_listings_V2 a
left outer join
locations b on a.storeId = b.storeID
where
convert(date, a.imageUpdateDate) = convert(date, GETDATE()-1)
group by
a.storeId, b.district, b.region
已編輯
我想將計數拆分為我在一行中得到的兩個不同的列。photoAppImage 為空的所有記錄都應在“Via_u”下顯示計數，而具有 1 個 photoAppImage 的記錄應在“Via_p”下顯示計數。預期的輸出將如下所示：
基本上我認為我很難在 case 陳述句中給出別名。有人可以幫我嗎？
uj5u.com熱心網友回復：
采用：
Count(case when a.photoappimage is null then 1 end) as via_u,
Count(case when a.photoappimage is null then 1 end)/100. as via_u_pct,
Count(case when a.photoappimage=1 then 1 end ) as via_p,
Count(case when a.photoAppImage is null or a.photoAppImage=1 then 1 end) as via_u_or_p
更新：結合所有（并使用子查詢來簡化 pct 計算）：
select
storeId,
district,
region,
via_u,
via_p,
via_u/100./(via_u via_p) as via_u_pct
from (
select
a.storeId, b.district, b.region,
count (case when a.photoAppImage is null then 1 end) as via_u,
count (case when a.photoAppImage =1 then 1 end) as via_p
from
used_listings_V2 a
left outer join
locations b on a.storeId = b.storeID
where
convert(date, a.imageUpdateDate) = convert(date, GETDATE()-1)
group by
a.storeId, b.district, b.region
) Step1
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/475224.html
上一篇：在關系表中顯示至少出現一次的行？