数据库统计问题，麻烦各位帮小弟写个sql

[不止会New]

需求：查询用户表下所有用户在一周前，一个月前，一年前分别发布文章数量得总和。
表关系：文章表中有一个作者ID（author_id）与用户表中用户ID（user_id）相连，文章发布时间（draft_time）进行时间判断。
遇到的问题：无法根据个人显示个人文章总数情况，查出来得数据都是一样的。

select * from(
	select p.user_name  from article_info a LEFT JOIN pf_user p on a.author_id = p.user_id
	) a,( 
	select COUNT(info_id) as info_week from article_info
	where DATE_FORMAT(draft_time,'%y%m%d')+7>DATE_FORMAT(now(),'%y%m%d')
	) b,(
	select COUNT(info_id)  as info_month from article_info
	where month(draft_time)=MONTH(now())
	) c,(
	select COUNT(info_id) as info_three_month from article_info
	where month(draft_time)+3>MONTH(now())
	) d,(
	select COUNT(info_id) as info_year from article_info
	where YEAR(draft_time)=YEAR(now())
	) e

希望各位大神，帮助小弟解决一下

[吉普赛的歌]

你这个是 mysql 吧？ 发一下测试用的 表结构 及 insert 语句 还列出最终的结果， 要不也不好弄。

[不止会New]

引用 1 楼 yenange 的回复:

你这个是 mysql 吧？ 发一下测试用的 表结构 及 insert 语句 还列出最终的结果， 要不也不好弄。

-- ----------------------------
-- Table structure for article_info
-- ----------------------------

CREATE TABLE `article_info` (
  `info_id` int(11) NOT NULL AUTO_INCREMENT,
  `flag` int(11) DEFAULT NULL,
  `head` varchar(512) DEFAULT NULL,
  `content` longtext,
  `attach_file` text,
  `author_id` int(11) DEFAULT NULL,
  `draft_time` varchar(32) DEFAULT NULL,
  `sub_id` int(11) DEFAULT NULL,
  `mark` int(11) DEFAULT NULL,
  `img_all` text,
  `img_hot` varchar(256) DEFAULT NULL,
  `field` int(11) DEFAULT NULL,
  PRIMARY KEY (`info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of article_info
-- ----------------------------
INSERT INTO `article_info` VALUES ('1', '1', '测试', '测试内容', null, '1', '2017-11-15 09:07:35', null null, null, null, null);
INSERT INTO `article_info` VALUES ('2', '1', '测试1', '测试1内容', null, '2', '2017-10-01 09:07:35', null, null, null, null, null);
INSERT INTO `article_info` VALUES ('3', '1', '测试2', '测试2内容', null, '3', '2015-01-01 09:07:35', null, null, null, null, null);

-- ----------------------------
-- Table structure for pf_unit
-- ----------------------------
DROP TABLE IF EXISTS `pf_unit`;
CREATE TABLE `pf_unit` (
  `unit_id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_name` varchar(128) DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  `org_code` varchar(64) DEFAULT NULL,
  `telephone` varchar(32) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `field` int(11) DEFAULT NULL,
  PRIMARY KEY (`unit_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of pf_unit
-- ----------------------------
INSERT INTO `pf_unit` VALUES ('1', '部门1', '部门1', null, null, null, null);
INSERT INTO `pf_unit` VALUES ('2', '部门2', '部门2', null, null, null, null);


-- ----------------------------
-- Table structure for pf_user
-- ----------------------------
DROP TABLE IF EXISTS `pf_user`;
CREATE TABLE `pf_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(128) DEFAULT NULL,
  `account` varchar(64) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  `telephone` varchar(32) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `unit_id` int(11) DEFAULT NULL,
  `flag` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `field` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of pf_user
-- ----------------------------
INSERT INTO `pf_user` VALUES ('1', '用户1', null, null, null, null, null, '1', null, null, null);
INSERT INTO `pf_user` VALUES ('2', '用户2', null, null, null, null, null, '2', null, null, null);

是mysql得，我这个结果就是根据username查得，然后

[不止会New]

查出来得数据相同，不知道错哪里了

[听雨停了]

SELECT b.author_id,b.info_week,c.info_month,d.info_three_month,e.info_year
from(
	select author_id,COUNT(info_id) as info_week from article_info
	where DATE_FORMAT(draft_time,'%y%m%d')+7>DATE_FORMAT(now(),'%y%m%d')
	GROUP BY author_id
	) b 
	INNER JOIN (
	select author_id,COUNT(info_id)  as info_month from article_info
	where month(draft_time)=MONTH(now())
	GROUP BY author_id
	) c ON b.author_id=c.author_id
	INNER JOIN (
	select author_id,COUNT(info_id) as info_three_month from article_info
	where month(draft_time)+3>MONTH(now())
	GROUP BY author_id
	) d ON c.author_id=d.author_id
	INNER JOIN (
	select author_id,COUNT(info_id) as info_year from article_info
	where YEAR(draft_time)=YEAR(now())
	GROUP BY author_id
	) e ON d.author_id=e.author_id

赶紧把你上一个帖子结了

[不止会New]

引用 4 楼 qq_37170555 的回复:

SELECT b.author_id,b.info_week,c.info_month,d.info_three_month,e.info_year
from(
	select author_id,COUNT(info_id) as info_week from article_info
	where DATE_FORMAT(draft_time,'%y%m%d')+7>DATE_FORMAT(now(),'%y%m%d')
	GROUP BY author_id
	) b 
	INNER JOIN (
	select author_id,COUNT(info_id)  as info_month from article_info
	where month(draft_time)=MONTH(now())
	GROUP BY author_id
	) c ON b.author_id=c.author_id
	INNER JOIN (
	select author_id,COUNT(info_id) as info_three_month from article_info
	where month(draft_time)+3>MONTH(now())
	GROUP BY author_id
	) d ON c.author_id=d.author_id
	INNER JOIN (
	select author_id,COUNT(info_id) as info_year from article_info
	where YEAR(draft_time)=YEAR(now())
	GROUP BY author_id
	) e ON d.author_id=e.author_id

赶紧把你上一个帖子结了

哇，老哥，感谢，这两个都结了