Skip to content

Instantly share code, notes, and snippets.

@suzuki-takashi
Last active August 21, 2020 03:33
Show Gist options
  • Save suzuki-takashi/01566e91aed8e742a7f8cf10114c0cf6 to your computer and use it in GitHub Desktop.
Save suzuki-takashi/01566e91aed8e742a7f8cf10114c0cf6 to your computer and use it in GitHub Desktop.
ランダムに行を取得する
--
--ランダムに行を取得する
--
--【参照元】SQLアンチパターンのP168
--
SELECT A.*
FROM
jtmd AS A
INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY NRJYUTYU ) AS ROWNUM --主キー順に番号を振る
  ,NRJYUTYU
FROM
jtmd
) AS B
ON A.NRJYUTYU = B.NRJYUTYU
AND (SELECT CEIL(RANDOM() * COUNT(NRJYUTYU)) FROM jtmd ) = B.ROWNUM --主キーからランダムに選ぶ
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment