下使用mysql GROUP_CONCAT和WHERE

Question

我在DB下表

EVENT

ID, TITLE, ... 

票数

ID, TYPE, ID_EVENT 

评论

个

ID, COMMENT, ID_EVENT 

DATES

ID, DATE, ID_EVENT 

一个事件有许多动作，有许多意见和有许多日期。

我使用以下查询从EVENTS表中检索信息，并为每个事件检索投票数，评论数和每个日期。对于他们的最新的一个事件=明日（2015年4月3日）

 SELECT events.id, 
       events.title, 
       GROUP_CONCAT(dates.date) AS dates, 
       COUNT(distinct votes.id) AS votes, 
       COUNT(distinct comments.id) AS comments 
     FROM events 
     LEFT JOIN dates on dates.post_id = events.id 
     LEFT JOIN votes on votes.post_id = events.id AND votes.type = 1 
     LEFT JOIN comments on comments.votes_id = votes.id 
     WHERE dates.date = CURDATE() + INTERVAL 1 DAY 
     GROUP BY events.id 

结果看起来像这样

id title dates       votes comment 
33 Event33 2015-04-03,2015-04-03,2015-04-03 4  0 
39 Event39 2015-04-03      9  1 

为什么日期栏重复同日（明天）??? Event33的日期应为2015-04-01,2015-04-02,2015-04-03。

出了什么问题？

Answer 1

您希望明天的活动有一个。你的查询做到了，但它也切断了所有其他日期。

您需要额外的加入或EXISTS子查询。

您还需要一个DISTINCT上GROUP_CONCAT()，你用它在COUNT()总以同样的方式：

SELECT events.id, 
     events.title, 
     GROUP_CONCAT(DISTINCT dates.date) AS dates, 
     COUNT(DISTINCT votes.id) AS votes, 
     COUNT(DISTINCT comments.id) AS comments 
FROM events 
    LEFT JOIN dates ON dates.post_id = events.id 
    LEFT JOIN votes ON votes.post_id = events.id AND votes.type = 1 
    LEFT JOIN comments ON comments.votes_id = votes.id 
WHERE EXISTS 
     (SELECT 1 
     FROM dates AS dd 
     WHERE dd.date = CURDATE() + INTERVAL 1 DAY 
      AND dd.post_id = events.id 
    ) 
GROUP BY events.id ; 

另一种方法是使用内联子查询。在此不需要GROUP BY或DISTINCT。你的情况A小调缺点，就是加盟comments是通过votes，所以一个子查询有一个额外的加入：

SELECT e.id, 
     e.title, 
     (SELECT GROUP_CONCAT(d.date) 
      FROM dates AS d 
      WHERE d.post_id = e.id 
     ) AS dates, 
     (SELECT COUNT(v.id) 
      FROM votes AS v 
      WHERE v.post_id = e.id AND v.type = 1 
     ) AS votes, 
     (SELECT COUNT(c.id) 
      FROM comments AS c 
      JOIN votes AS v ON c.votes_id = v.id 
      WHERE v.post_id = e.id AND v.type = 1 
     ) AS comments 
FROM events AS e 
WHERE EXISTS 
     (SELECT 1 
     FROM dates AS dd 
     WHERE dd.date = CURDATE() + INTERVAL 1 DAY 
      AND dd.post_id = events.id 
    ) ;