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'
)
|
结果如下:
如结果所示,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
|
最后看看执行计划的比较,后面的方式多了一个步骤(过滤):