【備忘録】MYSQLで変数を使う

前回書いたロト6で特定回数一致するSQLで特定の数値と一致するSQLを書く歳に同じ数値を大量に書くのが面倒で調べました。
SQL内でも変数が使えるようなので以下のように記述

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;

SET @num1 := 10;

の部分が変数定義。
これは固定値なのでSETを使用してますが、SELECTで動的に変数を作ることも出来ます。

例としては以下のような感じ。

SELECT @user_count := COUNT(*) FROM users;