SQL中count()方法的使用 |
您所在的位置:网站首页 › countif用不了 › 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' )结果如下: 如结果所示,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 NULL 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 不是排名函数。 以上的运行结果为:可以看出,在使用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最后看看执行计划的比较,后面的方式多了一个步骤(过滤): |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |