SQLiteで複雑なソート

Question

前提・実現したいこと

DELPHIで社内システムを作っています。
システム内部のデータベース部分にSQLiteを利用していますが
データ一覧表示時の並び順を思うようにできずにいます。
DELPHI部分は大丈夫ですが、データベース(SQLite)は全くの初心者で
手探り状態で開発しています。
SQLiteの構文をご教授頂ければ幸いです。

以下のようにソートして表示したいと思っています。
'上司'カラムは表示しませんので、データそのものを書き換えてしまう方法でも
かまいません。

SQLコード(上図真ん中の状態になります)

CREATE TABLE 社員DATA(ID INTEGER, 名前 TEXT, 社員No TEXT, 上司 TEXT);
INSERT INTO 社員DATA(ID, 名前, 社員No      ) VALUES(1, 'DDD', '111'       );
INSERT INTO 社員DATA(ID, 名前, 社員No      ) VALUES(2, 'PPP', '222'       );
INSERT INTO 社員DATA(ID, 名前, 社員No, 上司) VALUES(3, 'KKK', '333', '111');
INSERT INTO 社員DATA(ID, 名前, 社員No, 上司) VALUES(4, 'EEE', '444', '555');
INSERT INTO 社員DATA(ID, 名前, 社員No      ) VALUES(5, 'VVV', '555'       );
INSERT INTO 社員DATA(ID, 名前, 社員No, 上司) VALUES(6, 'QQQ', '666', '111');
INSERT INTO 社員DATA(ID, 名前, 社員No, 上司) VALUES(7, 'NNN', '777', '555');
INSERT INTO 社員DATA(ID, 名前, 社員No, 上司) VALUES(8, 'HHH', '888', '111');
INSERT INTO 社員DATA(ID, 名前, 社員No, 上司) VALUES(9, 'AAA', '999', '111');

SELECT 名前,社員No,上司 FROM 社員DATA ORDER BY 上司,社員No

宜しくお願いいたします。

試したこと

途中経過のソートは
[ SELECT 名前,社員No,上司 FROM 社員DATA ORDER BY 上司,社員No ]
で上図真ん中の順に表示することは出来ます。
調べると、右の状態にするには自己結合(再帰)とか出てきますが、
理解できずに可能なのかもわかりません。

以下文章、いただいたアドバイスにより考えたことの追記

SQL文で可能であればですが、上方図の真ん中の状態から下図のように上司カラムを
編集してから上司カラムでソートできれば、思っていた通りの結果になりそうです。

SQLでの実行の方が速そうですが、この内容の上司カラムの書き換えは開発環境側(DELPHI)
であれば自分にも出来そうな気がしてますので、そちらも併せてテストしてみようと思います。

Answer 1

ORDER BYの最初を、上司がNULLだったら社員No にしては？
ifnull 関数

Comments

[KoReMoYu]

早速の回答ありがとうございます。

まだすぐに検証はできませんが、後ほど試してみたいと思います。

[KoReMoYu]

アドバイスいただいた方法で、以下のようにしてみました。
[ SELECT 名前,社員No,上司 FROM 社員DATA ORDER BY IFNULL(上司,社員No),社員No ]

かなり思った通りの結果になりましたが、単純に'NUL'を'社員No'に変換して
ソートしますと、上図の例では右の結果の'EEE'と'VVV'が逆になります。
上司である'NUL'のレコードの下に部下が連なるようにしたいと思うのですが･･･

'上司'カラムは表示しませんので、データそのものを書き換えてしまう方法でも
かまわないのですが。

宜しくお願いします。

[Orlofsky]

さて、困りましたね。
わたしなら、社員no 444 と 555 のようなコード体系を入れ替えて上司を再設定することを考えるか、別に順番を調整する列を追加(メンテが面倒だけど)を考えます。

奇特な人がいるかもしれませんから、
実際にSQLを実行して現象を再現しやすいように、質問のテーブル定義とデータを CREATE TABLEとINSERTに変更しては？

[KoReMoYu]

アドバイスありがとうございます。
アドバイスいただいた内容により、質問本文を編集させていただきました。

上司カラムを書き換える方法を考えましたが、SQLで実行できればいいのですが、
開発環境側での実行であれば自分でも出来そうですので、併せてテストしてみようと
思います。

Answer 2

自己解決いたしました。

質問文の追記部に書きました上司カラムの書き換えをDELPHI側から行い、
上司カラムでのソートにより思い通りの順番にすることができました。

SQL文での対応ができるか興味ありましたが、アプリケーションの動作
としては問題ありませんので解決とさせて頂きます。

アドバイスありがとうございました。
お騒がせしました。