mysql - 每个用户行当月每天的统计数据

Question

我的组 75(user_group) 有 23 个用户。

然后我有表 users_participated，其中仅当用户参与时才设置行(有时它存在但为 0(不参与))。

现在我需要显示本月每个用户的每一天他是否参与。

我可以用 php 循环来做到这一点。但我想知道是否有一种方法可以仅使用单个 mysql 查询来完成此操作。

   //output html table should look like y = yes n = no 1,2,3,5 are days in month
   user | 1 | 2 | 3 | 4 | 5.. 
   user1| y | n | y | n | n
   user2| n | y | y | n | y

   // user table
   id | user   | group
   1  | user1  |  75
   2  | user2  |  75
   3  | user3  |  75
   4  | user4  |  68

   //participate table
   user_id | participated | date_added
      1    |  1           | 2014-03-29 11:03:00
      2    |  0           | 2014-03-29 11:03:00

//有时它可以是 0，有时如果它为 null，它甚至不在这个表中，如果你知道我的意思的话。

我该怎么做？

Answer 1

一个简单的交叉表查询就可以做到这一点:

SELECT u.user,
       max( if(day(p.date_added) = 1, p.participated, 0 ) ) day1,
       max( if(day(p.date_added) = 2, p.participated, 0 ) ) day2,
       ..............
       ..............
       ..............
       max( if(day(p.date_added) = 28, p.participated, 0 ) ) day28,
       max( if(day(p.date_added) = 29, p.participated, 0 ) ) day29,
       max( if(day(p.date_added) = 30, p.participated, 0 ) ) day30,
       max( if(day(p.date_added) = 31, p.participated, 0 ) ) day31
FROM user u
JOIN participate p
ON u.id = p.user_id
WHERE p.date_added >= '2014-03-01'
  AND p.date_added < '2014-04-01'
GROUP BY u.id

演示:http://sqlfiddle.com/#!9/cd31f/3