不用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().

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 |
+------------+----------+----------+

想達成的查詢結果:
+------------+----------+----------+-----------------
| 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;