mysql两个表。类别表category:
ID TYPE1 水果
2 菜
产品表product:id typeid name1 1 苹果
2 2 菜1
3 2 菜2
4 1 西瓜 现在要将类别表的所有类别列出来,如何将本类的产品统计出来呢?只用一条SQL.如: select c.id, c.type, count(p.id) as value from category c left join product p不会写了,求助!!!!谢谢
ID TYPE1 水果
2 菜
产品表product:id typeid name1 1 苹果
2 2 菜1
3 2 菜2
4 1 西瓜 现在要将类别表的所有类别列出来,如何将本类的产品统计出来呢?只用一条SQL.如: select c.id, c.type, count(p.id) as value from category c left join product p不会写了,求助!!!!谢谢
+------+------+-------+
| id | type | value |
+------+------+-------+
| 1 | 水果 | 2 |
| 2 | 菜 | 2 |
+------+------+-------+
2 rows in set (0.00 sec)lz要这样的效果?
from category c left join product p on c.id=p.typeid
group by c.id ,c.type
from category c left join product p on c.id=p.typeid
group by c.id ,c.type
Database changedmysql> create table product (id int,typeid int,name varchar(4));
Query OK, 0 rows affected (0.08 sec)mysql> insert into product select 1,1,'苹果';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into product select 2,2,'菜1';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into product select 3,2,'菜2';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into product select 4,1,'西瓜';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> select c.id, c.type, count(p.id) as value
-> from category c
-> left join product p on c.ID = p.typeid
-> group by c.id, c.type;
+------+------+-------+
| id | type | value |
+------+------+-------+
| 1 | 水果 | 2 |
| 2 | 菜 | 2 |
+------+------+-------+
2 rows in set (0.00 sec)