在MySQL中将结果分组并显示在列表中？
为此，请结合使用GROUPBY和ORDERBY-
select yourColumnName,count(*) as anyAliasName from yourTableName group by yourColumnName order by yourColumnName;
让我们创建一个表-
mysql> create table demo7 −> ( −> id int NOT NULL AUTO_INCREMENT, −> first_name varchar(50) −> , −> primary key(id) −> );
借助insert命令将一些记录插入表中-
mysql> insert into demo7(first_name) values('John');
mysql> insert into demo7(first_name) values('David');
mysql> insert into demo7(first_name) values('John');
mysql> insert into demo7(first_name) values('Bob');
mysql> insert into demo7(first_name) values('David');
mysql> insert into demo7(first_name) values('David');
mysql> insert into demo7(first_name) values('John');
mysql> insert into demo7(first_name) values('John');使用select语句显示表中的记录-
mysql> select *from demo7;
这将产生以下输出-
+----+------------+ | id | first_name | +----+------------+ | 1 | John | | 2 | David | | 3 | John | | 4 | Bob | | 5 | David | | 6 | David | | 7 | John | | 8 | John | +----+------------+ 8 rows in set (0.00 sec)
以下是将结果分组到MySQL并显示在列表中的查询-
mysql> select first_name,count(*) as frequency from demo7 group by first_name order by first_name;
这将产生以下输出-
+------------+-----------+ | first_name | frequency | +------------+-----------+ | Bob | 1 | | David | 3 | | John | 4 | +------------+-----------+ 3 rows in set (0.00 sec)