例如,我在表中有此数据,
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该表本身如何,这样我可以在下面获得此结果?
left join
inner join
id name parent 2 manage self 4 manage 10 6 manage 20
尽我所能,我只想用关键字“ manage”查询行,但我希望 add 的行中的列parent数据与结果中的 manage 行中的as相同。
parent
是否有可能?
编辑:
我实际表格的简化版本- system,
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
我的实际查询已经很乱了…
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
您只需要两次引用该表:
select t1.id, t1.name, t2.id, t2.name from TableA t1 inner join TableA t2 on t1.parent_id = t2.Id
如果要在列表中查看根,请用左联接替换inner。
更新:
我看错了你的问题。在我看来,您总是有两行,管理一行并添加一行。要通过管理进入“添加”,请执行以下操作:
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将它们联接起来:
select * from system inner join ( select * from system where name = 'add' ) s2 on system.parent_id = s2.parent_id where system.name = 'manage'
这将允许您使用s2中的所有列。