php - 如何在 MySQL 的单个查询中选择所有具有 main_category_name 和 parent_id 的 sub_category 记录？

Question

我的类别表如下所示:

------------------------------------
id  |   name         | parent_id   |
------------------------------------
1   |  Vehicles      |   0         |
2   |  Car Insurance |   1         |
3   |  Van Insurance |   1         |
4   |  PhoneRecharge |   0         |
5   |  prepaid       |   4         |
6   |  postpaid      |   4         |

输出应该如下所示:

---------------------------------------------------------
id  | parent_id | main_category_name | sub_category_name|
---------------------------------------------------------
2   |  1        |   Vehicles         | Car Insurance    |
3   |  1        |   Vehicles         | Van Insurance    |
5   |  4        |   PhoneRecharge    | prepaid          |
6   |  4        |   PhoneRecharge    | postpaid         |

要获得上述记录，我需要尽量减少与数据库的交互。所以我需要在单个查询中获得上述数据。

Answer 1

给你:http://sqlfiddle.com/#!9/1b1a7a/14

查询语句:SELECT * FROM category t1 INNER JOIN(SELECT * FROM category WHERE parent_id != 0)t2 ON t1.id = t2.parent_id