iT邦幫忙

0

[MySQL]SELECT多個(case when) FROM ONE TABLE卡關中,想請高手指導小弟~謝謝!

----------TABLE-------------
date type amount


2014-08-08 1 50
2014-08-08 2 60
2014-08-09 1 70
2014-08-09 2 80

想讓查詢的結果變成
date type(1) type(2)


2014-08-08 50 60
2014-08-09 70 80

以下這是小弟不才,錯誤示範的查詢
SELECT date,
(case when type = 1 then amount) as type(1),
(case when type = 2 then amount) as type(2)
FROM TABLE GROUP BY date;
得到的失敗品T-T!
date type(1) type(2)


2014-08-08 50 null

不知是否有哪位大大~能指導一下小弟~!
非常感謝各位!

看更多先前的討論...收起先前的討論...
wolfwang iT邦新手 5 級 ‧ 2014-08-12 11:28:59 檢舉
學習了,原來還可以這樣用。

這裡沒有真正去做sum的動作,所以除了用sum之外,還有別的做法嗎?

疑惑
不用SUM()的方式,要在Create Table時,
宣告 UNIQUE INDEX (idate, type),
確保每天每種型態,只會有一筆資料.

CREATE TABLE ithelp0812(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
idate DATE NOT NULL,
type TINYINT UNSIGNED NOT NULL,
amount INT UNSIGNED NOT NULL,
UNIQUE INDEX (idate, type)
);

INSERT INTO ithelp0812(idate, type, amount) VALUES
('2014-08-08', 1, 50),
('2014-08-08', 2, 60),
('2014-08-09', 1, 70),
('2014-08-09', 2, 80);
--- 才能用以下語法
SELECT a.idate
     , a.Type1 AS 'Type(1)'
     , b.Type2 AS 'Type(2)'
  FROM (SELECT idate
             , amount AS 'Type1'
          FROM ithelp0812
         WHERE type = 1) a
  JOIN
       (SELECT idate
             , amount AS 'Type2'
          FROM ithelp0812
         WHERE type = 2) b
 USING (idate)
 ORDER BY a.idate;

+------------+---------+---------+
| idate      | Type(1) | Type(2) |
+------------+---------+---------+
| 2014-08-08 |      50 |      60 |
| 2014-08-09 |      70 |      80 |
+------------+---------+---------+
2 rows in set (0.00 sec)

若每天每種型態資料,有可能有一筆以上,就要用GROUP BY
搭配SUM().
codegeass iT邦新手 5 級 ‧ 2014-08-13 12:19:48 檢舉
hitomitanaka大大:
之前詢問您的查詢方法,
如果要在每一ROW裡再開一個欄位去查詢前一天的type3的amount我又卡了....想再跟您請教一下~
非常感謝您~願意幫小弟一把!!
已下小弟的失敗查詢法:
SELECT date,
SUM(CASE WHEN type = 1 THEN amount ELSE 0 END) AS sumtype1, 
SUM(CASE WHEN type = 2 THEN amount ELSE 0 END) AS sumtype2,
(SELECT amount FROM report_day WHERE date = date_sub(date, interval 1 day) AND type =0) as date1
FROM report_day WHERE date BETWEEN '2014-08-01' AND '2014-08-31'
GROUP BY date;
+----------------TABLE-------------+
+------------+----------+----------+
| idate      | type	|   amount |
+------------+----------+----------+
| 2014-08-07 |        1 |       30 |
| 2014-08-07 |        2 |       40 |
| 2014-08-07 |        3 |       10 |
| 2014-08-08 |        1 |       50 |
| 2014-08-08 |        2 |       60 |
| 2014-08-08 |        3 |       11 |
| 2014-08-09 |        1 |       70 |
| 2014-08-09 |        2 |       80 |
| 2014-08-09 |        3 |       12 |
+------------+----------+----------+

codegeass iT邦新手 5 級 ‧ 2014-08-13 12:20:16 檢舉
想達成的查詢結果:
+------------+----------+----------+-----------------
| idate      | sumtype1 | sumtype2 |yesterday_type3 | 
+------------+----------+----------+-----------------
| 2014-08-07 |       30 |       40 |              0 |
| 2014-08-08 |       50 |       60 |             10 |
| 2014-08-09 |       70 |       80 |             11 |
+------------+----------+----------+-----------------

