我有一个挑战性的MySQL问题,超出了我的基本知识,我将非常感谢您的帮助。
我目前有以下查询:
select users.userid, CAST(posts.time AS DATE)
FROM users INNER JOIN posts ON users.post_id = posts.id
样本输出:userid | CAST(posts.time AS DATE)
1............2015-01-05
2............2015-02-06
2............2015-04-07
2............2015-04-07
3............2015-04-07
1............2015-02-06
7............2015-01-05
userid可以重复自己,可以有10个不同的行userid = 1;日期列也是如此。我想计算每个用户id在每个不同的日期有多少行。根据以上数据,输出应为:
-----------------------1----------2--------3---------4--------5--------6-------7
2015-01-05.............1..........0........0.........0........0........0.......1
2015-02-06.............1..........1........0.........0........0........0.......0
2015-04-07.............0..........2........1.........0........0........0.......0
我总共有7个用户。我想进一步用我定义的名称替换用户id;例:我将在标题/标题中定义1显示为Mike, 2显示为George,以此类推……
有可能吗?谢谢所有人。
如果您只有7个用户,并且只会有7个用户,那么转换数据并不太难:
select date(posts.time),
count(case when userid = 1 then userid end) as `1`,
count(case when userid = 2 then userid end) as `2`,
count(case when userid = 3 then userid end) as `3`,
count(case when userid = 4 then userid end) as `4`,
count(case when userid = 5 then userid end) as `5`,
count(case when userid = 6 then userid end) as `6`,
count(case when userid = 7 then userid end) as `7`
users INNER JOIN posts ON users.post_id = posts.id
group by date(posts.time)
演示
如果您的用户数量是可变的,或者容易改变-它会变得很烦人,您最好看看您的应用程序语言来处理它。
这是我所拥有的(我没有为你完成):
SELECT date, SUM(id_1) AS Mike, SUM(id_2) AS George FROM (SELECT CASE id WHEN 1 THEN 1 ELSE 0 END as id_1, CASE id WHEN 2 THEN 1 ELSE 0 END as id_2, date FROM test_dates) as tmp GROUP BY date;
+------------+------+--------+
| date | Mike | George |
+------------+------+--------+
| 2015-01-05 | 1 | 0 |
| 2015-02-06 | 1 | 1 |
| 2015-04-07 | 0 | 2 |
+------------+------+--------+
当你想要的是一个计数时,用1的总和来代替的技巧是一个值得记住的常见报告技巧。