SQL文でROW_NUMBER() OVERを使ってデータを取得できない

Question

##解決したいこと
SELECT ROW_NUMBER()OVERを使って、データを取得したいが、以下のコードを実行するとエラーが出る。

$sql = 'SELECT ROW_NUMBER() OVER(ORDER BY p.kickoff ASC) as "点数", p.kickoff as "試合日時", c.name as "対戦相手", g.goal_time as "ゴールタイム"
            FROM pairings as p
            LEFT JOIN countries as c ON p.enemy_country_id = c.id
            LEFT JOIN goals as g ON g.pairing_id = p.id
            WHERE g.player_id = :id';
    $sth = $this->dbh->prepare($sql);
    $sth->bindValue(':id', $id, PDO::PARAM_INT);
    $sth->execute();
    $result = $sth->fetchAll(PDO::FETCH_ASSOC);
    return $result;

##該当エラー

SELECT ROW_NUMBER() OVER(ORDER BY p.kickoff ASC) as "点数", p.kickoff as "試合日時", c.name as "対戦相手", g.goal_time as "ゴールタイム"

SQLを実行してみたところ、エイリアスエラーなどが表示される。

#1064 - SQL構文エラーです。バージョンに対応するマニュアルを参照して正しい構文を確認してください。 : '(ORDER BY p.kickoff ASC) as "点数", p.kickoff as "試合日時", c.name as "' 付近 1 行目
など

mysqlのバージョンは5.7です。

##試したこと
・データベースの接続に問題はないか？
→問題ない。一覧取得や他の詳細データを取得できる。

・SELECT文の中身を変更したら、データは取得できるか？
→できる。例： SELECT * FROM playersなどを実行してみた。

ROW_NUMBERの使い方とか調べましたが、自分では解決できず困っています。

Answer 1

$sql = 'SELECT * FROM players WHERE id = :id'

上記で上手くいったと言っても、質問のSQLではplayersは全然関係ないですからね。

確認するなら以下でしょうけど、そもそも:IDがpairingsに対するものなのかは不明ですが。

$sql = 'SELECT * FROM pairings WHERE id = :id'

追記

エイリアスや項目を"で括ってはいけません。　←"で括っても大丈夫でした。5.7での確認では無いですが。

SELECT ROW_NUMBER() OVER(ORDER BY p.kickoff ASC) as 点数, p.kickoff as 試合日時, c.name as 対戦相手, g.goal_time as ゴールタイム

上記で駄目な場合は、「`」で括って下さい。

Comments

[退会済みユーザー]

ご回答ありがとうございます。
""を外したり、``でくくったりしましたが、解決できませんでした。

[sazi]

コマンドなどで実行してエラーにならないSQLをプログラムに実装するのが基本です。
コマンドやDBツールなどで確認して下さい。

[sazi]

バージョンは5.7ですか。
であれば、row_number()は使えません。
window関数が使用できるのは8.0からです。

Answer 2

• 単に合致するレコードがないか
• SQL文が実はまちがっていてエラーを返しているけどPDOが例外処理をしていないか

ではないでしょうか？

Comments

[退会済みユーザー]

レコードが合致するか検証したところ、上記のようにSQL構文エラーが表示されました。例外処理は一応実装しています。

[yambejp]

SQLのバージョンはなんでしょう？
あまり関係ないかもしれませんが
エイリアスのエンクロージャをダブルクォーテーションから
バッククォーテーションに変えてみてください
（"→`）

[退会済みユーザー]

phpmyadminのバージョンは4.9.7です。バッククォーテーションに変更してもエラーは解決しなかったです。

[yambejp]

>phpmyadminのバージョン

いえMySQLのバージョンです
phpMyAdminだとTOPページにサーバのバージョンが表示されると思います

[退会済みユーザー]

失礼いたしました。5.7.32です。

Answer 3

追記
なんか全く使った覚えが無いのでまず動作確認をすすめたのだけれど…仕様になかったらしい。

MySQLではROW_NUMBERが使えない

MySQLでは ROW_NUBER関数が使えません。
ROW_NUMBER関数と同じことをMySQLで実装する場合は「ユーザ変数」を使用します。
※ポテパンから抜粋

参考URL　ポテパン【SQL】連番を振る
https://style.potepan.com/articles/23566.html

8系から使えるようになったとかなんとか。

MySQL でも 8.0 から Window 関数が使えるようになりましたが

※Qiitaから抜粋

参考URL　Qiita　MySQL の Window 関数を完全に理解する
https://qiita.com/ngyuki/items/c35dd314815939ffbeb6

---

あまり得意ではないのだけども…
まず最小構成でテーブルの結合等も行わず、
asも使わずにSQL文が通るかどうか試してみるのはいかがでしょうか。

SELECT ROW_NUMBER() OVER(ORDER BY p.kickoff ASC) score,
FROM pairings

みたいな。テーブル１つ、カラム１つ。※カラムは複数あった方が良いかもしれない。