INSERT INTO ithelp0812(idate, type, amount) VALUES
('2014-08-07', 1, 30),
('2014-08-07', 2, 40),
('2014-08-07', 3, 10),
('2014-08-08', 1, 50),
('2014-08-08', 2, 60),
('2014-08-08', 3, 11),
('2014-08-09', 1, 70),
('2014-08-09', 2, 80),
('2014-08-09', 3, 12);
SELECT a.idate
     , a.sumtype1 AS 'sumtype1'
     , a.sumtype2 AS 'sumtype2'
     , IFNULL(b.sumtype3, 0) AS 'yesterday_type3'
  FROM (SELECT idate
             , SUM(CASE
                   WHEN type = 1 THEN amount
                   ELSE 0
                   END) AS 'sumtype1'
             , SUM(CASE
                   WHEN type = 2 THEN amount
                   ELSE 0
                   END) AS 'sumtype2'
          FROM ithelp0812
         GROUP BY idate) a
  LEFT JOIN
       (SELECT idate
             , SUM(amount) AS 'sumtype3'
          FROM ithelp0812
         WHERE type = 3
         GROUP BY idate) b
    ON a.idate = DATE_ADD(b.idate, INTERVAL 1 DAY)
 ORDER BY idate;
+------------+----------+----------+-----------------+
| idate      | sumtype1 | sumtype2 | yesterday_type3 |
+------------+----------+----------+-----------------+
| 2014-08-07 |       30 |       40 |               0 |
| 2014-08-08 |       50 |       60 |              10 |
| 2014-08-09 |       70 |       80 |              11 |
+------------+----------+----------+-----------------+

也許你會問為何type3 也用SUM(),按照你目前的資料其實是
無須使用的,但是我覺得後續若有多筆資料時,這樣就繼續
沿用此SQL Command即可.
因為本站的討論有1000字元的限制.
下次可以發新的問題,這樣會比較方便回答喔.
codegeass iT邦新手 5 級 ‧ 2014-08-19 10:03:19 檢舉
了解了~
謝謝您~!

1 個回答

22
一級屠豬士
iT邦高手 1 級 ‧ 2014-08-11 23:16:20
最佳解答
<pre class="c" name="code">CREATE TABLE ithelp0811(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
idate DATE NOT NULL,
type TINYINT UNSIGNED NOT NULL,
amount INT UNSIGNED NOT NULL
);

INSERT INTO ithelp0811(idate, type, amount) VALUES
('2014-08-08', 1, 50),
('2014-08-08', 2, 60),
('2014-08-09', 1, 70),
('2014-08-09', 2, 80);

----
SELECT idate
     , SUM(CASE
           WHEN type = 1 THEN amount
           ELSE 0
           END) AS sumtype1
     , SUM(CASE
           WHEN type = 2 THEN amount
           ELSE 0
           END) AS sumtype2
  FROM ithelp0811
 GROUP BY idate;

+------------+----------+----------+
| idate      | sumtype1 | sumtype2 |
+------------+----------+----------+
| 2014-08-08 |       50 |       60 |
| 2014-08-09 |       70 |       80 |
+------------+----------+----------+

--
增加一些資料
INSERT INTO ithelp0811(idate, type, amount) VALUES
('2014-08-08', 1, 10),
('2014-08-08', 2, 20),
('2014-08-09', 1, 30),
('2014-08-09', 2, 40);

SELECT idate
     , SUM(CASE
           WHEN type = 1 THEN amount
           ELSE 0
           END) AS sumtype1
     , SUM(CASE
           WHEN type = 2 THEN amount
           ELSE 0
           END) AS sumtype2
  FROM ithelp0811
 GROUP BY idate;

+------------+----------+----------+
| idate      | sumtype1 | sumtype2 |
+------------+----------+----------+
| 2014-08-08 |       60 |       80 |
| 2014-08-09 |      100 |      120 |
+------------+----------+----------+

簽名簽名筆記筆記拍手拍手謝謝謝謝

codegeass iT邦新手 5 級 ‧ 2014-08-12 10:03:59 檢舉

謝謝
突然間 我被你打通任督二脈了~

enen1980 iT邦研究生 1 級 ‧ 2014-08-12 10:25:21 檢舉

利害利害~~

我要發表回答

立即登入回答