mysql - 如何统计该类别的产品数量？

Question

如何统计该类别的产品数量？

表格类别

category_id 
title 
lft 
rght 
parent 
level

表格产品

product_id 
category_id 
title

数据表格类别

|1|Electronics|1|16|0|0
|2|Televisions|2|7|1|1
|3|LCD|3|4|2|2
|4|PLASMA|5|6|2|2
|5|Players|8|15|1|1
|6|Mp3 players|9|10|5|2
|7|CD players|11|12|5|2
|8|DVD players|13|14|5|2
|9|Furniture|17|18|0|0

表格产品

|1|4|Plasma 1
|2|4|Plasma 2
|3|4|Plasma 3
|4|4|Plasma 4
|5|4|Plasma 5
|6|3|LCD 1
|7|3|LCD 2
|8|3|LCD 3
|9|6|MP3 1
|10|6|MP3 2
|11|7|CD 1
|12|7|CD 2
|13|8|DVD 1
|14|8|DVD 2
|15|8|DVD 3

我想数一下这些例子。

Electronics (15)
Televisions (8)
LCD (3)
PLASMA (5)
Players (7)
Mp3 players (2)
CD players (2)
DVD players (3)
Furniture (0)

并使用

SQL

select parent.title, parent.level, count(product.title) as sum_products  
from category as node, category as parent, product  
where node.lft between parent.lft and parent.rght   
and node.category_id = product.category_id  
group by parent.title   
order by node.lft

这些结果

Electronics (15)
Televisions (8)
LCD (3)
PLASMA (5)
Players (7)
Mp3 players (2)
CD players (2)
DVD players (3)

我如何像 sample 一样做出来？

家具 (0) 未在查询中显示。

感谢您的帮助。

select parent.title, parent.level, coalesce(count(product.title),0) as sum_products from category as node, category as parent, product where node.lft between parent.lft and parent.rght and node.category_id = product.category_id group by parent.title order by node.lft

http://i.stack.imgur.com/adJd2.png

谢谢jpw的解答

此代码完成

select 
  parent.title, 
  parent.level, 
  count(product.title) as sum_products  
from category as node
join category as parent 
  on node.lft between parent.lft and parent.rght   
left join product 
  on node.category_id = product.category_id  
group by parent.title, parent.level
order by node.lft

|       TITLE | LEVEL | SUM_PRODUCTS |
|-------------|-------|--------------|
| Electronics |     0 |           15 |
| Televisions |     1 |            8 |
|         LCD |     2 |            3 |
|      PLASMA |     2 |            5 |
|     Players |     1 |            7 |
| Mp3 players |     2 |            2 |
|  CD players |     2 |            2 |
| DVD players |     2 |            3 |
|   Furniture |     0 |            0 |

Answer 1

如果问题是排除了空类别家具，我相信解决方案是使用显式左连接而不是对产品表的隐式内连接，如下所示:

select 
  parent.title, 
  parent.level, 
  count(product.title) as sum_products  
from category as node
join category as parent 
  on node.lft between parent.lft and parent.rght   
left join product 
  on node.category_id = product.category_id  
group by parent.title, parent.level
order by node.lft

Sample SQL Fiddle

此查询将给出以下输出:

|       TITLE | LEVEL | SUM_PRODUCTS |
|-------------|-------|--------------|
| Electronics |     0 |           15 |
| Televisions |     1 |            8 |
|         LCD |     2 |            3 |
|      PLASMA |     2 |            5 |
|     Players |     1 |            7 |
| Mp3 players |     2 |            2 |
|  CD players |     2 |            2 |
| DVD players |     2 |            3 |
|   Furniture |     0 |            0 |