mysql ユーザ定義関数を使ってFizzBuzzをSQLで書いてみる

mysql ユーザ定義関数を使ってFizzBuzzをSQLで書いてみる

社内のskypeグループでSQLでFizzBuzzが話題になったときに書いてみたSQL。
（ただ単にユーザ定義関数を使いたかっただけだったりします。。。）

SQL

create table digit(n int);

insert into digit value(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

set @var:=0, @var3:=3 ,@var5:=5;

select if(fizz is null,if(buzz is null,var,buzz),if(buzz is null,fizz,concat(fizz,buzz))) as '?' from (
select @var3:=@var3-1 , @var5:=@var5-1 , @var:=@var+1 as var,
@fizz:=if (@var3=0,"fizz",null) as fizz,
@buzz:=if (@var5=0,"buzz",null) as buzz,
if (@var3=0,@var3:=3,null),
if (@var5=0,@var5:=5,null)
from(
  select d1.n + d2.n * 10 + 1 n
  from digit d1, digit d2
) d ) a
;

いざ動かしてみると。

mysql> set @var:=0, @var3:=3 ,@var5:=5;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select if(fizz is null,if(buzz is null,var,buzz),if(buzz is null,fizz,concat(fizz,buzz))) as '?' from (
    -> select @var3:=@var3-1 , @var5:=@var5-1 , @var:=@var+1 as var,
    -> @fizz:=if (@var3=0,"fizz",null) as fizz,
    -> @buzz:=if (@var5=0,"buzz",null) as buzz,
    -> if (@var3=0,@var3:=3,null),
    -> if (@var5=0,@var5:=5,null)
    -> from(
    ->   select d1.n + d2.n * 10 + 1 n
    ->   from digit d1, digit d2
    -> ) d ) a
    -> ;
+----------+
| ?        |
+----------+
| 1        |
| 2        |
| fizz     |
| 4        |
| buzz     |
| fizz     |
| 7        |
| 8        |
| fizz     |
| buzz     |
| 11       |
| fizz     |
| 13       |
| 14       |
| fizzbuzz |
| 16       |
| 17       |
| fizz     |
| 19       |
| buzz     |
| fizz     |
| 22       |
| 23       |
| fizz     |
| buzz     |
| 26       |
| fizz     |
| 28       |
| 29       |
| fizzbuzz |
| 31       |
| 32       |
| fizz     |
| 34       |
| buzz     |
| fizz     |
| 37       |
| 38       |
| fizz     |
| buzz     |
| 41       |
| fizz     |
| 43       |
| 44       |
| fizzbuzz |
| 46       |
| 47       |
| fizz     |
| 49       |
| buzz     |
| fizz     |
| 52       |
| 53       |
| fizz     |
| buzz     |
| 56       |
| fizz     |
| 58       |
| 59       |
| fizzbuzz |
| 61       |
| 62       |
| fizz     |
| 64       |
| buzz     |
| fizz     |
| 67       |
| 68       |
| fizz     |
| buzz     |
| 71       |
| fizz     |
| 73       |
| 74       |
| fizzbuzz |
| 76       |
| 77       |
| fizz     |
| 79       |
| buzz     |
| fizz     |
| 82       |
| 83       |
| fizz     |
| buzz     |
| 86       |
| fizz     |
| 88       |
| 89       |
| fizzbuzz |
| 91       |
| 92       |
| fizz     |
| 94       |
| buzz     |
| fizz     |
| 97       |
| 98       |
| fizz     |
| buzz     |
+----------+
100 rows in set (0.00 sec)