mysqlのサブクエリを同じsql文内で複数回使用したい

Question

select (select count(*)+1 from (select usf_sid, count(*) as ct from usfavo group by usf_sid) as t1 where t1.ct > t2.ct ) as rank from (select usf_sid, count(*) as ct from usfavo group by usf_sid) as t2 where usf_sid=1

現在このようなクエリを実行しております。
内容は、usfavoテーブルをusf_sidでグループ化しその数をカウントします。
その中で usf_sid=1 のものが上位何番目にあたるかを一度のクエリで取得しています。
上記のクエリは正常に動作しますが、問題なのは、

(select usf_sid, count(*) as ct from usfavo group by usf_sid)

このサブクエリですが、select句とfrom句に同じものを２度記述しています。
これをスマートに一回のサブクエリの記述にまとめたいです。

with句は試してみたのですが、mysqlのバージョンが5.7な為か、使用できませんでした。
その他の方法も探してみたのですが、明確な答えが出せなかった為質問させていただきました。
お分かりの方がおられましたらご教示いただけないでしょうか。
よろしくお願いいたします。

select count(*)+1
from (select usf_sid, count(*) as ct1 from usfavo group by usf_sid) t1
where ct1 > (select count(*) from usfavo where usf_sid=1)

こちらのコードで解決いたしました。
ご回答いただいた皆様、ありがとうございます。
ご指摘いただいた点など参考にさせていただき、勉強してきます。

Comments

[yambejp]

create tableとinsert intoで具体的なサンプルデータと、ほしい結果を例示できませんか？

[Orlofsky]

CREATE TABLE文や数件で良いから現状のデータのINSERT文も載せると適切なコメントが付き易いです。コードは 対応しているMarkdownの機能 https://teratail.com/help#about-markdown の[コードを入力]を使いましょう。

[joeast]

ご指摘ありがとうございます、コードの記述について更新しました

[joeast]

ご指摘ありがとうございます、こちらのmysqlクエリで求める結果は得られている状態です。 今回質問させていただいたのは、 「select句とfrom句に同じサブクエリを記述している部分を、一回の記述へまとめられないか」 という部分になります。

[yambejp]

いや、そうじゃなくて、サンプルデータがないと検証ができないので回答がブレるということです

[joeast]

そうなのですね、ご指摘ありがとうございます、承知いたしました。 データを用意します。

Answer 1

どうしても一度はusf_sid毎の件数を取得する必要があるので厳しいです。
MYSQLにrank()があれば、withが使えなくても大丈夫なんですけどね。
usf_sidを限定して取得を前提であれば以下のような記述でも可能です。
但し、性能的には検証できませんので、悪しからず。

select count(*)+1
from (select usf_sid, count(*) as ct1 from usfavo group by usf_sid) t1
where ct1 > (select count(*) from usfavo where usf_sid=1)

#データ

CREATE TABLE usfavo
    (`usf_sid` int, `seq` int)
;
    
INSERT INTO usfavo
    (`usf_sid`, `seq`)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 1),
    (2, 2),
    (2, 3),
    (3, 1),
    (3, 2),
    (4, 1),
    (4, 2),
    (4, 3),
    (4, 4)
;

Comments

[joeast]

saziさんからいただいたコードで正常に動作いたしました。
またサブクエリの重複もなくスマートに記述できており、
こちらのコードを参考にさせていただこうと思います。
ありがとうございました。

[sazi]

質問のSQLと動作の違う点がありました。
存在しないusf_sidを指定された場合に値を返してしまう点です。
※yambejpさんも同様。
その場合にも対応すると結局別な部分で冗長となります。

[joeast]

ありがとうございます、承知いたしました。
現在のロジックでは存在しないusf_sidを指定される場合はまずありませんので、
こちらで運用可能かと考えております。
ご教示ありがとうございます。

Answer 2

saziさんのデータをそのまま使うとすると、こうでもいいかもしれません

select count(*)+1 from(
select 1 from usfavo group by usf_sid
having count(*)>(select count(*) from usfavo where usf_sid=1)
) as sub

ただしやや効率がわるいかも

Comments

[yambejp]

ま、実際のところ「構文を見やすくしたい」ということならviewを使うのがラクなんですけどね

[joeast]

viewについては知識不足でした。
ありがとうございます、調べてみます。