如何左联接或内部联接表本身 [英] How to left join or inner join a table itself

问题描述

例如，我在表中有此数据，

I have this data in a table, for instance,

id      name        parent      parent_id
1       add         self        100
2       manage      null        100
3       add         10          200
4       manage      null        200
5       add         20          300
6       manage      null        300

我如何left join或inner join该表本身，以便在下面获得此结果?

How can I left join or inner join this table itself so I get this result below?

id      name        parent
2       manage      self
4       manage      10
6       manage      20

您可以尽我所能，只想用关键字'manage'查询行，但我希望 add 行中的parent列数据与<在结果中strong> management 的行.

As you can I that I just want to query the row with the keyword of 'manage' but I want the column parent's data in add's row as the as in manage's row in the result.

有可能吗?

我的实际表的简化版本-system

the simplified version of my actual table - system,

system_id   parent_id   type    function_name       name        main_parent         make_accessible     sort
31          30          left    main                Main        NULL                0                   1
32          31          left    page_main_add       Add         self                0                   1
33          31          left    page_main_manage    Manage      NULL                0                   2

我的实际查询已经很混乱了...

my actual query and it is quite messy already...

SELECT 
    a.system_id,
    a.main_parent,
    b.name, 
    b.make_accessible, 
    b.sort

FROM system AS a

INNER JOIN -- self --
(
    SELECT system_id, name, make_accessible, sort
    FROM system AS s2

    LEFT JOIN -- search --
    (
    SELECT system_id AS parent_id
    FROM system AS s1
    WHERE s1.function_name = 'page'
    ) AS s1

    ON s1.parent_id = s2.parent_id

    WHERE s2.parent_id = s1.parent_id
    AND s2.system_id != s1.parent_id
    ORDER BY s2.sort ASC
) b
ON b.system_id = a.parent_id


WHERE a.function_name LIKE '%manage%'
ORDER BY b.sort ASC

我目前得到的结果

system_id   main_parent     name    make_accessible sort
33          NULL            Main    0                1

但是我在这之后

system_id   main_parent     name    make_accessible sort
33          self            Main    0                1

推荐答案

您只需要两次引用该表:

You just need to reference the table twice:

select t1.id, t1.name, t2.id, t2.name
from TableA t1 
  inner join TableA t2
    on t1.parent_id = t2.Id

如果要在列表中查看根，请用左联接替换内部.

Replace inner with left join if you want to see roots in the list.

更新:

我误解了你的问题.在我看来，您总是有两行，管理一行并添加一行.要通过管理进入添加"，请执行以下操作:

I misread your question. It seems to me that you always have two rows, manage one and add one. To get to "Add" from manage:

select system.*, (select parent 
                    from system s2 
                   where s2.parent_id = system.parent_id 
                     and s2.name = 'add') 
                 AS parent
from system
where name = 'manage'

或者，您可以将表拆分为两个派生表，并通过parent_id将它们联接起来:

Or, you might split the table into two derived tables and join them by parent_id:

select *
  from system
  inner join
  (
     select * from system where name = 'add'
  ) s2
    on system.parent_id = s2.parent_id
  where system.name = 'manage'

这将允许您使用s2中的所有列.

This will allow you to use all the columns from s2.

这篇关于如何左联接或内部联接表本身的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！