C# と VB.NET の質問掲示板

■No92558 (初心者 さん) に返信
> 2項目分を出力したいということです。

こういうことでしょうか？（間違ってるかも）

MySQL は専門外なので、
「GROUP BY ID1, ID2 WITH ROLLUP」と
「GROUP BY ID1, ID2, apple_name WITH ROLLUP」
「GROUP BY ID1, ID2, apple_name, banana_name WITH ROLLUP」
の違いは良く分かっていません。


********* 案1 *********
SELECT
  ID1, ID2, total
, CASE WHEN ID1 IS NULL AND ID2 IS NULL THEN '総計' WHEN ID1 IS NULL OR ID2 IS NULL THEN '小計' ELSE apple_name  END AS apple_name
, CASE WHEN ID1 IS NULL AND ID2 IS NULL THEN '総計' WHEN ID1 IS NULL OR ID2 IS NULL THEN '小計' ELSE banana_name END AS banana_name 
FROM (
  SELECT ID1, ID2, apple_name, banana_name
  , SUM(money) AS total
  FROM tbl_name
  GROUP BY ID1, ID2 /* , apple_name, banana_name */
  WITH ROLLUP
) AS sq


********* 案2 *********
WITH Q1 AS (
  SELECT
    ID1, ID2, apple_name, banana_name
  , SUM(money) AS total
  FROM
    tbl_name
  GROUP BY
    ID1, ID2 /* , apple_name, banana_name */
  WITH ROLLUP
), Q2 AS (
  SELECT
    Q1.*, CASE
      WHEN Q1.ID1 IS NULL AND Q1.ID2 IS NULL THEN '総計'
      WHEN Q1.ID1 IS NULL  OR Q1.ID2 IS NULL THEN '小計'
    END AS TITLE
  FROM Q1
)
SELECT 
    ID1, ID2, total
  , IFNULL(title, apple_name) AS apple_name
  , IFNULL(title, banana_name) AS apple_name
FROM Q2