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)