sql中group by

某图书馆收藏有书籍具有不同的出版年份,管理员需要做一下统计工作:

(1)每一年书籍的数目,如: 2000年有10本书, 2001年有5本书...

(2)每一种书籍的数目,如: 西游记有10本, 三国演义有5本...

(3)每一种书不同年份对应的数目, 如2本西游记在2000出版的,8本在2008年出版...

(4)每一年不同种类书籍的数目,如2000年出版的西游记有2本,三国演义有5本...

(5)某一种书不同年份对应的数目(与(3)的区别在于,(3)需要统计所有的书籍,(5)是统计指定的某一种书籍)

新建表与准备数据(books.sql)

create table `books`(

    id int not null auto_increment,

    title varchar(50) not null,

    year int not null,

    primary key(id)

    )Engine=InnoDB default charset=utf8;

建表:

>>> (echo 'use test'; cat books.sql) | mysql - u root --password  密码;

数据如下:

+----+--------------+------+

| id | title        | year |

+----+--------------+------+

|  1 | 三国演义     | 2000 |

|  2 | 三国演义     | 2000 |

|  3 | 三国演义     | 2000 |

|  4 | 三国演义     | 2010 |

|  5 | 三国演义     | 2010 |

|  6 | 红楼梦       | 2003 |

|  7 | 红楼梦       | 2003 |

|  8 | 红楼梦       | 2003 |

|  9 | 红楼梦       | 2003 |

| 10 | 红楼梦       | 2003 |

| 11 | 红楼梦       | 2016 |

| 12 | 红楼梦       | 2016 |

| 13 | 红楼梦       | 2016 |

| 14 | 红楼梦       | 2012 |

| 15 | 红楼梦       | 2015 |

| 16 | 红楼梦       | 2015 |

| 17 | 水浒传       | 2001 |

| 18 | 水浒传       | 2001 |

| 19 | 水浒传       | 2001 |

| 20 | 水浒传       | 2003 |

| 21 | 水浒传       | 2004 |

| 22 | 水浒传       | 2005 |

| 23 | 水浒传       | 2015 |

| 24 | 水浒传       | 2015 |

| 25 | 西游记       | 2015 |

| 26 | 西游记       | 2015 |

| 27 | 西游记       | 2015 |

| 28 | 西游记       | 2015 |

| 29 | 西游记       | 2012 |

| 30 | 西游记       | 2012 |

| 31 | 西游记       | 2014 |

| 32 | 西游记       | 2014 |

| 33 | 西游记       | 2011 |

| 34 | 西游记       | 2011 |

+----+--------------+------+

34 rows in set (0.00 sec)

1.统计每一年书籍的数目

select year, count(1) as book_nums from books group by year;

结果:

+------+-----------+

| year | book_nums |

+------+-----------+

| 2000 |         3 |

| 2001 |         3 |

| 2003 |         6 |

| 2004 |         1 |

| 2005 |         1 |

| 2010 |         2 |

| 2011 |         2 |

| 2012 |         3 |

| 2014 |         2 |

| 2015 |         8 |

| 2016 |         3 |

+------+-----------+

11 rows in set (0.00 sec)

2.统计每一种书籍的数目

select title, count(1) as book_nums from books group by title;

结果:

+--------------+-----------+

| title        | book_nums |

+--------------+-----------+

| 三国演义     |         5 |

| 水浒传       |         8 |

| 红楼梦       |        11 |

| 西游记       |        10 |

+--------------+-----------+

4 rows in set (0.00 sec)

3.统计每一种书不同年份对应的数目

select title, year, count(1) as book_nums from books group by title, year;

结果:

+--------------+------+-----------+

| title        | year | book_nums |

+--------------+------+-----------+

| 三国演义     | 2000 |         3 |

| 三国演义     | 2010 |         2 |

| 水浒传       | 2001 |         3 |

| 水浒传       | 2003 |         1 |

| 水浒传       | 2004 |         1 |

| 水浒传       | 2005 |         1 |

| 水浒传       | 2015 |         2 |

| 红楼梦       | 2003 |         5 |

| 红楼梦       | 2012 |         1 |

| 红楼梦       | 2015 |         2 |

| 红楼梦       | 2016 |         3 |

| 西游记       | 2011 |         2 |

| 西游记       | 2012 |         2 |

| 西游记       | 2014 |         2 |

| 西游记       | 2015 |         4 |

+--------------+------+-----------+

15 rows in set (0.00 sec)

4.统计每一年不同种类书籍的数目

select year, title, count(1) as book_nums from books group by year, title;

结果:

+------+--------------+-----------+

| year | title        | book_nums |

+------+--------------+-----------+

| 2000 | 三国演义     |         3 |

| 2001 | 水浒传       |         3 |

| 2003 | 水浒传       |         1 |

| 2003 | 红楼梦       |         5 |

| 2004 | 水浒传       |         1 |

| 2005 | 水浒传       |         1 |

| 2010 | 三国演义     |         2 |

| 2011 | 西游记       |         2 |

| 2012 | 红楼梦       |         1 |

| 2012 | 西游记       |         2 |

| 2014 | 西游记       |         2 |

| 2015 | 水浒传       |         2 |

| 2015 | 红楼梦       |         2 |

| 2015 | 西游记       |         4 |

| 2016 | 红楼梦       |         3 |

+------+--------------+-----------+

5.统计某一种书不同年份对应的数目

select title, year, count(1) as book_nums from books where title = "西游记" group by year;

结果:

+-----------+------+-----------+

| title     | year | book_nums |

+-----------+------+-----------+

| 西游记    | 2011 |         2 |

| 西游记    | 2012 |         2 |

| 西游记    | 2014 |         2 |

| 西游记    | 2015 |         4 |

+-----------+------+-----------+

讨论:

(1)与(2)结果不同是因为它们分别从年份和书名对书籍进行分类

(3)与(4)结果其实是一样的,只不过显示的格式不同,看起来不一样,两种方式都是对书籍进行了年份和书名的分类,只是先后顺序不同而已

(5) 指定某一种书的进行年份分类,此时有两种选择.第一种,先找出所有指定的书籍,再按年份进行分类;第二种,先按年份进行分类,再从不同年份中选出指定的书籍,也可以得到结果.

你会选第一种还是第二种?

先看第一种方式:

第一步找出所有的指定书籍,需要把全部书籍查找一遍;第二步,按年份分类,需要将所有指定的书籍查找一遍

第二种方式:

第一步按年份分类,需要把全部数据查找一遍;第二步,在不同年份书籍中找出指定的数据,相当于再次查找一次全部数据

因此,选择第一种方式比较合适,先找到指定的书籍,再按照时间分类.

在sql中, where title = '西游记' 代表找到指定的书籍; group by year 表示按时间分类.那么问题来了,sql执行 where 和 group by 时,哪个先执行?

(毕)

巴特西

sql中group by

最新文章

热门文章