MySQL日期/作者比较 [英] MySQL date/author comparison
问题描述
在StackOverflow上有一个关于该主题的先前问题(可以插入链接，目前我没有任何特权)，标题为"MySQL日期比较过滤器"，这是对该主题的扩展.
There was a previous question on StackOverflow about this subject (can insert the link, I've got no privileges for the moment) entitled "MySQL date comparison filter", and this goes as an extension of that one.
我的WordPress博客上有一些作者，我希望通过MySQL来提高他们的工作效率.在MySQL下，下一个查询可以很好地在某一天的特定时间范围内获得作者的帖子:
I've got some authors on my WordPress blog and I would like to get their productivity through MySQL. The next query works pretty well under MySQL to get an author's post during certain time range only one day:
SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
JOIN wp_postmeta
ON (wp_posts.ID = wp_postmeta.post_id)
WHERE wp_posts.post_type = 'post'
AND post_author = '50'
AND post_date
BETWEEN STR_TO_DATE('2011-10-27 14:19:17','%Y-%m-%d %H:%i:%s')
AND STR_TO_DATE('2011-10-27 14:51:17','%Y-%m-%d %H:%i:%s')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 100
但这只是我在那个小时范围内的那一天的帖子.我想得到一张表格，其中包含每天和每一位作者的日常数据.在每一天和每一位作者中，应该有该作者在当天和该小时范围内发布的帖子数.
But it gives me just the posts of that day during that hour range. I'd like to get a table with with everyday data filled up for each day and each author. On each day and each author, there should be the number of posts published by that author on that day and on that hour range.
输出应该是这样的:
October Auth1 Auth2 Auth3
1 0 0 0
2 0 0 0
3 0 1 0
4 0 2 0
5 1 0 0
6 0 2 0
7 0 0 0
8 3 0 0
9 0 0 0
10 5 1 0
11 1 0 0
...
31 2 1 1
所以日期应该是一个变量，但是我想包括所有作者，所以我要删除post_author AND行.
So the date should be a variable, but I'd like to include all authors, so I'd remove the post_author AND line.
我不是MySQL的专家，但我想知道这是否可以轻松实现，并将查询结果(或更确切地说，查询结果的某些字段)导出为表格，如所示.
I'm no expert at MySQL but I wonder if this could be done more or less easily and export the query results (or, more exactly, some fields of the query results) as a table, like the one shown.
推荐答案
我认为您应该创建一个日期参考表，填充该表，然后在查询中从该表中填充LEFT OUTER JOIN. 如何显示缺少的日期?"的问题是一个常见的问题，但我会讲无论如何.
I reckon you should create a date reference table, populate that table and then LEFT OUTER JOIN from that table in your query. The problem of 'How Do I Display Missing Dates?' is quite a common SO question but I'll go for it anyway.
预备步骤
在mysql提示符下运行:
At the mysql prompt run:
use WordPress;
步骤1-创建日期参考表
create table all_date
(id int unsigned not null primary key auto_increment,
a_date date not null,
last_modified timestamp not null default current_timestamp on update current_timestamp,
unique key `all_date_uidx1` (a_date));
第2步-填充日期参考表
此表的想法是每个日期都有一行.现在您可以通过运行插入语句来实现此目的，但是为什么不编写一个例程来为您填充它(您可以事件创建一个
The idea of this table is to have one row for every date. Now you could achieve this by running insert statements ad nauseum but why not write a routine to populate it for you (you could event create a MySQL scheduled event to ensure that you always have a complete set of dates in the table. Here's a suggestion for that routine:
DELIMITER //
CREATE PROCEDURE populate_all_dates(IN from_date DATE, IN days_into_future INT)
BEGIN
DECLARE v_date DATE;
DECLARE ix int;
SET ix := 0;
SET v_date := from_date;
WHILE v_date <= (from_date + interval days_into_future day) DO
insert into all_date (a_date) values (v_date)
on duplicate key update last_modified = now();
set ix := ix +1;
set v_date := from_date + interval ix day;
END WHILE;
END//
DELIMITER ;
您现在可以运行:
call populate_all_dates('2011-10-01',30);
要填充10月的所有日期(或仅将days_into_the_future参数设置为所需的任意值).
To populate all the dates for October (or just crank up the days_into_the_future parameter to whatever you want).
现在您有了一个日期参考表，其中包含您感兴趣的所有日期，您可以继续运行10月份的查询:
Now that you have a date reference table with all dates that you're interested in populated you can go ahead and run your query for October:
select day(a.a_date) as 'October',
IFNULL(t.a1,0) as 'Auth1',
IFNULL(t.a2,0) as 'Auth2',
IFNULL(t.a50,0) as 'Auth50'
from all_date a
LEFT OUTER JOIN
(
SELECT date(wp.post_date) as post_date,
sum(case when wp.post_author = '1' then 1 else 0 end) as a1,
sum(case when wp.post_author = '2' then 1 else 0 end) as a2,
sum(case when wp.post_author = '50' then 1 else 0 end) as a50,
count(*) as 'All Auths'
FROM wp_posts wp
WHERE wp.post_type = 'post'
AND wp.post_date between '2011-10-01' and '2011-10-31 23:59:59'
GROUP BY date(wp.post_date)
) t
ON a.a_date = t.post_date
where a.a_date between '2011-10-01' and '2011-10-31'
group by day(a.a_date);
这篇关于MySQL日期/作者比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
