2007/11/24 土曜日

CodeZine:SQLによる数独の解法(パズル, SQL)

Filed under: SQL — admin @ 2:04:18

CodeZine:SQLによる数独の解法(パズル, SQL)

No Tags

2007/11/7 水曜日

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

Filed under: SQL — admin @ 3:28:36

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

答えはこれだった。

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

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

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

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

No Tags

2007/10/31 水曜日

SQLで今月のカレンダーを作る(MySQL)

Filed under: mysql, SQL — admin @ 2:40:18

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が冗長かつ、処理が大変無駄です。

No Tags

2007/10/30 火曜日

UNIONでWHERE

Filed under: mysql, SQL — admin @ 21:56:21

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 なんとか

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

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

No Tags

完全外部結合(FULL OUTER JOIN)

Filed under: SQL — admin @ 20:04:01

■ 関連

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

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

No Tags

2007/10/26 金曜日

クロス集計まとめ

Filed under: SQL — admin @ 10:18:50

 ■ 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
No Tags