group by的用法

最新推荐文章于 2024-08-02 17:59:22 发布

zhaohehe123

最新推荐文章于 2024-08-02 17:59:22 发布

阅读量775

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/zhaohehe123/article/details/8852553

数据库专栏收录该内容

5 篇文章

订阅专栏

mysql> use ecshop
Database changed

mysql> select cat_id,count(*) as total from ecs_goods group by cat_id;
+--------+-------+
| cat_id | total |
+--------+-------+
| 2 | 1 |
| 3 | 15 |
| 4 | 3 |
| 5 | 1 |
| 8 | 3 |
| 11 | 2 |
| 13 | 2 |
| 14 | 2 |
| 15 | 2 |
+--------+-------+
9 rows in set (0.00 sec)

mysql> select cat_id,sum(shop_price) as total from ecs_goods group by cat_id;
+--------+----------+
| cat_id | total |
+--------+----------+
| 2 | 823.33 |
| 3 | 26191.00 |
| 4 | 6891.00 |
| 5 | 3700.00 |
| 8 | 226.00 |
| 11 | 62.00 |
| 13 | 67.00 |
| 14 | 108.00 |
| 15 | 140.00 |
+--------+----------+
9 rows in set (0.00 sec)

mysql> select cat_id,max(shop_price) as total from ecs_goods group by cat_id;
+--------+---------+
| cat_id | total |
+--------+---------+
| 2 | 823.33 |
| 3 | 5999.00 |
| 4 | 2878.00 |
| 5 | 3700.00 |
| 8 | 100.00 |
| 11 | 42.00 |
| 13 | 48.00 |
| 14 | 90.00 |
| 15 | 95.00 |
+--------+---------+
9 rows in set (0.03 sec)

mysql> set names gb2312;
Query OK, 0 rows affected (0.00 sec)

mysql> select cat_id,goods_name,max(shop_price) as total from ecs_goods group by cat_id;
+--------+-----------------------+---------+
| cat_id | goods_name | total |
+--------+-----------------------+---------+
| 2 | 恒基伟业G101 | 823.33 |
| 3 | 飞利浦9@9v | 5999.00 |
| 4 | KD876 | 2878.00 |
| 5 | 诺基亚N96 | 3700.00 |
| 8 | 诺基亚N85原装充电器 | 100.00 |
| 11 | 索爱原装M2卡读卡器 | 42.00 |
| 13 | 小灵通/固话50元充值卡 | 48.00 |
| 14 | 移动100元充值卡 | 90.00 |
| 15 | 联通100元充值卡 | 95.00 |
+--------+-----------------------+---------+
9 rows in set (0.00 sec)

mysql> select cat_id,goods_name,avg(shop_price) as total from ecs_goods group by cat_id;
+--------+-----------------------+-------------+
| cat_id | goods_name | total |
+--------+-----------------------+-------------+
| 2 | 恒基伟业G101 | 823.330000 |
| 3 | 飞利浦9@9v | 1746.066667 |
| 4 | KD876 | 2297.000000 |
| 5 | 诺基亚N96 | 3700.000000 |
| 8 | 诺基亚N85原装充电器 | 75.333333 |
| 11 | 索爱原装M2卡读卡器 | 31.000000 |
| 13 | 小灵通/固话50元充值卡 | 33.500000 |
| 14 | 移动100元充值卡 | 54.000000 |
| 15 | 联通100元充值卡 | 70.000000 |
+--------+-----------------------+-------------+
9 rows in set (0.00 sec)

mysql> select cat_id,goods_name,avg(shop_price) as total from ecs_goods group by cat_id having avg(shop_price)>1000;
+--------+------------+-------------+
| cat_id | goods_name | total |
+--------+------------+-------------+
| 3 | 飞利浦9@9v | 1746.066667 |
| 4 | KD876 | 2297.000000 |
| 5 | 诺基亚N96 | 3700.000000 |
+--------+------------+-------------+
3 rows in set (0.00 sec)

mysql> select cat_name from ecs_category where cat_id=3;
+----------+
| cat_name |
+----------+
| GSM手机 |
+----------+
1 row in set (0.00 sec)

mysql> select cat_name from ecs_category where cat_id=3 union select goods_name from ecs_goods where cat_id=3;
+------------------------+
| cat_name |
+------------------------+
| GSM手机 |
| 飞利浦9@9v |
| 诺基亚E66 |
| 索爱C702c |
| 摩托罗拉A810 |
| 诺基亚5320 XpressMusic |
| 夏新N7 |
| 三星SGH-F258 |
| 三星BC01 |
| 金立 A30 |
| 多普达Touch HD |
| P806 |
| 摩托罗拉E8 |
| 诺基亚N85 |
+------------------------+
14 rows in set (0.05 sec)

mysql> select goods_name from ecs_goods where cat_id=3;
+------------------------+
| goods_name |
+------------------------+
| 飞利浦9@9v |
| 诺基亚E66 |
| 索爱C702c |
| 索爱C702c |
| 摩托罗拉A810 |
| 诺基亚5320 XpressMusic |
| 摩托罗拉A810 |
| 夏新N7 |
| 三星SGH-F258 |
| 三星BC01 |
| 金立 A30 |
| 多普达Touch HD |
| P806 |
| 摩托罗拉E8 |
| 诺基亚N85 |
+------------------------+
15 rows in set (0.00 sec)

mysql> select goods_name from ecs_goods where cat_id=(select cat_id from ecs_category where cat_name='GSM手机');
+------------------------+
| goods_name |
+------------------------+
| 飞利浦9@9v |
| 诺基亚E66 |
| 索爱C702c |
| 索爱C702c |
| 摩托罗拉A810 |
| 诺基亚5320 XpressMusic |
| 摩托罗拉A810 |
| 夏新N7 |
| 三星SGH-F258 |
| 三星BC01 |
| 金立 A30 |
| 多普达Touch HD |
| P806 |
| 摩托罗拉E8 |
| 诺基亚N85 |
+------------------------+
15 rows in set (0.05 sec)

mysql> select goods_name from (select * from ecs_goods where cat_id in(3,5)) as tem_name where goods_name like '诺基亚%';
+------------------------+
| goods_name |
+------------------------+
| 诺基亚E66 |
| 诺基亚5320 XpressMusic |
| 诺基亚N85 |
| 诺基亚N96 |
+------------------------+
4 rows in set (0.03 sec)