複数の共通表式の使い方

SQL

「SQLクックブック ―データベースエキスパートのための実践レシピ集」を含むブログ - はてなキーワードで、自分のブログを除いて一番最近のブログのカテゴリ「SQL Server」を読んでびっくり。
はてなダイアリーとか、はてなダイアリーとか、それなんて今日のエントリの内容？
・・・精進します。


で、ちょっと気になったのが、はてなダイアリーと、その続編 (?) のはてなダイアリーについて。

この解決案として、

BETWEEN 条件で絞った WITH 文を入れることで回避はできます

とコメントしたものの、WITH 文はネストできないようです。
嘘のコメントをしてしまいました。

はてなダイアリー

とあるんですが、そんなことないですよー。できます*1 *2。

WITH X([伝票日付], [借方金額], [貸方金額]) AS (
    SELECT
        [伝票日付]
      , [借方金額]
      , [貸方金額]]
    FROM
        [仕訳テーブル]
    WHERE
        [伝票日付] BETWEEN '2007/01/01' AND '2007/12/31'
),
Y([伝票日付], [借方金額], [貸方金額]) AS (
    SELECT
        [伝票日付]
      , [借方金額]
      , 0
    FROM
        X
    WHERE
        借方勘定科目 = N'普通預金'
    UNION ALL
    SELECT
        [伝票日付]
      , 0
      , [貸方金額]
    FROM
        X
    WHERE
        貸方勘定科目 = N'普通預金'
)
SELECT
    [伝票日付]
  , SUM([借方金額]) AS [借方金額]
  , SUM([貸方金額]) AS [貸方金額]
FROM
    Y
GROUP BY
    [伝票日付]
ORDER BY
    [伝票日付]
;

でも・・・

最初の SQL の方が好きです！
更に自分好みに修正すると、

SELECT
    [伝票日付]
  , SUM(CASE [借方勘定科目]
          WHEN N'普通預金' THEN [借方金額]
                           ELSE 0
        END) AS [借方金額]
  , SUM(CASE [貸方勘定科目]
          WHEN N'普通預金' THEN [貸方金額]
                           ELSE 0
        END) AS [貸方金額]
FROM
    [仕訳テーブル]
WHERE
    [伝票日付] BETWEEN '2007/01/01' AND '2007/12/31'
      AND N'普通預金' IN([借方勘定科目], [貸方勘定科目])
GROUP BY
    [伝票日付]
ORDER BY
    [伝票日付]
;

元のエントリには、

このSQLは２度手間なことをしている気がします。

はてなダイアリー

とあるんですけど、どちらかというと WITH 使ったバージョンの方が、2 回テーブルスキャンもしくはシークがかかるわけで、2 度手間です。
それに、こっちの SQL の方がシンプルだと思うんですよね。


追記：
SUM の中に CASE 式があるのが嫌なら、

WITH X AS (
    SELECT
        [伝票日付]
      , CASE [借方勘定科目]
          WHEN N'普通預金' THEN [借方金額]
                           ELSE 0
        END) AS [借方金額]
      , CASE [貸方勘定科目]
          WHEN N'普通預金' THEN [貸方金額]
                           ELSE 0
        END) AS [貸方金額]
    FROM
        [仕訳テーブル]
    WHERE
        [伝票日付] BETWEEN '2007/01/01' AND '2007/12/31'
          AND N'普通預金' IN([借方勘定科目], [貸方勘定科目])
)
SELECT
    [伝票日付]
  , SUM([借方金額])
  , SUM([貸方金額])
FROM
    X
GROUP BY
    [伝票日付]
ORDER BY
    [伝票日付]
;

こうするとか？
いや、そもそも、借方勘定科目と貸方勘定科目のどちらかが普通預金てのは WHERE 句で確定してるんだから、たぶん

伝票日付 借方勘定科目 貸方勘定科目 借方金額 貸方金額
2007/1/13 普通預金 hoge 10000 0
2007/1/13 piyo 普通預金 0 12000
2007/1/13 foo 普通預金 0 5000
2007/1/13 普通預金 bar 2000 0

みたいになってると予想すると、

SELECT
    [伝票日付]
  , SUM([借方金額])
  , SUM([貸方金額])
FROM
    [仕訳テーブル]
WHERE
    [伝票日付] BETWEEN '2007/01/01' AND '2007/12/31'
      AND N'普通預金' IN([借方勘定科目], [貸方勘定科目])
GROUP BY
    [伝票日付]
ORDER BY
    [伝票日付]
;

でいいんじゃないのかな。
・・・たぶん、普通預金の反対側の金額には取引先のコードとかが入っていて、上のような単純な SQL が使えないと予想。

*1:WITH をネストしてるわけではないけど、やりたいことは実現できる

*2:どうでもいいことだけど、WITH 文じゃなくて BNF 的には WITH 節、プログラマのための SQL 第 2 版的には WITH 演算子もしくは共通テーブル式