Search Postgresql Archives

Re: How to inquiry a nest result?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello

2011/11/10 shuaixf <shuaixf@xxxxxxxxx>:
> --*Test SQL*
> CREATE TABLE tb(id integer primary key,
>                name varchar(32),
>                parent integer);
>
> INSERT INTO tb VALUES(1, 'iPhone',    NULL);
> INSERT INTO tb VALUES(2, 'HTC',       NULL);
> INSERT INTO tb VALUES(3, 'Motorola',  NULL);
> INSERT INTO tb VALUES(4, 'iPhone3GS', 1);
> INSERT INTO tb VALUES(5, 'G7',        2);
> INSERT INTO tb VALUES(6, 'G8',        2);
> INSERT INTO tb VALUES(7, 'iPhone4',   1);
> INSERT INTO tb VALUES(8, 'iPhone4-white', 7);
> INSERT INTO tb VALUES(9, 'iPhone4-black', 7);
> INSERT INTO tb VALUES(10,'G7-A',      5);
> INSERT INTO tb VALUES(11,'G7-B',      5);
>
> *How to create a SQL to inquiry the result like this:*
> id       name
> ----------------
>  1        iPhone
>  4        iPhone3GS
>  7        iPhone4S
>  8        iPhone4S-white
>  9        iPhone4S-black
>  2        HTC
>  5        G7
> 10        G7-A
> 11        G7-B
>  3        Motorola
>
> Thank you very much!
>
>

postgres=# with recursive x as (select tb.*, tb.id::text as path
                                                 from tb
                                                where parent is null
                                              union all
                                              select tb.*, path ||'|'
||  tb.id
                                                 from tb
                                                        join x
                                                        on tb.parent = x.id)
                      select id, name from x order by path;
 id │     name
────┼───────────────
 1 │ iPhone
 4 │ iPhone3GS
 7 │ iPhone4
 8 │ iPhone4-white
 9 │ iPhone4-black
 2 │ HTC
 5 │ G7
 10 │ G7-A
 11 │ G7-B
 6 │ G8
 3 │ Motorola
(11 rows)

>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-inquiry-a-nest-result-tp4981259p4981259.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux