如果问题是排除空类别家具我相信一个解决方案是使用显式左连接，而不是隐式内部连接到产品表，如下：

  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和parent.rght 
 left join product 
 on node.category_id = product.category_id 
 group by parent.title，parent.level 
 order by node.lft 
 

示例SQL Fiddle

此查询将给出以下输出：

  | TITLE | LEVEL | SUM_PRODUCTS | 
 | ------------- | ------- | -------------- | 
 |电子| 0 | 15 | 
 |电视| 1 | 8 | 
 | LCD | 2 | 3 | 
 | PLASMA | 2 | 5 | 
 |玩家| 1 | 7 | 
 | MP3播放器| 2 | 2 | 
 | CD播放器| 2 | 2 | 
 | DVD播放器| 2 | 3 | 
 |家具| 0 | 0 | 
 

How to count product in this category?

TABLE CATEGORY

category_id 
title 
lft 
rght 
parent 
level

TABLE PRODUCT

product_id 
category_id 
title

DATA Table category

|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

Table product

|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

I would like to count these example.

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

and use

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

and these result

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

How I'll make out like a sample?

Furniture (0) Don't show in query.

Thank you for your help.

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

Thank you jpw for answer

This code complete

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 |

If the problem is that the empty category furniture is excluded I believe a solution is to use an explicit left join instead of the implicit inner join to the product table like this:

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

This query would give the following output:

|       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 |