前回書いたロト6で特定回数一致するSQLで特定の数値と一致するSQLを書く歳に同じ数値を大量に書くのが面倒で調べました。
SQL内でも変数が使えるようなので以下のように記述
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SET @num1 := 10; SET @num2 := 11; SET @num3 := 13; SET @num4 := 16; SET @num5 := 24; SET @num6 := 26; SELECT t1.kaisuu AS kaisuu1, t1.num1, t1.num2, t1.num3, t1.num4, t1.num5, t1.num6, t2.kaisuu AS kaisuu2, t2.num1, t2.num2, t2.num3, t2.num4, t2.num5, t2.num6 FROM loto6_tousen_tbl t1 JOIN loto6_tousen_tbl t2 ON t1.kaisuu != t2.kaisuu WHERE ( (t1.num1 IN (t2.num1, t2.num2, t2.num3, t2.num4, t2.num5, t2.num6)) + (t1.num2 IN (t2.num1, t2.num2, t2.num3, t2.num4, t2.num5, t2.num6)) + (t1.num3 IN (t2.num1, t2.num2, t2.num3, t2.num4, t2.num5, t2.num6)) + (t1.num4 IN (t2.num1, t2.num2, t2.num3, t2.num4, t2.num5, t2.num6)) + (t1.num5 IN (t2.num1, t2.num2, t2.num3, t2.num4, t2.num5, t2.num6)) + (t1.num6 IN (t2.num1, t2.num2, t2.num3, t2.num4, t2.num5, t2.num6)) ) >= 4 and (t1.num1 = @num1 or t1.num2 = @num1 or t1.num3 = @num1 or t1.num4 = @num1 or t1.num5 = @num1 or t1.num6 = @num1) and (t1.num1 = @num2 or t1.num2 = @num2 or t1.num3 = @num2 or t1.num4 = @num2 or t1.num5 = @num2 or t1.num6 = @num2) and (t1.num1 = @num3 or t1.num2 = @num3 or t1.num3 = @num3 or t1.num4 = @num3 or t1.num5 = @num3 or t1.num6 = @num3) and (t1.num1 = @num4 or t1.num2 = @num4 or t1.num3 = @num4 or t1.num4 = @num4 or t1.num5 = @num4 or t1.num6 = @num4) and (t1.num1 = @num5 or t1.num2 = @num5 or t1.num3 = @num5 or t1.num4 = @num5 or t1.num5 = @num5 or t1.num6 = @num5) and (t1.num1 = @num6 or t1.num2 = @num6 or t1.num3 = @num6 or t1.num4 = @num6 or t1.num5 = @num6 or t1.num6 = @num6) order by kaisuu1 desc, kaisuu2 desc; |
|
1 |
SET @num1 := 10; |
の部分が変数定義。
これは固定値なのでSETを使用してますが、SELECTで動的に変数を作ることも出来ます。
例としては以下のような感じ。
|
1 |
SELECT @user_count := COUNT(*) FROM users; |
【備忘録】MYSQLで変数を使う
