简单分析sql统计查询.doc

示例表关系如下 [v_MatchInReward] 视图如下 CTEATE VIEW [dbo].[v_MatchInReward] AS SELECT dbo.MatchInfo.DemandID, dbo.MatchInfo.DemandUserID, dbo.MatchInfo.MatchType, dbo.MatchInfo.IsReadByDemandProvider, dbo.MatchInfo.Status, dbo.MatchInfo.OpertorID, dbo.MatchInfo.IsAudited, dbo.MatchInfo.IsDeleted, dbo.Reward.BountyAmount FROM dbo.MatchInfo INNER JOIN dbo.Reward ON dbo.MatchInfo.DemandID dbo.Reward.RewardID 要求按照MatchInfo.DemandUserID分组统计 Status 4的次数 IsReadByDemandProvider 1的次数 BountyAmount金额的总数 以及在MatchInfo出现的数据条数 set statistics time off set statistics io on /* 版本一 错误用法，先使用case when 再使用count,就相当于先根据case when分组，然后才统计， 不是真正的按条件 */ SELECT DemandUserID, IsNull COUNT 1 ,0 as InRewardCount, IsNull SUM BountyAmount ,0 AS BountyAmountCount, IsNull CASE IsReadByDemandProvider WHEN 0 THEN COUNT 1 END,0 AS NotSeeCount, IsNull CASE [Status] WHEN 4 THEN COUNT 1 END, 0 AS CandidateCount FROM v_MatchInReward WHERE DemandUserID 1 AND MatchType 3 GROUP BY DemandUserID,IsReadByDemandProvider,[Status] /* 版本二 累赘用法，使用主查询分组，多个子查询来统计， 造成多次表扫描 */ SELECT m.DemandUserID, IsNull COUNT 1 ,0 as InRewardCount, IsNull select SUM BountyAmount from Reward inner join matchinfo on RewardID matchinfo.DemandID where matchinfo.DemandUserID m.DemandUserID ,0 AS BountyAmountCount, IsNull select count 1 from matchinfo where IsReadByDemandProvider 0 and demandUserId m.DemandUserID ,0 AS NotSeeCount, IsNull select count 1 from matchinfo where [Status] 4 and demandUserId m.DemandUserID , 0 AS CandidateCount FROM matchinfo as m WHERE m.DemandUserID 1 AND m.MatchType 3 GROUP BY m.DemandUserID /* 版本三 正确用法，在count函数中使用case when,不会导致多层级分组，得到的是真实的数据 缺陷是，count虽然没有统计case when得到的null值 不管设定没设定 ，但是消息框中还是有警告， 尽管没有很实际的数据说明这种情况的不妥，但还是要避免 */ SELECT m.DemandUserID, IsNull COUNT 1 ,0 as InRewardCount, IsNull SUM BountyAmount ,0 AS BountyAmountCount, IsNull count case IsReadByDemandProvider when 0 then 1 end ,0 AS NotSeeCount, IsNull count case [Status] when 4 then 1 end , 0 AS CandidateCount FROM matchinfo a