SQL中count()方法的使用

本文详细介绍了T-SQL中的COUNT函数用法,包括COUNT(*)与COUNT(expression)的区别及应用场景,如何结合CASE表达式和窗口函数进行复杂统计,并通过实例对比不同查询方式的执行计划。

t_sql中的COUNT函数

1 count函数的定义

count函数的定义可见MSDN。定义如下:

COUNT  ( { [ [ ALL  | DISTINCT  ] expression ] | * } )
那么 COUNT  有两种使用方式 COUNT (expression)和 COUNT (*),它返回一个对一个表按某列计数的值。
  • COUNT (*)返回表的行数。它不会过滤 null 和重复的行。
  • COUNT (expression)会过滤掉 null 值,所以值 null 行不会加入到计数当中,但如果不在expression前面加上 distinct 关键字,它是会过滤掉重复行的。
以此可以得出一个结论: count (*)返回值总是大于或等于 count (expression)的返回值。
在应用中,好多人喜欢使用 COUNT (1),这里面的1其实就是一个expression,因为你的表中没有列名为1的列,那么它的返回结果是和 COUNT (*)一模一样的,
个人觉得效率也是没有差别的。
; WITH  cte1(c1,c2, Description) AS (
     SELECT  1, 1, 'This is a fox'  UNION  ALL
     SELECT  2, NULL , 'Firefox'  UNION  All
     SELECT  NULL , 2, 'People consider foxes as clever but sly animals'  UNION  All
     SELECT  NULL , NULL , NULL  UNION  ALL
     SELECT  3, NULL , 'This is me'  UNION  ALL
     SELECT  3, 3, 'Fox on the run' )

结果如下:

result1

如结果所示,COUNT(*),COUNT(2)和COUNT(3)是一模一样的。而COUNT(c1)显然过滤掉了NULL值。

注意,COUNT 的参数expression可以为常量(像上面的2,3…),表的列,函数,还可以是语句,具体可见MSDN的定义。下面展示了这个应用。

如果想为cte1中列Description中有字符串'fox’进行计数,典型的做法是:

SELECT  COUNT (*) FROM  cte1
WHERE  PATINDEX( '%fox%' ,cte1.Description) <> 0

这种做法是where中过滤,另外一种方式是在expression中定义查找条件:

SELECT  COUNT ( NULLIF (PATINDEX( '%fox%' , cte1.Description), 0))
FROM  cte1

如果description列中没有字符串'fox'那么PATINDEX函数返回的是0,NULLIF函数因为两个参数相等,那么结果是NULL,因为NULL不会参与计数,所以列中没有'fox’的行不会

参与计数,达到了查找的目的。

当然,我们还可在expression中使用case表达式:

SELECT  COUNT ( CASE
     WHEN  PATINDEX( '%fox%' ,cte1.Description) <> 0 THEN  1
     ELSE  <strong> NULL </strong> END )
FROM  cte1

注意ELSE语句后面必须是NULL,如果是非NULL,ELSE语句也会参与COUNT计数的。

2 在count函数后接聚合窗口函数OVER。注意聚合窗口函数中是不能有ORDER BY,ORDER BY只能出现在排名函数的over子句中。OVER字句的定义见MSDN

SELECT  c.*, COUNT (*) OVER(PARTITION BY  c.c1) 'c1 * count' ,
         COUNT (c1) OVER(PARTITION BY  c.c1) 'c1 c1 count' ,
     COUNT (*) OVER(PARTITION BY  c.c2) 'c2 count' ,
     COUNT ( CASE
         WHEN  LEFT (c.Description, 1) IN  ( 'T' ) THEN  1
         ELSE  NULL  END ) OVER(PARTITION BY  LEFT (c.Description, 1)) 'start with T' ,
     COUNT ( CASE
         WHEN  LEFT (c.Description, 1) IN  ( 'T' , 'F' , 'P' ) THEN  1
         ELSE  NULL  END ) OVER(PARTITION BY  LEFT (c.Description, 1)) 'start with T, F OR P'
FROM  cte1 c
注意OVER字句不能为OVER(PARTITION BY  c.c1 ORDER  BY  c.c1),这是因为 count 不是排名函数。
以上的运行结果为:
<a href= "http://images.cnblogs.com/cnblogs_com/fgynew/WindowsLiveWriter/t_sqlcount_11359/result333_4.png" ><img style= "border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px"  title= "result333"  border= "0"  alt= "result333"  src= "http://images.cnblogs.com/cnblogs_com/fgynew/WindowsLiveWriter/t_sqlcount_11359/result333_thumb_1.png"  width= "804"  height= "183" ></a>

可以看出,在使用OVER子句时候,COUNT还是遵循了最基本的准则,COUNT(*)会对null行计数,而COUNT(expression)则不会。

以上在COUNT 的expression中设置条件显然不是一种很优化的方式,因为这种方式会首先读取表中的所有数据,是对表进行扫描,而在where子句中设置条件进行过滤是一种很好的方式。因为从逻辑上讲,where先于select执行,所有数据库引擎只会读取部分数据,不是读取所有数据。如果要对以表中c1列的null进行统计,可以有两种方式:

SELECT  COUNT (*)
FROM  cte1
WHERE  c1 IS  NULL

或者:

SELECT  COUNT ( CASE
     WHEN  c1 IS  NULL  THEN  'x'
     ELSE  NULL  END )
FROM  cte1

最后看看执行计划的比较,后面的方式多了一个步骤(过滤):

result3

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值