有两个表格 product_data一个是记录产品名称的. product_stock一个是记录其中一个产品的库存记录条数(一条记录代表有1个库存) .
product_datapid pname
-----------------------------------
1 iphone1
2 iphone2
4 iphone3
5 iphone4
product_stock
-------------------------------
pid
1
1
1
2
2
3
3
3
3
请问如何由库存多到少排列IPHONE,并显示iphone库存量.
product_datapid pname
-----------------------------------
1 iphone1
2 iphone2
4 iphone3
5 iphone4
product_stock
-------------------------------
pid
1
1
1
2
2
3
3
3
3
请问如何由库存多到少排列IPHONE,并显示iphone库存量.
from product_data a inner join product_stock b on A.pid=B.pid
Group by a.pid,a.pname
Order by count(*) desc
from product_data a left join product_stock b on A.pid=B.pid
Group by a.pid,a.pname
Order by count(*) desc
select a.pid,a.pname,count(B.pid) from product_data a left join product_stock b on A.pid=B.pid
Group by a.pid,a.pname Order by count(*) desc
举例说明。 找一个应该出现而没出现的贴出来看一下。 现在应该是所有product_data表中的记录都会出现。
给出你的 create table , insert into 语句,这样别人可以直接用你的例子进行测试。
mysql> select pid,pname, count(product_stock.pid) from product_data left join pr
oduct_stock using(pid)
-> group by pid
-> order by count(product_stock.pid) desc ;
+------+---------+--------------------------+
| pid | pname | count(product_stock.pid) |
+------+---------+--------------------------+
| 1 | iphone1 | 4 |
| 2 | iphone2 | 2 |
| 5 | iphone4 | 0 |
| 4 | iphone3 | 0 |
+------+---------+--------------------------+
4 rows in set (0.00 sec)mysql>
5和4的没有库存,显示就是0.