Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Admiral Stats のデータから、各艦娘をLv99まで育成済みの提督数を調べる SQL
cat kekkon_yobigun.sql | mysql admiral_stats_development -uroot > kekkon_yobigun.tsv
SELECT
sm.ship_type,
sm.ship_name,
sm.implemented_at + INTERVAL 9 HOUR AS implemented_at_jst,
lv99_cnt.cnt
FROM (
SELECT
lv99.book_no,
COUNT(lv99.admiral_id) AS cnt
FROM (
SELECT
ss.admiral_id,
sm.book_no
FROM ship_statuses ss
LEFT JOIN ship_masters sm ON ss.book_no = sm.book_no
WHERE
ss.exported_at >= '2017-12-11 15:00:00' AND ss.exported_at < '2018-02-09 15:00:00'
AND ss.level = 99
AND sm.remodel_level = 0
GROUP BY ss.admiral_id, ss.book_no
) lv99
GROUP BY lv99.book_no ORDER BY cnt DESC
) lv99_cnt
LEFT JOIN ship_masters sm ON lv99_cnt.book_no = sm.book_no;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment