13

## 試解一個FB上看到的SQL問題.

FB程式人雜誌上看到的

``````CREATE TABLE fb0817(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(1) NOT NULL,
status CHAR(4),
fdate DATE NOT NULL
);

INSERT INTO fb0817(name, status, fdate) VALUES
('A', 'pass', '2013/12/1'),
('B', 'fail', '2013/12/1'),
('C', 'pass', '2013/12/3'),
('A', NULL,   '2013/12/4'),
('A', NULL,   '2013/12/5'),
('B', 'fail', '2013/12/6'),
('C', NULL,   '2013/12/7'),
('C', 'pass', '2013/12/8'),
('A', 'fail', '2013/12/8'),
('D', 'pass', '2013/12/10'),
('A', NULL,   '2013/12/11'),
('A', NULL,   '2013/12/12'),
('B', 'fail', '2013/12/13'),
('B', 'pass', '2013/12/14'),
('B', 'fail', '2013/12/15'),
('D', 'fail', '2013/12/15');

-----------------------------------------------

SELECT a.name
, a.Days
, c.gs
FROM (SELECT name
, GROUP_CONCAT(fdate ORDER BY fdate SEPARATOR ',') AS 'Days'
FROM fb0817
GROUP BY name) a
JOIN (SELECT name
, GROUP_CONCAT(fstat, ':', cntfstat) AS 'gs'
FROM (SELECT name
, ifnull(status, 'null') AS 'fstat'
, COUNT(ifnull(status, 'null')) AS 'cntfstat'
FROM fb0817
GROUP BY name, status) b
GROUP BY name) c
USING (name)
ORDER BY name;

+------+-------------------------------------------------------------------+----------------------+
| name | Days                                                              | gs                   |
+------+-------------------------------------------------------------------+----------------------+
| A    | 2013-12-01,2013-12-04,2013-12-05,2013-12-08,2013-12-11,2013-12-12 | pass:1,null:4,fail:1 |
| B    | 2013-12-01,2013-12-06,2013-12-13,2013-12-14,2013-12-15            | fail:4,pass:1        |
| C    | 2013-12-03,2013-12-07,2013-12-08                                  | null:1,pass:2        |
| D    | 2013-12-10,2013-12-15                                             | fail:1,pass:1        |
+------+-------------------------------------------------------------------+----------------------+

----------

CREATE TABLE fb0817b (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
status CHAR(4) NOT NULL
);

INSERT INTO fb0817b(status) VALUES
('pass'),('fail'),('null');

SELECT a.name
, a.Days
, c.gs
FROM (SELECT name
, GROUP_CONCAT(fdate ORDER BY fdate SEPARATOR ',') AS 'Days'
FROM fb0817
GROUP BY name) a
JOIN (SELECT y.name
, GROUP_CONCAT(y.status, ':', IFNULL(z.cntfstat, 0) ORDER BY y.status SEPARATOR ',') AS 'gs'
FROM (SELECT DISTINCT name
, x.status
FROM fb0817
JOIN fb0817b x) y
LEFT JOIN
(SELECT name
, ifnull(status, 'null') AS 'fstat'
, COUNT(ifnull(status, 'null')) AS 'cntfstat'
FROM fb0817
GROUP BY name, status) z
ON y.name = z.name
AND y.status = z.fstat
GROUP BY y.name) c
USING (name)
ORDER BY name;

+------+-------------------------------------------------------------------+----------------------+
| name | Days                                                              | gs                   |
+------+-------------------------------------------------------------------+----------------------+
| A    | 2013-12-01,2013-12-04,2013-12-05,2013-12-08,2013-12-11,2013-12-12 | fail:1,null:4,pass:1 |
| B    | 2013-12-01,2013-12-06,2013-12-13,2013-12-14,2013-12-15            | fail:4,null:0,pass:1 |
| C    | 2013-12-03,2013-12-07,2013-12-08                                  | fail:0,null:1,pass:2 |
| D    | 2013-12-10,2013-12-15                                             | fail:1,null:0,pass:1 |
+------+-------------------------------------------------------------------+----------------------+
``````

### 5 則留言

0
andyto202
iT邦研究生 4 級 ‧ 2014-08-17 21:59:09

0
a70706z
iT邦新手 5 級 ‧ 2014-08-17 23:27:05

0

iT邦大師 1 級 ‧ 2014-08-17 23:46:03

0

iT邦高手 1 級 ‧ 2014-08-18 08:27:38

0
sten
iT邦新手 3 級 ‧ 2014-12-09 11:54:49