问匹配cat_subcat并包含空值的cat_ID的类别表

EN

CREATE TABLE Table1
    (`cat_ID` int, `cat_Name` varchar(20), `Cat_SubCat` varchar(4))
;

INSERT INTO Table1
    (`cat_ID`, `cat_Name`, `Cat_SubCat`)
VALUES
    (1, 'cameras', '17'),
    (2, 'intercoms', '17'),
    (3, 'televisions', '20'),
    (4, 'kitchen appliances', '19'),
    (5, 'speakers', '20'),
    (6, 'smart thermostat', '19'),
    (7, 'smart outlets', '18'),
    (8, 'robotic lawn mowers', '17'),
    (9, 'smartphones', '21'),
    (11, 'smart vaccums', '19'),
    (12, 'smartwatches', '21'),
    (13, 'smart locks', '17'),
    (14, 'smart doorbell', '17'),
    (15, 'smart light switches', '18'),
    (17, 'Security/Outdoors', NULL),
    (18, 'Lamps & Lights', NULL),
    (19, 'Household', NULL),
    (20, 'Entertainment', NULL),
    (21, 'Wearables', NULL)
;

select 
  t2.cat_ID,
  t2.cat_Name,
  t2.Cat_SubCat
from table1 t2
left join table1 t1
  on t2.Cat_SubCat = t1.cat_id
where t1.Cat_Subcat is null
order by Coalesce(t2.cat_subcat, t2.cat_Id-0.01)

| cat_ID |             cat_Name | Cat_SubCat |
|--------|----------------------|------------|
|     17 |    Security/Outdoors |     (null) |
|      1 |              cameras |         17 |
|     13 |          smart locks |         17 |
|     14 |       smart doorbell |         17 |
|      8 |  robotic lawn mowers |         17 |
|      2 |            intercoms |         17 |
|     18 |       Lamps & Lights |     (null) |
|     15 | smart light switches |         18 |
|      7 |        smart outlets |         18 |
|     19 |            Household |     (null) |
|     11 |        smart vaccums |         19 |
|      6 |     smart thermostat |         19 |
|      4 |   kitchen appliances |         19 |
|     20 |        Entertainment |     (null) |
|      5 |             speakers |         20 |
|      3 |          televisions |         20 |
|     21 |            Wearables |     (null) |
|      9 |          smartphones |         21 |
|     12 |         smartwatches |         21 |

  SELECT 
    *
  FROM
    Category,
    (
      SELECT cat_ID,
      FROM Category
      WHERE Cat_SubCat is NULL
    ) tempTbl

  WHERE
    (Cat_SubCat is NULL)
    OR
    (tempTbl.cat_ID = Cat_SubCat)

ORDER BY COALESCE(cat_id, cat_subcat) ASC