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');

-----------------------------------------------
解答 #1

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

----------
解答#2

建立輔助表
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 |
+------+-------------------------------------------------------------------+----------------------+

貼一下圖好了: