--(0,1,2,3,4,7),查询时可能只有这里面的其中几个,我想将没有的也查询出来,这个要怎样做呢?
SELECT STATUS ,COUNT(status) AS num FROM
(
(select (case
when i.state=2 then 5
when AuditFlag=2 AND ExceptionMsg like '%Boss%' then 4
when AuditFlag=2 AND ExceptionMsg like '%号码文件%' then 7
when AuditFlag=0 and len(isnull(ExceptionMsg,''))>0 then 8
when AuditFlag=1 and (SecurityEventID is null or SecurityEventID=0) and isnull(GiftSucNumReduceCount,0)<=0 then 1 --OK
when AuditFlag=1 and (SecurityEventID is null or SecurityEventID=0) and isnull(GiftSucNumReduceCount,0)>0 then 2 --丢失
when AuditFlag=1 and isnull(SecurityEventID,0)>0 then 3 --异常有增加
else 6
end --已经处理,但是未通过
) Status ,CreateTime
from SCP_Gift g left join dbo.WF_Instance i on g.InstanceID=i.InstanceID left join dbo.SCP_SecurityEvent se on se.EventID=g.SecurityEventID
)
UNION ALL
SELECT 0 as STATUS,CreateTime from SCP_DealBossNum as d where gifttype=1 and not exists(select * from SCP_Gift where BossWordNum like '%'+BossNum+'%')
) tmp WHERE STATUS IN(0,1,2,3,4,7) AND createtime >'2010-11-5'
GROUP BY STATUS
ORDER BY STATUS
SELECT STATUS ,COUNT(status) AS num FROM
(
(select (case
when i.state=2 then 5
when AuditFlag=2 AND ExceptionMsg like '%Boss%' then 4
when AuditFlag=2 AND ExceptionMsg like '%号码文件%' then 7
when AuditFlag=0 and len(isnull(ExceptionMsg,''))>0 then 8
when AuditFlag=1 and (SecurityEventID is null or SecurityEventID=0) and isnull(GiftSucNumReduceCount,0)<=0 then 1 --OK
when AuditFlag=1 and (SecurityEventID is null or SecurityEventID=0) and isnull(GiftSucNumReduceCount,0)>0 then 2 --丢失
when AuditFlag=1 and isnull(SecurityEventID,0)>0 then 3 --异常有增加
else 6
end --已经处理,但是未通过
) Status ,CreateTime
from SCP_Gift g left join dbo.WF_Instance i on g.InstanceID=i.InstanceID left join dbo.SCP_SecurityEvent se on se.EventID=g.SecurityEventID
)
UNION ALL
SELECT 0 as STATUS,CreateTime from SCP_DealBossNum as d where gifttype=1 and not exists(select * from SCP_Gift where BossWordNum like '%'+BossNum+'%')
) tmp WHERE STATUS IN(0,1,2,3,4,7) AND createtime >'2010-11-5'
GROUP BY STATUS
ORDER BY STATUS
比如
status num
0 906
2 16
3 3
7 0
4 0
1 0
就是 in 里面 有的,结果都要有。没有就用 0
case when status in (2,3,4,5) then num else 0 end