MySQL GROUP BY问题

Question

我正在尝试构建一个SQL查询，它应该列出所有用户的活动，而不是求和或其他任何东西，只列出所执行的每个活动的完整列表。

我遇到的问题是，我似乎不能通过工作来获得组，以便将每个用户的活动打印出来。

SQLFiddle = http://sqlfiddle.com/#!2/5dce6/2

最后，我将构建这个JSON对象：

users: [{
    userid: '1', 
    name: 'John',
    activities: [{
       activity_typeid: 6431,
       time: "2013-01-01 00:00:00",
       activity_weight: 20
      }, {
       activity_typeid: 6431,
       time: "2013-01-01 00:00:00",
       activity_weight: 20
    }]
}, {
    userid: '2', 
    name: Peter',
    activities: [{
       activity_typeid: 6431,
       time: "2013-01-01 00:00:00",
       activity_weight: 20
      }
    }]
}]    

SQL：

SELECT activity_typeid, userid, time, activity_weight, activityname
FROM activity_entries
WHERE competitionid = '52a99783c5d6f'

Answer 1

SELECT activity_typeid, userid, time, activity_weight, activityname
FROM activity_entries
WHERE competitionid = '52a99783c5d6f' AND userid = someuserid

Answer 2

我认为在这种情况下您不需要GROUP BY。ORDER BY仍然是有意义的。例如：

SELECT activity_typeid, userid, time, activity_weight, activityname
FROM activity_entries
WHERE competitionid = '52a99783c5d6f'
ORDER BY userid

然后，您可以遍历查询结果。如果当前数据具有与以前数据不同的userid，则创建新的user对象，否则只需将新的activity添加到现有user对象的activities中。

Answer 3

我认为你想使用group_concat()

SELECT userid, username, 
       group_concat(concat_ws(',', activity_typeid, time, activity_weight) separator '; ') as activities
FROM activity_entries
WHERE competitionid = '52a99783c5d6f'
GROUP BY userid;

这将把活动放在一个字符串中，每个活动用分号分隔。然后用逗号分隔各个组件。