NexTech

CodeZine：SQLによる数独の解法（パズル, SQL）

5-15 クロスジョインで求めた集合と外部結合

答えはこれだった。

完全外部結合でもできない。

なるほど、cross joinまでは考えてたのでいい線まで行ってたんだね。

それにしても、このサイトのレベルはいつもながら無茶。

■ 関連
3-27 full joinとunionの相互変換

UNION　ALL にWHEREを使うサンプルとして作ってみました。
SQLの無駄を省く為にMySQLのユーザー変数も使っています。

■ SQLを短く
月初・月末の週の空白の条件判断を毎行毎に行っているので効率が悪い。SQLは短い。

SELECT
  IFNULL(NULLIF((n - (
    @f:=DAYOFWEEK(CONCAT(DATE_FORMAT(CURDATE(),'%Y/%m'),'/01')))
                                 +1)*((n- @f +1)<=(
    @e:=DATE_FORMAT(LAST_DAY( CURDATE( ) ),'%e'))
                                           ),0),' ') "日",
  IFNULL(NULLIF((n - @f +2)*((n- @f +2)<=@e),0),' ') "月",
  IFNULL(NULLIF((n - @f +3)*((n- @f +3)<=@e),0),' ') "火",
  IFNULL(NULLIF((n - @f +4)*((n- @f +4)<=@e),0),' ') "水",
  IFNULL(NULLIF((n - @f +5)*((n- @f +5)<=@e),0),' ') "木",
  IFNULL(NULLIF((n - @f +6)*((n- @f +6)<=@e),0),' ') "金",
  IFNULL(NULLIF((n - @f +7)*((n- @f +7)<=@e),0),' ') "土"
FROM (
  SELECT  1 n UNION ALL
  SELECT  8   UNION ALL
  SELECT 15   UNION ALL
  SELECT 22   UNION ALL
  SELECT 29 ) AS t1

■ 高速最適化
月初・月末の週の空白の条件判断をそれぞれ1回づつしか行わない。

SELECT
  IFNULL(NULLIF(2 - (
    @f:=DAYOFWEEK(CONCAT(DATE_FORMAT(CURDATE(),'%Y/%m'),'/01'))
                     ),0),' ') "日",
  IFNULL(NULLIF(3 - @f,0),' ') "月",
  IFNULL(NULLIF(4 - @f,0),' ') "火",
  IFNULL(NULLIF(5 - @f,0),' ') "水",
  IFNULL(NULLIF(6 - @f,0),' ') "木",
  IFNULL(NULLIF(7 - @f,0),' ') "金",
  IFNULL(NULLIF(8 - @f,0),' ') "土"
UNION ALL
SELECT
  n-@f,n-@f+1,n-@f+2,n-@f+3,n-@f+4,n-@f+5,n-@f+6
FROM (
  SELECT 9 n UNION ALL SELECT 16 UNION ALL SELECT 23
) as t1
UNION ALL
SELECT
  IFNULL(NULLIF((30 - @f)*((30- @f)<=(
    @e:=DATE_FORMAT(LAST_DAY( CURDATE( ) ),'%e'))
                                       ),0),' '),
  IFNULL(NULLIF((31 - @f)*((31- @f)<=@e),0),' '),
  IFNULL(NULLIF((32 - @f)*((32- @f)<=@e),0),' '),
  IFNULL(NULLIF((33 - @f)*((33- @f)<=@e),0),' '),
  IFNULL(NULLIF((34 - @f)*((34- @f)<=@e),0),' '),
  IFNULL(NULLIF((35 - @f)*((35- @f)<=@e),0),' '),
  IFNULL(NULLIF((36 - @f)*((36- @f)<=@e),0),' ')

■ 解説

    @f:=DAYOFWEEK(CONCAT(DATE_FORMAT(CURDATE(),'%Y/%m'),'/01'))

は今月の1日が何曜日か曜日の位置(日曜1…土曜7)を取ってきてユーザー変数@fに代入します。

    @e:=DATE_FORMAT(LAST_DAY( CURDATE( ) ),'%e'))

は今月の最終日が何日か取ってきてユーザー変数eに代入します。

ちなみに、ユーザー変数に代入しないで、ここの部分をすべてこちらに置き換えても動きます。
但し、SQLが冗長かつ、処理が大変無駄です。

UNIONでORDERは使えるけど
WHEREは直接使えない
なんで結合するSQLごとに それぞれ同じWHEREを指定する

SELECT * FORM A WHERE なんとか
UNION ALL
SELECT * FORM B WHERE なんとか

方法、でもこれだとマージした際のWHEREとかはできないので

SELECT * FROM ( 
SELECT * FORM A
UNION ALL
SELECT * FORM B
)
WHERE なんとか

 という風にサブクエリーにする。

オラクルのカレンダー表示がすばらしい

■

■ 関連

• テーブルのどちらかにデータがない事があるテーブル結合について
• http://www2.moug.net/bbs/acm_v/20070913000009.htm
• http://oshiete1.goo.ne.jp/qa3203522.html

http://www.grapecity.com/japan/devclub/Consultants/how_to_database/012/page05.htm

http://homepage1.nifty.com/kojama/works/rdbms/common.html

 ■ MySQL

1)究極的に変態チックに省略するとこうなる

SELECT
   sum(carrier_id=1) "ドコモ",
   sum(carrier_id=2) "au",
   sum(carrier_id=3) "SoftBank"
 FROM
   access

 2) 一般的にはこちら

 SELECT
   sum(field(carrier_id,1)) "ドコモ",
   sum(field(carrier_id,2)) "au",
   sum(field(carrier_id,3)) "SoftBank"
 FROM
   access

 ■ Oracle

 SELECT
   sum(decode(carrier_id,1,1,0,0) "ドコモ",
   sum(decode(carrier_id,2,1,0,0) "au",
   sum(decode(carrier_id,3,1,0,0) "SoftBank"
 FROM
   access

 ■ PostgreSQL
 ※ Oracleはこっちでもできるようです。

 SELECT
   sum(case when carrier_id=1 then 1 else 0 end) "ドコモ",
   sum(case when carrier_id=2 then 1 else 0 end) "au",
   sum(case when carrier_id=3 then 1 else 0 end) "SoftBank"
 FROM
   access