How can I group children and parents in a single q

问题:

I have a table that represents a simple parent->child hierarchy, something like:

             Table "public.transactions"
   Column  | Type          |  Modifiers
-----------+---------------+-----------------------------------------------------------
 id        | integer       | not null default nextval('transactions_id_seq'::regclass)
 parent_id | integer       | not null default 0
 amount    | numeric(15,4) | not null default 0.0000

I'd like to display the table with child transactions (those with a parent_id > 0) grouped below their respective parents. e.g.,

parent
   child
   child
parent
   child
parent
parent

(note: nested spaces are only here to visually represent hierarchy, they're not needed for the query results)

Can I do this in a single query? I'm running Postgresql 9.3 in case it matters.

回答1:

For a single level of nesting, this may seem almost trivial:

SELECT *
FROM   transactions
ORDER  BY COALESCE(parent_id, id), id