How can I get all parents' parents as columns

问题:

I have a table with columns like

entityID, entityName, parentID

How can I write a query to return all the levels of parents for an entity as to return something like

childentityname, parentlevel1name, parentlevel2name, parentLevel3name and so on

I am not a SQL ninja by any means. Is this possible? If so, how?

I'm using Microsoft SQL Server DB.

回答1:

A recursive CTE is what you need look here (EDIT: Only in SQL SERVER 2005+)

Something along the lines of:

WITH recurse_cte (entityID,entityName, parentID, Level)
AS
(
-- Anchor member definition
    SELECT e.entityID,e.entityName, e.parentID,
        0 AS Level
    FROM self_joined AS e
        UNION ALL
-- Recursive member definition
    SELECT e.entityID,e.entityName, e.parentID,
        Level + 1
     FROM self_joined AS e
    INNER JOIN recurse_cte AS cte
        ON e.entityID = cte.parentID
)

select * from recurse_cte

回答2:

On postgres, this is exactly what WITH RECURSIVE is for. You probably don't need to do much more than change column names from the (linked here) documentation.

I don't know if the OP's DB supports recursive, probably depends on version number. If available, the syntax will be similar or identical. If not, it's a big nuisance. It's very hard to make a pure SQL solution, especially if the number of levels is unbounded.

回答3:

SELECT 
  'accounts'.'id' AS id_0,
  'accounts'.'child_id' AS child_id_0, 
  'child_accounts_1'.'id' AS id_1, 
  'child_accounts_1'.'child_id' AS child_id_1, 
  'child_accounts_2'.'id' AS id_2, 
  'child_accounts_2'.'child_id' AS child_id_2, 
  'child_accounts_3'.'id' AS id_3, 
  'child_accounts_3'.'child_id' AS child_id_3, 
  'child_accounts_4'.'id' AS id_4, 
  'child_accounts_4'.'child_id' AS child_id_4
FROM 
  'accounts' 
LEFT OUTER JOIN 'accounts' 'child_accounts_1'
  ON 'child_accounts_1'.'id' = 'accounts'.'child_id'
LEFT OUTER JOIN 'accounts' 'child_accounts_2'
  ON 'child_accounts_2'.'id' = 'child_accounts_1'.'child_id'
LEFT OUTER JOIN 'accounts' 'child_accounts_3'
  ON 'child_accounts_3'.'id' = 'child_accounts_2'.'child_id'
LEFT OUTER JOIN 'accounts' 'child_accounts_4'
  ON 'child_accounts_4'.'id' = 'child_accounts_3'.'child_id'
WHERE 'accounts'.'id' = 56

This is very similar to what you're doing except mine is a hierarchy of children.

The accounts table has an attribute negative_overflow_account_id which references itself. This here will grab the 'id' and 'negative_overflow_id' of the first 5 layers of the nesting.

I wrote in my code a loop that will generate this query based on the constant MAX_OVERFLOW which will generate this when set to '5', and will do more/less if a different number is used.

Basically my use-case was to make sure someone wasn't setting an infinitely circular loop, so if it hits level 5 then an error goes to the user telling them they can't set it that deep. And if any of the levels references the top-level or one of the previous levels then an error is also generated indicating circular recursion (Which would crash the app later down the line if allowed to persist).

EDIT: I shortened the names. noone wants to see my stupid ridiculous naming convention for that dumb table ;)