如何使用mysql进行分层数据模式的选择?

I have used hierarchical data modal to store products in database. Products have only main and subcategory, and this is the result when I retrieving the Full Tree of Products.

我使用了分层数据模式在数据库中存储产品。产品只有主和子类别，这是我检索整个产品树的结果。

SELECT t1.name AS lev1,
             t2.name as lev2, 
             t3.name as lev3,
FROM categories AS t1
    LEFT JOIN categories AS t2 
        ON t2.parent = t1.category_id
    LEFT JOIN categories AS t3 
        ON t3.parent = t2.category_id
WHERE t1.name = 'Products'

+----------+----------------------+-------------------+
| lev1     | lev2                 | lev3              |
+----------+----------------------+-------------------+
| Products | Computers            | Laptops           |
| Products | Computers            | Desktop Computers |
| Products | Computers            | Tab PCs           |
| Products | Computers            | CRT Monitors      |
| Products | Computers            | LCD Monitors      |
| Products | Computers            | LED Monitors      |
| Products | Mobile Phones        | LG Phone          |
| Products | Mobile Phones        | Anroid Phone      |
| Products | Mobile Phones        | Windows Mobile    |
| Products | Mobile Phones        | iPad              |
| Products | Mobile Phones        | Samsung Galaxy    |
| Products | Digital Cameras      | test              |
| Products | Printers and Toners  | NULL              |
| Products | test                 | abc               |
| Products | test2                | NULL              |
| Products | test3                | NULL              |
| Products | Computer Accessaries | USB Cables        |
| Products | Computer Accessaries | Network Cables    |
+----------+----------------------+-------------------+

My question is I need to select level2 and level3 category id along with this select query.

我的问题是，我需要选择level2和level3类别id以及这个select查询。

I tried it something like this:

我试过了

SELECT t1.name AS lev1,
                 t2.name as lev2, 
                 t3.name as lev3,
                 t3.category_id
FROM categories AS t1
    LEFT JOIN categories AS t2 
        ON t2.parent = t1.category_id
    LEFT JOIN categories AS t3 
        ON t3.parent = t2.category_id
WHERE t1.name = 'Products'

But its only provide level3 IDs. like this.

但它只提供三级id。像这样。

+----------+----------------------+-------------------+-------------+
| lev1     | lev2                 | lev3              | category_id |
+----------+----------------------+-------------------+-------------+
| Products | Computers            | Laptops           |           3 |
| Products | Computers            | Desktop Computers |           4 |
| Products | Computers            | Tab PCs           |           5 |
| Products | Computers            | CRT Monitors      |           6 |
| Products | Computers            | LCD Monitors      |           7 |
| Products | Computers            | LED Monitors      |           8 |
| Products | Mobile Phones        | LG Phone          |          10 |
| Products | Mobile Phones        | Anroid Phone      |          11 |
| Products | Mobile Phones        | Windows Mobile    |          12 |
| Products | Mobile Phones        | iPad              |          13 |
| Products | Mobile Phones        | Samsung Galaxy    |          14 |
| Products | Digital Cameras      | test              |          21 |
| Products | Printers and Toners  | NULL              |        NULL |
| Products | test                 | abc               |          20 |
| Products | test2                | NULL              |        NULL |
| Products | test3                | NULL              |        NULL |
| Products | Computer Accessaries | USB Cables        |          23 |
| Products | Computer Accessaries | Network Cables    |          24 |
+----------+----------------------+-------------------+-------------+

Can anybody tell me how can I get both level2 and level3?

有人能告诉我怎样才能同时达到二级和三级吗?

2 个解决方案

#1

If you have approach like if product has only second level category not 3 level category, you can try

如果你有方法，比如如果产品只有二级类别而不是三级类别，你可以试试

SELECT t1.name AS lev1,t2.name as lev2, t3.name as lev3,COALESCE( t3.category_id, t2.category_id ) FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent = t1.category_id
LEFT JOIN categories AS t3 ON t3.parent = t2.category_id WHERE t1.name = 'Products'

Which gives you category id of level 2 when there isn't any 3 level category

当没有任何3级类别时，它会给你2级的类别id

#2

Add to your fields this t2.category_id,

将这个t2.category_id添加到你的字段中，

SELECT t1.name AS lev1,
                 t2.name as lev2, 
                 t2.category_id, 
                 t3.name as lev3,
                 t3.category_id
FROM categories AS t1
    LEFT JOIN categories AS t2 
        ON t2.parent = t1.category_id
    LEFT JOIN categories AS t3 
        ON t3.parent = t2.category_id
WHERE t1.name = 'Products'

#1