MySQL - 选择时间范围内的行分组日期和ID

问题描述：

I'm having trouble with a query I need to get some data from my db.

I have a table with the following data:

id    user_id     game_id    date
1     1           1          2015-08-19
2     1           2          2015-08-19
3     1           3          2015-08-19
4     2           1          2015-08-20
5     2           2          2015-08-20
6     1           1          2015-08-20
7     1           1          2015-08-20
8     3           1          2015-08-20
9     2           1          2015-08-21
10    1           1          2015-08-21
11    1           2          2015-08-21
12    2           1          2015-08-21

I need to get the total amount of users that "played" on a date range, i.e 2015-08-10 to 2015-08-22, grouped by date and user_id. For example, with this data given the result should be:

total_players    date
1                2015-08-19
3                2015-08-20
2                2015-08-21

I've tried this the following query but it doesn't group correctly:

SELECT date, COUNT(*) AS total_players 
FROM plays 
WHERE date BETWEEN '$start_date' AND '$end_date' 
GROUP BY date, user_id

Can anybody help me with the correct query? Thanks everyone in advance.

我遇到查询问题我需要从数据库中获取一些数据。 p>

我有一张包含以下数据的表： p>

  id user_id game_id date 
1 1 1 2015-08-19 
2 1 2 2015-08-19  
3 1 3 2015-08-19 
4 2 1 2015-08-20 
5 2 2 2015-08-20 
6 1 1 2015-08-20 
7 1 1 2015-08-20 
8 3 1 2015  -08-20 
9 2 1 2015-08-21 
10 1 1 2015-08-21 
11 1 2 2015-08-21 
12 2 1 2015-08-21 
  code>  pre>  
 
 我需要获得在日期范围内“播放”的用户总数，即2015-08-10到2015-08-22，按日期和user_id分组。
例如，用此 给出结果的数据应为： p> 
 
 
  total_players date 
1 2015-08-19 
3  2015-08-20 
2 2015-08-21 
  code>  pre> 
 
 我试过这个以下查询，但它没有正确分组： p> \  n 
 
  SELECT日期，COUNT（*）AS total_players 
FROM播放
WHERE date BETWEEN'$ start_date'和'$ end_date'
GROUP BY date，user_id 
  code>  pre>  
 
 任何人都可以帮我正确查询吗？
提前谢谢所有人。 p> 
  div>

答

SELECT date, COUNT(DISTINCT user_id) 
FROM plays
WHERE date BETWEEN '$start_date' AND '$end_date' 
GROUP BY date

Tested locally on test database with your data

total_players    date
1                2015-08-19
3                2015-08-20
2                2015-08-21

答

http://sqlfiddle.com/#!9/08957/3

SELECT `date`, COUNT(DISTINCT user_id) AS total_players 
FROM plays 
GROUP BY `date`

SELECT `date`, COUNT(DISTINCT user_id) AS total_players 
FROM plays 
WHERE date BETWEEN '$start_date' AND '$end_date' 
GROUP BY `date`

MySQL - 选择时间范围内的行分组日期和ID

相关推荐