Thread: How to inquiry a nest result?
--*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!
--
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.
On 10 November 2011 15:43, shuaixf <shuaixf@gmail.com> wrote: > --*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! > > > > > -- > 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. You can do that with a recursive CTE (common table expression). See the documentation for SELECT in versions 8.4 or later, which contains examples of queries like these. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Hello
2011/11/10 shuaixf <shuaixf@gmail.com>:
> --*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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Appreciate for your help ! -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-inquiry-a-nest-result-tp4981259p4984218.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.