笔记237 SqlServer 中 Group by、having、order by、Distinct 使用注意事项 COUNT函数 COUNT_BIG函数

笔记237 SqlServer 中 Group by、having、order by、Distinct 使用注意事项  COUNT函数  COUNT_BIG函数 

--SqlServer 中 Group by、having、order by、Distinct 使用注意事项 2013-4-14


SELECT COUNT(*) AS COUNT,REQUEST,METHOD FROM REQUESTMETH GROUP BY
REQUEST,METHOD HAVING (REQUEST ='FC.OCEAN.JOB.SERVER.CBIZOZBKHEADER' OR REQUEST='FC.Ocean.Job.Server.CBizOzDocHeader')
AND COUNT(*) >3
ORDER BY REQUEST


--注意事项:--------------------------------------------------------------------
--
--HAVING后的条件不能用别名COUNT>3 必须使用COUNT(*) >3，否则报：列名 'COUNT' 无效。
--
--having 子句中的每一个元素并不一定要出现在select列表中  having只是group by之后再筛选
--但是Group by 子句中的每一个元素都要出现在select列表中


--如果把该语句写成：

SELECT COUNT(*) AS COUNT,REQUEST,METHOD FROM REQUESTMETH GROUP BY
REQUEST ORDER BY REQUEST

--那么将报：
--
--选择列表中的列 'REQUESTMETH.method' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。


--注意：
--1、使用GROUP BY 子句时，SELECT 列表中的非汇总列必须为GROUP BY 列表中的项。
--(即select 中的字段必须为GROUP BY 列表中的项，如果select 中有字段的话)
--例如
SELECT COUNT(*) AS COUNT FROM [dbo].[CT_Append] GROUP BY [I_A_CardType] ORDER BY [I_A_CardType]
--2、分组时，所有的NULL值分为一组。
--3、GROUP BY 列表中一般不允许出现复杂的表达试、显示标题以及SELECT列表中的位置标号。
--
--如
SELECT REQUEST,METHOD, COUNT(*) AS COUNT FROM REQUESTMETH GROUP BY
REQUEST,2 ORDER BY REQUEST 

--错误信息为：每个 GROUP BY 表达式都必须包含至少一个列引用。


--GROUP BY 中使用 ORDER BY注意事项：---------------------------------------------

SELECT COUNT(*) AS COUNT FROM REQUESTMETH GROUP BY REQUEST,METHOD ORDER BY REQUEST,METHOD

--这样是允许的, ORDER BY后面的字段包含在GROUP BY 子句中，order by字段没有在group by子句中也就是group by临时表没有order by指定的字段所以会报错

SELECT COUNT(*) AS COUNTS FROM REQUESTMETH GROUP BY REQUEST ORDER BY COUNT(*) DESC

--这样是允许的，ORDER BY后面的字段包含在聚合函数中，结果集同下面语句一样

SELECT COUNT(*) AS COUNTS FROM REQUESTMETH GROUP BY REQUEST ORDER BY COUNTS DESC

--这样是允许的，区别于HAVING，HAVING后不允许跟聚合函数的别名作为过滤条件


SELECT COUNT(*) AS COUNTS FROM REQUESTMETH GROUP BY REQUEST ORDER BY METHOD

--这样是错误的：ORDER BY 子句中的列 "REQUESTMETH.method" 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
--既没有在select 中也没有在group by中

--SELECT DISTINCT 中使用 ORDER BY注意事项：-----------------------------------

SELECT DISTINCT BOOKID FROM BOOK ORDER BY BOOKNAME

--以上语句将报：

--如果指定了SELECT DISTINCT，那么ORDER BY 子句中的项就必须出现在选择列表中。

--因为以上语句类似

SELECT BOOKID FROM BOOK GROUP BY BOOKID ORDER BY BOOKNAME

--其实错误信息也为：

--ORDER BY子句中的列"BOOK.BookName" 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。


--应该改为：

