[前][次][番号順一覧][スレッド一覧]

mysql:13061

From: 関 俊夫 <関 俊夫 <to-seki@xxxxxxxxxx>>
Date: Sun, 4 Jun 2006 10:30:01 +0900
Subject: [mysql 13061] Re: 同一テーブルから階層別に分けるSQL文をつくりたい

自己解決しました。
F・Yさんありがとうございました。
実現したSQL文を下に載せておきます。
なお4階層目の"id"を取得するではなく、最下階層の"id"を取得するでした。

SELECT a1, a2, a3, a4, a5
FROM (
SELECT name AS a1, NULL AS a2, NULL AS a3, NULL AS a4, id AS a5
FROM sections
WHERE upper_section_id =0
UNION SELECT t1_0.name AS a1, t1_1.name AS a2, NULL AS a3, NULL AS a4, t1_1.
id AS a5
FROM sections AS t1_0, sections AS t1_1, sections AS t1_2, sections AS t1_3
WHERE t1_0.upper_section_id =0
AND t1_1.upper_section_id = t1_0.id
UNION SELECT t1_0.name AS a1, t1_1.name AS a2, t1_2.name AS a3, NULL AS a4,
t1_2.id AS a5
FROM sections AS t1_0, sections AS t1_1, sections AS t1_2, sections AS t1_3
WHERE t1_0.upper_section_id =0
AND t1_1.upper_section_id = t1_0.id
AND t1_2.upper_section_id = t1_1.id
UNION SELECT t1_0.name AS a1, t1_1.name AS a2, t1_2.name AS a3, t1_3.name AS
a4, t1_3.id AS a5
FROM sections AS t1_0, sections AS t1_1, sections AS t1_2, sections AS t1_3
WHERE t1_0.upper_section_id =0
AND t1_1.upper_section_id = t1_0.id
AND t1_2.upper_section_id = t1_1.id
AND t1_3.upper_section_id = t1_2.id
) AS t0
ORDER BY a1, a2, a3, a4

------------------------
関 俊夫
ダンサーズオンライン
http://www.dancers-online.com/



[前][次][番号順一覧][スレッド一覧]

     12277 2005-10-25 09:10 ["fujita" <t-fujita@x] MySQL5.0でシンボリックが有効にならない  
     12278 2005-10-26 08:14 ┣[Tetsuro IKEDA <tetsu]                                       
     12279 2005-10-26 09:49 ┃┗["fujita" <t-fujita@x]                                     
     12280 2005-10-26 09:57 ┃ ┗[Tetsuro IKEDA <tetsu]                                   
     12281 2005-10-26 10:30 ┃  ┗["fujita" <t-fujita@x]                                 
     13055 1938-05-16 13:05 ┗[関 俊夫 <to-seki@xx] 同一テーブルから階層別に分けるSQL文をつくりたい
     13057 2006-06-03 21:06  ┣["F.Y" <fumi_sby@xxxx]                                     
     13059 2006-06-04 06:36  ┃┗[関 俊夫 <to-seki@xx]                                   
     13060 2006-06-04 08:15  ┃ ┗[関 俊夫 <to-seki@xx]                                 
->   13061 2006-06-04 10:30  ┃  ┗[関 俊夫 <to-seki@xx]                               
     13058 2006-06-04 06:31  ┣[関 俊夫 <to-seki@xx]                                     
     13180 2006-07-23 03:25  ┗[demanotto <demanotto]