在Magento中一个产品可以加入多个分类中,那么如何获取产品所在的所有分类呢?又要用到SQL了:
1 | select c.sku |
2 | ,GROUP_CONCAT(p.category_id SEPARATOR
',' ) as
categories |
3 | from
catalog_product_entity as
c |
4 | inner
join catalog_category_product
as p |
5 | on
c.entity_id = p.product_id |
6 | group
by c.sku; |
多个分类ID用逗号分开,查询结果如下:
01 | +-------------------+------------+ |
02 | | sku | categories | |
03 | +-------------------+------------+ |
04 | | 1111 | 22 | |
05 | | 1112 | 22 | |
06 | | 1113 | 22 | |
07 | | 1114 | 22 | |
08 | | 1gbdimm | 15,31 | |
09 | | 1tb7200 | 29 | |
10 | | 226bw | 30 | |
11 | | 250gb5400 | 29 | |
12 | | 2gbdimm | 15,31 | |
13 | | 384822 | 23 | |
14 | | 4fasd5f5 | 19 | |
15 | | 500gb5400 | 29 | |
16 | | 500gb7200 | 29 | |
17 | | 512dimm | 15,31 | |
18 | | 750 | 12,26 | |
19 | | 8525PDA | 8 | |
20 | +-------------------+------------+ |
这里的GROUP_CONCAT()函数少见但有用,它可以把多行记录,合并成一行。这在SQL中通常称为:行转列。