22,209
社区成员
发帖
与我相关
我的任务
分享
;WITH a([value],[type]) AS (
select 2,1 UNION
select 2,2 UNION
select 4,3
)
SELECT dict_type.type,isnull(a.[value],0) AS [value] FROM (VALUES(1),(2),(3),(4),(5)) dict_type([type])
LEFT JOIN a ON a.[type]=dict_type.[type]
type (No column name)
1 2
2 2
3 4
4 0
5 0
select a.number,ISNULL(b.value,0) from
(select number from master..spt_values
where type='p' and number between 1 and 5) a
left join
(SELECT AVG(value) AS value, type
FROM [table]
GROUP BY type ) b
on a.number = b.type