SELECT DISTINCT BOOKID,BOOKNAME FROM BOOK ORDER BY BOOKNAME
SELECT DISTINCT BOOKID,BOOKNAME FROM BOOK
SELECT BOOKID,BOOKNAME FROM BOOK GROUP BY BOOKID,BOOKNAME

--以上两句查询结果是一致的，DISTINCT的语句其实完全可以等效的转换为GROUP BY语句

 

https://time.geekbang.org/column/article/82865
distinct 和 group by 的性能

如果表 t 的字段 a 上没有索引，那么下面这两条语句的性能是不是相同的?
select a from t group by a
select distinct a from t;

首先需要说明的是，这种 group by 的写法，并不是 SQL 标准的写法。
标准的 group by 语句，是需要在 select 部分加一个聚合函数，比如：
select a,count(*) from t group by a

这条语句的逻辑是：按照字段 a 分组，计算每组的 a 出现的次数。在这个结果里，由于做的是聚合计算，相同的 a 只出现一次。
没有了 count(*) 以后，也就是不再需要执行“计算总数”的逻辑时，第一条语句的逻辑就变成是：
按照字段 a 做分组，相同的 a 的值只返回一行，而这就是 distinct 的语义，
所以不需要执行聚合函数时，distinct 和 group by 这两条语句的语义和执行流程是相同的，因此执行性能也相同。

这两条语句的执行流程是下面这样的。
1.创建一个临时表，临时表有一个字段 a，并且在这个字段 a 上创建一个唯一索引；
2.遍历表 t，依次取数据插入临时表中：如果发现唯一键冲突，就跳过；否则插入成功；
3.遍历完成后，将临时表作为结果集返回给客户端

---

COUNT (Transact-SQL)
返回组中的项数。COUNT 与 COUNT_BIG 函数类似。两个函数唯一的差别是它们的返回值。COUNT 始终返回 int 数据类型值。COUNT_BIG 始终返回 bigint 数据类型值。后面可以跟 OVER 子句。

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

参数
ALL
对所有的值进行聚合函数运算。ALL 是默认值。
DISTINCT
指定 COUNT 返回唯一非空值的数量。
expression
除 text、image 或 ntext 以外任何类型的表达式。不允许使用聚合函数和子查询。
*
指定应该计算所有行以返回表中行的总数。COUNT(*) 不需要任何参数，而且不能与 DISTINCT 一起使用。COUNT(*) 不需要 expression 参数，因为根据定义，该函数不使用有关任何特定列的信息。COUNT(*) 返回指定表中行数而不删除副本。它对各行分别计数。包括包含空值的行。

返回类型
int

备注
COUNT(*) 返回组中的项数。包括 NULL 值和重复项。
COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。
COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。
对于大于 2^31-1 的返回值，COUNT 生成一个错误。这时应使用 COUNT_BIG。

 

 

 

COUNT_BIG (Transact-SQL)
返回组中的项数。COUNT_BIG 的用法与 COUNT 函数类似。两个函数唯一的差别是它们的返回值。COUNT_BIG 始终返回 bigint 数据类型值。COUNT 始终返回 int 数据类型值。后面可能跟随 OVER 子句。

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )

参数
ALL
对所有的值进行聚合函数运算。ALL 是默认值。
DISTINCT
指定 COUNT_BIG 返回唯一非空值的数量。
expression
是任何类型的表达式。不允许使用聚合函数和子查询。
*
指定应该计算所有行以返回表中行的总数。COUNT_BIG(*) 不需要任何参数，而且不能与 DISTINCT 一起使用。COUNT(*) 不需要 expression 参数，因为根据定义，该函数不使用有关任何特定列的信息。COUNT_BIG(*) 返回指定表中的行数并将重复行计算在内。它对各行分别计数。包括包含空值的行。

返回类型
bigint

备注
COUNT_BIG(*) 返回组中的项数。包括 NULL 值和重复项。
COUNT_BIG(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。
COUNT_BIG(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。

 

 

 

f