SQLであるカラムが条件に合致する場合に並び替えたい

Question

テーブルの中に状態カラムを持つテーブル(Bテーブル)の条件に応じて出力する順番を変えるSQLを作成したいのですが上手く実現できません。
どのように修正すれば実現できるでしょうか？

SELECT 
　　`A`.*, 
　　`B`.id as B_id, 
　　`C`.id as C_id, 
　　`C`.operating_status as C_operating
FROM `A` 
inner join `B` on `A`.id = `B`.A_id 
left outer join `C` on `C`.B_id = `C`.id 
ORDER BY 
　　`A`.A_name ASC, 
　　FIELD(C_operating, '稼働中', '一時停止中', '終了');

実行しているSQLは細部は省きますが上記のものです。
AテーブルとBテーブルが親子関係にあり、BテーブルとCテーブルが親子関係にあります。
実行結果としては
単にAテーブルのA_nameで並び替えられたものの内部で状態ごと(稼働中、一時停止中、終了中)に順序が変わるだけなので
状態が終了中のみのデータでもA_nameの昇順で上の方に存在してしまいます。
下記イメージ図

本来実現したいこととしては
状態が’稼働中’であるデータを含んでいるものは上。
'一時停止中'、'終了中'のみのものは下。という順番にしたいということです。
上記にあるイメージでは完全に稼働状況別に別れてしまっていて同一A_nameがあっても状態が異なる場合は表示場所が変わってしまいます。

おそらくサブクエリを使うかなにかしないといけないとは思うのですが処理が浮かばないので、お力添えいただきたいです。
足りない情報などがあれば追記しますのでよろしくお願いいたします。

Comments

[maisumakun]

「実行結果」と「本来実現したいこと」の違いがわかりません（どちらでも全く同じデータの順序になるのではないでしょうか？）。

[ig_h]

>maisumakun様
自分の書き方が悪いですねすみません...
実行結果は単にcompaniesテーブルのcompany_nameで並び替えられたものの内部で状態ごと(稼働中、一時停止中、終了中)に順序が変わるだけなので
状態が終了中のみのデータでもcompany_nameの昇順で上の方に存在してしまいます。
実現したいのは状態が’稼働中’であるserviceを含んでいるデータは上。
'一時停止中'、'終了中'のみのものは下。という順番にしたいということです。

[退会済みユーザー]

稼働中,一時停止中,終了って文言を収めたテーブルに、表示順位を示すカラムをもたせて、ORDER BY句に加えれば。一時表を作るとか、サブクエリー＋VALUESとか駆使すればできそうな気がするけど、具体例まではかけない。

[ig_h]

>m6u様
m6u様が言われている表示順位に関してはFIELD句にて実現できているのかな、と思っています。
自分的にやりたい処理は稼働中,一時停止中,終了という文言順での並び替えではあるのですが、
同一company_nameの場合はまとめて出力したい、といった具合であります。

[Orlofsky]

ここに書いた内容と希望する出力結果を質問に反映しては？

[ig_h]

>Orlofsky様
ありがとうございます！確かにそうですね、失念しておりました...

[yambejp]

具体的なデータがないと回答がぶれます
2020/10の一時停止中が２つあったり、終了がすべて同じ取引開始月
だったりするのはどういう優先順位なのでしょうか？
取引開始月がない稼働中のデータはどうい根拠でしょうか？

[ig_h]

>yambejp様
画像の方、確かに取引開始月は必要なかったですね、修正いたします。
申し訳ございません。
ちなみに質問の方に書いてあるSQLに目は通していただいていますでしょうか？
条件に入れていない取引開始月に対しての優先順位はないかと思うので、
回答に対する考慮点としてはあまり関係がないかと思うのですが質問の意図はどちらにあるのでしょうか？

Answer 1

ざっと見た感じの感想では
たんにnameとC_operatingを逆にすればいいだけのような気がしますが

A.A_name ASC,

FIELD(C_operating, '稼働中', '一時停止中', '終了')

FIELD(C_operating, '稼働中', '一時停止中', '終了') ASC,
`A`.A_name ASC

具体的なサンプルもないし何をしたいかわかりません
joinとかしないで単純なサンプルから始めたほうがよいでしょう

create table tbl(id int primary key,service_operating varchar(10),company_name varchar(30));
insert into tbl values
(1,'aaa','ddd'),
(2,'bbb','ggg'),
(3,'ccc','kkk'),
(4,'bbb','iii'),
(5,'ccc','eee'),
(6,'aaa','fff'),
(7,'bbb','hhh');

select * from tbl order by field(service_operating,'bbb','aaa','ccc'),company_name