TSQL中 Count() 函数使用的一点小技巧

 TSQL中 Count() 函数使用的一点小技巧

 

摘自：http://blog.csdn.net/plean/archive/2008/09/08/2900843.aspx

 

阅：使用子查询效率要高于外连接；

 

COUNT( * ) 返回组中项目的数量，这些项目包括 NULL 值和副本。
COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。
COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。
COUNT( expression )  默认是ALL , COUNT( expression ) 其实相当于COUNT(ALL expression)；

由于COUNT( * ) 和 COUNT( expression ) 返回的值会不一样，一些情况下就会有问题，比如下面的范例：

有以下两个表
Table1 有以下记录（类似一个分类表）
ID Title
1 aa
2 bb
3 cc
4 dd
5 ee
6 ff
Table2 有以下记录用来记录Table1中的分类具体出现，这里的ID对应Table1中的ID
ID
1
1
2
3
5
3
4

如果我们要统计每一个分类在Table2中的出现次数，（如果某个分类出现次数为0，也要统计出来）
可以用下面的SQL 语句
SELECT count(b.ID) as num ,a.ID as AnswerID
FROM Table1 a left  OUTER JOIN Table2 b ON a.ID = b.ID
group by a.ID

如果是下面的写法，统计出来的结果就是错误的。：

SELECT count(*) as num ,a.ID  as AnswerID
FROM Table1 a left  OUTER JOIN Table2 b ON a.ID = b.ID
group by a.ID 

另外这个统计可以通过下面的方式实现：

SELECT
　CategoryID,
　(SELECT COUNT(*) FROM Content WHERE CategoryID=c.CategoryID)
FROM
　Category c

 

 

 

******************************************************************************

关于子查询和Join的比较

******************************************************************************

 

 

嗯，应该说在某些情况下还是会出现使用JOIN的效率不如使用子查询的情形，至少索引或统计的结构就可能引起这个差异。以Northwind数据库为例，如有下面两个逻辑相等的查询：

SELECT
　c.CategoryID, COUNT(p.CategoryID)
FROM
　Categories c LEFT JOIN Products p
　　ON p.CategoryID = c.CategoryID
GROUP BY
　c.CategoryID

和：

SELECT
　c.CategoryID,
　(SELECT COUNT(*) FROM Products
　　WHERE CategoryID = c.CategoryID)
FROM
　Categories c

在默认情况下，把两个查询放在一个批里查看执行计划，会发现各自都占50%的开销，而且执行计划完全相同——这就是你说的内部转换。然而，如果把主表（即Categories表）的主键索引删除，情况就不同了——两种查询的执行计划有了微妙的差异，且总是使用子查询的开销要小一些（我曾见过相同逻辑的两种查询开销之比达到7:3甚至更高）。

当然，一个好的DBA不会不给主表主键建立索引，但我想说的是，SQL Server内部对子查询向外连接的转换过程对某些情形来说还是有更好的优化策略的。另外从SQL程序员的角度说，对于这种应用场合应该首先考虑使用子查询，无论从可读性、执行效率还是功能来说，这种写法都有很大的优势。

 

 

 

专家就是专家，一针见血。不过我又确认了一下，发现就算写成这样：

SELECT
　c.CategoryID, COUNT(p.CategoryID)
FROM
　Categories c LEFT JOIN Products p
　　ON p.CategoryID = c.CategoryID
GROUP BY
　c.CategoryID

和：

SELECT
　c.CategoryID,
　(SELECT COUNT(*) FROM Products
　　WHERE CategoryID = c.CategoryID)
FROM
　Categories c
GROUP BY
　c.CategoryID

在CategoryID字段没有索引的时候，还是使用子查询的开销更小一些——而此时两者的逻辑应该是一样的了。所以结论还是不变，就是在这种应用场合下，使用子查询的性能总是小于等于使用外连接的。而且在CategoryID是主键的前提下，使用子查询的时候可以不用写GROUP BY字句而获得同样的结果，这样可以不受GROUP BY对选择列的局限。

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/plean/archive/2008/09/08/2900843.aspx