カテゴリ
以前の記事
2024年 03月 2024年 01月 2023年 12月 2023年 08月 2023年 06月 2023年 05月 2023年 03月 2022年 08月 2021年 11月 2021年 10月 2021年 02月 2020年 12月 2020年 11月 2020年 10月 2020年 06月 2020年 05月 2018年 02月 2017年 12月 2017年 11月 2017年 09月 2017年 08月 2016年 07月 2016年 06月 2016年 05月 2015年 12月 2015年 10月 2015年 09月 2015年 07月 2015年 04月 2015年 03月 2015年 02月 2014年 07月 2012年 03月 2012年 02月 2011年 11月 2011年 06月 2011年 05月 2011年 04月 2011年 02月 2010年 11月 2010年 09月 2010年 06月 2009年 04月 2009年 03月 2009年 02月 2008年 12月 2008年 02月 2007年 10月 2007年 08月 2006年 04月 2006年 03月 2005年 08月 2005年 06月 2005年 04月 2005年 03月 2005年 01月 2004年 10月 2004年 07月 2004年 06月 2004年 05月 2004年 04月 2004年 03月 2004年 02月 フォロー中のブログ
その他のジャンル
ファン
記事ランキング
ブログジャンル
画像一覧
|
前提
①階層情報は存在しない、代わりに親のキー(ID)を持つ→parent_id ルートノードだった場合のparent_idは0 ②階層ごとのインデックス番号は存在しない、代わりに一つ前のキー(ID)を持つ→prev_id 同一階層の前のデータが存在しない場合prev_idは0 で、階層の深さを取得しつつ、前後関係を維持したレコードを取得する 適当にコピペして作ったのでたぶんバグがある・・・。 ・・ 階層を取得するSQLと順番を取得するSQLをWITH RECURSIVEで別々に取得したうえで JOINで結合する。 ORDER BYのところがミソらしい(int配列で整列する・・。) ※やっぱりバグってたので修正・・・ WHEN t.prev_id = 0::numeric THEN (cnt_s_1.id_tree || t.parent_id) || t.id ELSE cnt_s_1.id_tree || t.id END AS id_tree, WHEN t.prev_id = 0::numeric THEN (cnt_s_1.id_tree || 0::numeric) || t.id ELSE cnt_s_1.id_tree || t.id 面倒なので今の状態をそのまま貼り付け(どうせメモだし) WITH RECURSIVE cnt_s AS ( SELECT base.id, base.summary_id, base.parent_id, base.prev_id, ARRAY[base.id] AS id_tree, 1 AS cnt FROM text_structure base WHERE base.prev_id = 0::numeric OR base.parent_id = 0::numeric UNION ALL SELECT t.id, t.summary_id, t.parent_id, t.prev_id, CASE WHEN t.prev_id = 0::numeric THEN (cnt_s_1.id_tree || t.parent_id) || t.id ELSE cnt_s_1.id_tree || t.id END AS id_tree, cnt_s_1.cnt + 1 AS cnt FROM text_structure t JOIN cnt_s cnt_s_1 ON t.summary_id = cnt_s_1.summary_id AND cnt_s_1.id = CASE WHEN t.prev_id = 0::numeric THEN t.parent_id ELSE t.prev_id END AND t.parent_id <> 0::numeric ), depth_s AS ( SELECT base.id, base.summary_id, base.parent_id, base.prev_id, ARRAY[base.id] AS depth_tree, 1 AS depth FROM text_structure base WHERE base.parent_id = 0::numeric UNION ALL SELECT t.id, t.summary_id, t.parent_id, t.prev_id, depth_s_1.depth_tree || t.id AS depth_tree, depth_s_1.depth + 1 AS depth FROM text_structure t JOIN depth_s depth_s_1 ON t.summary_id = depth_s_1.summary_id AND t.parent_id = depth_s_1.id ) SELECT row_number() OVER (ORDER BY cnt_s.id_tree) AS rn, cnt_s.id, cnt_s.summary_id, cnt_s.parent_id, cnt_s.prev_id, CASE WHEN next_s.id IS NULL THEN 0::numeric ELSE next_s.id END AS next_id, depth_s.depth FROM cnt_s LEFT JOIN depth_s ON cnt_s.id = depth_s.id LEFT JOIN text_structure next_s ON cnt_s.parent_id = next_s.parent_id AND cnt_s.id = next_s.prev_id WHERE (cnt_s.id_tree[1] IN ( SELECT t.id FROM text_structure t WHERE t.parent_id = 0::numeric)) ORDER BY cnt_s.id_tree; ALTER TABLE text_tree_view 再修正(なぜかnumericになってた) -- View: text_tree_view -- DROP VIEW text_tree_view; CREATE OR REPLACE VIEW text_tree_view AS WITH RECURSIVE cnt_s AS ( SELECT base.id, base.summary_id, base.parent_id, base.prev_id, ARRAY[base.id] AS id_tree, 1 AS cnt FROM text_structure base WHERE base.prev_id = 0 OR base.parent_id = 0 UNION ALL SELECT t.id, t.summary_id, t.parent_id, t.prev_id, CASE WHEN t.prev_id = 0 THEN (cnt_s_1.id_tree || 0) || t.id ELSE cnt_s_1.id_tree || t.id END AS id_tree, cnt_s_1.cnt + 1 AS cnt FROM text_structure t JOIN cnt_s cnt_s_1 ON t.summary_id = cnt_s_1.summary_id AND cnt_s_1.id = CASE WHEN t.prev_id = 0 THEN t.parent_id ELSE t.prev_id END AND t.parent_id <> 0 ), depth_s AS ( SELECT base.id, base.summary_id, base.parent_id, base.prev_id, ARRAY[base.id] AS depth_tree, 1 AS depth FROM text_structure base WHERE base.parent_id = 0 UNION ALL SELECT t.id, t.summary_id, t.parent_id, t.prev_id, depth_s_1.depth_tree || t.id AS depth_tree, depth_s_1.depth + 1 AS depth FROM text_structure t JOIN depth_s depth_s_1 ON t.summary_id = depth_s_1.summary_id AND t.parent_id = depth_s_1.id ) SELECT row_number() OVER (ORDER BY cnt_s.id_tree) AS rn, cnt_s.id, cnt_s.summary_id, cnt_s.parent_id, cnt_s.prev_id, CASE WHEN next_s.id IS NULL THEN 0 ELSE next_s.id END AS next_id, depth_s.depth, td.sub_title, td.sub_title_icon_name, td.text, td.text_icon_name, td.depth_icon_name FROM cnt_s LEFT JOIN depth_s ON cnt_s.id = depth_s.id LEFT JOIN text_structure next_s ON cnt_s.parent_id = next_s.parent_id AND cnt_s.id = next_s.prev_id LEFT JOIN text_detail td ON cnt_s.id = td.id WHERE (cnt_s.id_tree[1] IN ( SELECT t.id FROM text_structure t WHERE t.parent_id = 0)) ORDER BY cnt_s.id_tree; ALTER TABLE text_tree_view OWNER TO twork;
by rx21
| 2017-08-25 07:04
| Program
|
ファン申請 |
||