mysql - 如何用相同的表列编写sql条件

Question

我有一个名为 fee_category 的表

+------------+--------------+-----------+
| fee_cat_id | fee_cat_name | parent_id |
+------------+--------------+-----------+
|          1 | days scholar |         0 |
|          2 | tuition      |         1 |
|          3 | exam         |         1 |
|          4 | bus          |         1 |
+------------+--------------+-----------+

我想根据parent_id fee_type 获取如下

如果 parent_id=1

然后

feetype=days scholar

我试过如下

SELECT fee_cat_id
     , fee_cat_name
     , fee_cat_name IN 
         ( select fee_cat_name  
             from fee_category  
            where parent_id !=0
         ) fee_type 
 from fee_category; 

我的输出看起来像这样

+------------+--------------+----------+
| fee_cat_id | fee_cat_name | fee_type |
+------------+--------------+----------+
|          1 | days scholar |        0 |
|          2 | tuition      |        1 |
|          3 | exam         |        1 |
|          4 | bus          |        1 |
+------------+--------------+----------+

我要输出如下

+------------+--------------+----------  +
| fee_cat_id | fee_cat_name | fee_type   |
+------------+--------------+----------  +
|          1 | days scholar |  none      |
|          2 | tuition      |days scholar|
|          3 | exam         |days scholar|
|          4 | bus          |days scholar|
+------------+--------------+----------+

Answer 1

您可以为表格命名。

SELECT a.fee_cat_id, a.fee_cat_name, COALESCE(b.fee_cat_name, 'NONE') as fee_type 
FROM fee_category a
LEFT OUTER JOIN fee_category b
ON a.fee_type = b.fee_cat_id;