如何计数在这个类别中的产品？ [英] How to count product in this category?

问题描述

如何计算此类别中的产品？

表类别

  category_id 
 title 
 lft 
 rght 
 parent 
 level 
  

TABLE PRODUCT

  product_id 
 category_id 
 title 
  
 
 
 
 DATA 
表类别
  | 1 |电子产品| 1 | 16 | 0 | 0 
 | 2 |电视| 2 | 7 | 1 | 1 
 | 3 | LCD | 3 | 4 | 2 | 2 
 | 4 | PLASMA | 5 | 6 | 2 | 2 
 | 5 |玩家| 8 | 15 | 1 | 1 
 | 6 | Mp3 player | 9 | 10 | 5 | 2 
 | 7 | CD播放器| 11 | 12 | 5 | 2 
 | 8 | DVD播放器| 13 | 14 | 5 | 2 
 | 9 |家具| 17 | 18 | 0 | 0 
  
表产品
  | 1 | 4 |等离子体1 
 | 2 | 4 |等离子体2 
 | 3 | 4 |等离子体3 
 | 4 | 4 |等离子体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 
  
  
 
 
 电子产品（15）
电视机（8）
 LCD（3）
 PLASMA 5）
玩家（7）
 MP3播放器（2）
 CD播放器（2）
 DVD播放器（3）
家具（0）
  
并使用
 
 
 
 SQL 
 
 
> 选择parent.title，parent.level，count（product.title）as sum_products 
从类别作为节点，类别作为父项，产品
其中node.lft在parent.lft和parent.rght 
和node.category_id = product.category_id之间
 group by parent.title 
 order by node.lft 
  

和这些结果

  （5）
电视机（8）
 LCD（3）
 PLASMA（5）
播放器（7）
 MP3播放器（2）
 DVD播放器（3）
  

？

家具（0）不在查询中显示。

感谢您的帮助。 p>

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和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 title）as sum_products 
 from category as node 
 join category as parent 
在node.lft上的parent.lft和parent.rght之间
 left在节点上连接product 
。 category_id = product.category_id 
 group by parent.title，parent.level 
按节点顺序order.lft 
 
 | 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 | 
  

解决方案

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

  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 |

这篇关于如何计数在这个类别中的产品？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！