0

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

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邦新手 3 級 ‧ 2014-08-12 11:28:59 檢舉

```不用SUM()的方式,要在Create Table時,

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)

codegeass iT邦新手 5 級 ‧ 2014-08-13 12:19:48 檢舉
```hitomitanaka大大:

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

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

### 1 個回答

22

iT邦高手 1 級 ‧ 2014-08-11 23:16:20

``````&lt;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 檢舉