关于case的问题 我这条写错了

Question

SELECT (case(avg(a.[WinCount])+avg(a.[LostCount])+avg(a.[DrawCount])+avg(a.[FleeCount]))when null then 0 else 0)+(avg(b.[WinCount])+avg(b.[LostCount])+avg(b.[DrawCount])+avg(b.[FleeCount]))) as GameCount  FROM [QPGameScoreDB].[dbo].[GameScoreInfo] a left join [QPTreasureDB].[dbo].[GameScoreInfo] b on a.UserID=b.UserID  where a.UserID=2188 Group by a.UserID因为 如果GameScoreInfo表 不存在这条记录的时候 那么 这条语句 就返回的null
那么我想在avg(a.[WinCount])+avg(a.[LostCount])+avg(a.[DrawCount])+avg(a.[FleeCount]) 的时候 如果返回的
是null值那么 我直接给它一个0  但是我这个case 用法不对  求教了这里应该怎么用这个case

Answer 1

-- 不需要 用 case ... when ，直接用 isnull()函数就可以啦!

Answer 2

select isnull(a.a+a.b+a.c,0)或者你可以具体到括号里面加isnull

Answer 3

SELECT a.UserID,
       avg(isnull(a.[WinCount],0))+
       avg(isnull(a.[LostCount],0))+
       avg(isnull(a.[DrawCount],0))+
       avg(isnull(a.[FleeCount],0))+
       avg(isnull(b.[WinCount],0))+
       avg(isnull(b.[LostCount],0))+
       avg(isnull(b.[DrawCount],0))+
       avg(isnull(b.[FleeCount],0)) as GameCount 
FROM [QPGameScoreDB].[dbo].[GameScoreInfo] a 
left join [QPTreasureDB].[dbo].[GameScoreInfo] b 
on a.UserID=b.UserID 
where a.UserID=2188 
Group by a.UserID;-- 应该是：先各字段相加，再求平均值吧：SELECT a.UserID,
       avg(isnull(a.[WinCount],0)+isnull(a.[LostCount],0)+isnull(a.[DrawCount],0)+isnull(a.[FleeCount],0)+isnull(b.[WinCount],0)+
           isnull(b.[LostCount],0)+isnull(b.[DrawCount],0)+isnull(b.[FleeCount],0) ) as GameCount 
FROM [QPGameScoreDB].[dbo].[GameScoreInfo] a 
left join [QPTreasureDB].[dbo].[GameScoreInfo] b 
on a.UserID=b.UserID 
where a.UserID=2188 
Group by a.UserID;

Answer 4

-- avg(b.[WinCount])+avg(b.[LostCount])+avg(b.[DrawCount])+avg(b.[FleeCount])
--GameScoreInfo表 不存在这条记录的时候,上面四项至少有一项为null，所以不需case语句，如下：
SELECT avg(b.[WinCount])+avg(b.[LostCount])+avg(b.[DrawCount])+avg(b.[FleeCount]) as GameCount  
FROM [QPGameScoreDB].[dbo].[GameScoreInfo] a left join [QPTreasureDB].[dbo].[GameScoreInfo] b on a.UserID=b.UserID  
where a.UserID=2188 Group by a.UserID

Answer 5

case when then  else end

Answer 6

Select avg(isNull(a.[WinCount],0))+avg(isNull(a.[LostCount],0))+avg(isNull(a.[DrawCount],0))+avg(isNull(a.[FleeCount],0))
+(avg(isNull(b.[WinCount],0))+avg(isNull(b.[LostCount],0))+avg(isNull(b.[DrawCount],0))+avg(isNull(b.[FleeCount],0))) As GameCount
FROM [QPGameScoreDB].[dbo].[GameScoreInfo] a left join [QPTreasureDB].[dbo].[GameScoreInfo] b on a.UserID=b.UserID  
where a.UserID=2188 Group by a.UserID

Answer 7

Select avg(isNull(a.[WinCount],0))+avg(isNull(a.[LostCount],0))
+avg(isNull(a.[DrawCount],0))+avg(isNull(a.[FleeCount],0))
+(avg(isNull(b.[WinCount],0))+avg(isNull(b.[LostCount],0))
+avg(isNull(b.[DrawCount],0))+avg(isNull(b.[FleeCount],0))) As GameCount
FROM [QPGameScoreDB].[dbo].[GameScoreInfo] a 
left join [QPTreasureDB].[dbo].[GameScoreInfo] b on a.UserID=b.UserID  
where a.UserID=2188 Group by a.UserID