group_concat 中的重複值

我有兩張桌子，第一張是餐桌食物，第二張是活動：

INSERT INTO food
   (`id`, `foodName`)
VALUES
   (1, 'food1'),
   (2, 'food2'),
   (3, 'food3'),
   (4, 'food4'),
   (5, 'food5'),
   (6, 'food6'),
;
CREATE TABLE Activity
   (`id` int,`place` varchar(14),`food_id` int,`timing` TIME,`date_and_time` DATETIME)
;
INSERT INTO Activity
   (`id`,`place`, `food_id`,`timing`,`date_and_time`)
VALUES
   (1, 'place1', 1, '10:30am','2013-05-01'),
   (2, 'place1', 1, '12:30pm','2013-05-01'),
   (3, 'place1', 1, '04:30pm','2013-05-01'),
   (4, 'place2', 2, '10:30am','2013-05-02'),
   (5, 'place2', 2, '12:30pm','2013-05-02'),
   (6, 'place2', 2, '4:30pm','2013-05-02'),
   (7, 'place1', 2, '10:30am','2013-05-02'),
   (8, 'place1', 2, '12:30pm','2013-05-02'),
   (9, 'place1', 2, '4:30pm','2013-05-02'),
   (10, 'place2', 3, '10:30am','2013-05-03'),
   (11, 'place2', 3, '12:30pm','2013-05-03'),
   (12, 'place2', 3, '4:30pm','2013-05-03')
;

現在我正在使用以下查詢：

SELECT 
   a.activity_type AS Activity, 
   COUNT(DISTINCT p.id) AS Products,
   CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ',  ') USING utf8) 
     AS Categories
FROM 
   food AS p
 JOIN 
   ( SELECT activity_type
          , prod_id
     FROM activity 
     WHERE activity_type <> '' 
     GROUP BY activity_type
            , prod_id
   ) AS a
   ON p.id = a.prod_id
GROUP BY 
   activity_type
ORDER BY 
   Products DESC ;

你能幫我嗎，我需要以下格式的輸出：

place | food_id | Timings             |                              |
     |         |---------------------|Date                          |
     |         |Time1 |Time2 | Time3 |                              |
---------------+----------+------------------------------------------|
place1 | 1      | 10:30am| 12:30pm| 4:30pm |2013-05-01(MAX timestamp)|          
place2 | 1      | 10:30am| 12:30am| 4:30am |2013-05-01(MAX timestamp)|

如果您的date_and_time值始終相同，請使用以下查詢：

select place, food_id, '10:30am' as Time1, '12:30pm' as Time2, '4:30pm' as Time3, DATE_FORMAT(MAX(date_and_time), '%Y-%m-%d') as Date from Activity group by food_id, place;

我認為，最好使用 PIVOT。下面的連結顯示了它是如何工作的： http ://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

引用自：https://dba.stackexchange.com/questions/41296