count,group by,having(SQL)代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 count,group by,having(SQL)
作者:Dezai.CN / 发布于2011/11/22/ 625
<div>
<h1 class="postTitle"><span style="font-size: 12px; font-weight: normal">---所有书籍价格的统计</span></h1>
<span style="font-size: 12px; font-weight: normal">select sum(price)总价,avg(price)均价,max(price)最高价,min(price)最低价</span>
<div class="postBody">
<div id="cnblogs_post_body">
from titles

---统计where条件的记录
---business类型书籍价格的统计
select sum(price)总价,avg(price)均价,max(price)最高价,min(price)最低价
from titles where type='business'

--count返回记录的条数
--返回作者共来自几个州
select count (distinct state)州数 from authors 

select count(au_id) from authors
--返回表的记录的条数
select count(*) from authors

select * from titles

--type类型的记录条数
select count(distinct type) from titles

select count(title_id) from titles

--group by
--返回各个类别的书籍的统计
select type, sum(price) 总价,avg(price) 均价,max(price) 最高价,min(price) 最低价,
count(*) 条数 from titles group by type


--返回各个出版社分别出版书籍的数量并排序(降序)
select * from titles
select pub_id, count(*) 数量 from titles group by pub_id order by 数量 desc


---1389出版社出版的书籍数量
select * from titles
select count(*) 数量 from titles where pub_id=1389

--对type,pub_id进行分组统计
select count(*) 数量,type,pub_id from titles group by type,pub_id 
order by 数量 desc

--having筛选组
--返回类别的均价>15的书籍的统计
select avg(price)均价,type from titles group by type having avg(price)>15
--注:先求平均值,再求均价>15的记录.

select avg(price) 均价,type from titles
where price>15 group by type
--注:先求价格>15的记录,再根据类别求其价格>15的均价.


--要返回平均价格在13到18之间的图书分类
select avg(price) 均价,type from titles group by type 
having avg(price) between 13 and 18

--返回出版书籍的数量>=6的出版社编号
select * from titles

select count(*) 数量,pub_id from titles
group by pub_id having count(*)>=6

--返回作者人数最多的state名字
select * from authors

select top 1 state,count(*)数量 from authors group by state
order by count(*) desc

--返回business,mod_cook这两个类别的统计信息
select * from titles

select type,sum(price) 总价,avg(price) 均价,max(price) 最高价,min(price) 最低价
from titles where type in('business','mod_cook') group by type
--注:先根据where条件将business,mod_cook类别的书籍选出,再进行统计.

select type,sum(price) 总价,avg(price) 均价,max(price) 最高价,min(price) 最低价
from titles group by type having type in('business','mod_cook')
--注:先进行统计,再根据where条件将business,mod_cook类别的书籍选出.

--------------------------------------------------

<pre id="best-answer-content" class="reply-text mb10">
如：
  查询学生的平局成绩大于60的
  SELECT FSno,AVG(FGrade) AS FAvgGrade
  FROM TStudent
  GROUP BY FSno
  HAVING AVG(FGrade) > 60 
等价于
  SELECT *
  FROM 
  (
    SELECT FSno,AVG(FGrade) AS FAvgGrade
    FROM TStudent
    GROUP BY FSno
  ) AS T
WHERE FAvgGrade>60
:having性能更高，代码更简洁

关于能够用于条件筛选的 
ON:联结(join)筛选
WHERE: 一般条件筛选
HAVING: 分组后的条件筛选</pre>
<pre id="best-answer-content" class="reply-text mb10">
--------------------------------------------------</pre>
<pre id="best-answer-content" class="reply-text mb10"><pre id="content-386877729" class="reply-text mb10">
如果一个列 没有出现在 group by 子句中,那么该列 就不能直接出现在select 后面 或者 having 子句后面,必须加上聚合函数才行.
例如：select min(年龄) ,性别 from 职工表 group by 性别
---若，年龄 没有出现在group by 子句中，必须加聚合函数才能写在select 的后面。</pre>
</pre>
</div>
</div>
</div>
试试其它关键字
　count,group　　by,having　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)