Question

I have a mysql table named "category". the basic structure looks like this-

------     --------     ------
cat_id     cat_name     parent
------     --------     ------
1          test1        NULL
2          test2        NULL
3          test3        2
4          test4        1
5          test5        3

现在我想到与母类别有关的所有类别数据姓名。在单项询问中(而不仅仅是id)。这是可能的。 i 可以通过使用第二个查询(带孩子的母姓)来做,同时使用整个循环和合并的数据。但这能否用一个单一问题来做?

Answer 1

Join the table with itself, using the parent column to link to the cat_id of the parent.

SELECT c1.cat_id as childID, c1.cat_name ChildName, c2.cat_name as ParentName
from category c1
LEFT OUTER JOIN category c2
ON c1.parent = c2.cat_id

Be careful: since some elements have no parents (NULL), I put a LEFT OUTER JOIN so those rows are displayed as well. If you don t want that, use a JOIN instead of LEFT OUTER JOIN.
You can also show the lines, but display something else (empty or a text or ...) instead of the NULL by using COALESCE.
You can consider the result as one (big) new table, so you can add WHERE clauses as you usually do, for example filtering on the parent name: WHERE c2.cat_name = test2

Answer 2

Select p.cat_id, p.cat_name, c.cat_id, c.cat_name, c.parent 
From category Left Join category On p.cat_id = c.parent 
Where p.cat_name =  name

Answer 3

SELECT c1.category_name AS category, c2. category_name AS sub_category FROM (

SELECT * FROM category ) AS c1 INNER JOIN (

SELECT * FROM category ) AS c2 ON c1.category_id = c2.category_id

友情链接