页次: 1
首先创建一个表
http://www.ipbbs.net/viewtopic.php?id=165
可以用 条件 group by ,
grup by 字段 having 条件
第一个: 产看每种商品的数量
mysql> select g_name, count(*) as nuns from goods group by g_name ; +--------+------+ | g_name | nuns | +--------+------+ | aaa | 3 | | bbb | 3 | | ccc | 3 | +--------+------+ 3 rows in set (0.00 sec)
第二 :仅仅查看产品aaa的数量
mysql> select g_name, count(*) as nuns from goods group by g_name having g_name = 'aaa'; +--------+------+ | g_name | nuns | +--------+------+ | aaa | 3 | +--------+------+ 1 row in set (0.00 sec)
第三: 产品每个厂家提供的产品都有啥?
mysql> select v_id , group_concat(g_name) from goods group by v_id; +------+----------------------+ | v_id | group_concat(g_name) | +------+----------------------+ | 1 | aaa,bbb,ccc | | 2 | aaa,bbb,ccc | | 3 | aaa,bbb,ccc | +------+----------------------+ 3 rows in set (0.00 sec)
这是按照 厂家的id来分组的。 把厂家的所有的产品“连接起来”
第四:查看每个厂家的商品的评价价格
mysql> select v_id , avg(g_price) from goods group by v_id; +------+--------------+ | v_id | avg(g_price) | +------+--------------+ | 1 | 4.100000 | | 2 | 5.100000 | | 3 | 6.100000 | +------+--------------+ 3 rows in set (0.00 sec)
avg, sum,
第四: 查看有几个厂家
mysql> select count(distinct v_id) from goods; +----------------------+ | count(distinct v_id) | +----------------------+ | 3 | +----------------------+ 1 row in set (0.03 sec)
ipbbs.net
离线
页次: 1