mysql具有...> avg()不能按预期工作

Question

我创建了两个视图来帮助计算user_diary_number，然后选择日记数量>总用户user_diary_number平均值的用户。

下面是两个视图：

create view user_diary_number as
(
select user_id,count( distinct diary_id ) as diary_num
from user_diary

group by user_id
);

第二，使用having和avg

create view hw_diary as
(
select u.user_id, u.realname, ud.diary_num, school.school_name
from (user as u cross join user_diary_number as ud on u.user_id = ud.user_id )cross join school on u.school_id = school.school_id
having diary_num > avg(diary_num)

);

现在的问题是，第二个视图只有1行结果。当然，我们有超过1个用户，他们的日志数量>平均diary_num。事实上，我总共有251个日记和103个用户。一些用户有9，4，5个日记。但结果只有1个用户有3个日记。

我的相对表是：

CREATE TABLE IF NOT EXISTS `school` (
  `school_id` int(11) NOT NULL,
  `school_name` varchar(45) NOT NULL,
  `location` varchar(45) NOT NULL,
  `master` varchar(45) NOT NULL,
  `numbers_of_student` int(11) NOT NULL,
  PRIMARY KEY (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `user_diary` (
  `diary_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(45) NOT NULL,
  `content` varchar(255) NOT NULL,
  `addtime` DATETIME NOT NULL,
  PRIMARY KEY (`diary_id`,`user_id`),
  KEY `fk_diary_user_id_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

交叉连接有什么问题吗？还是别的什么？非常感谢!

Answer 1

你不能那样使用avg。在我的个人电影数据库里，

select * from movie having year > avg(year);

不会产生任何结果，并且

select * from movie having year > (select avg (year) from movie);

生成预期的结果。

Answer 2

必须在单独的子查询中计算平均值。

类似于：

select ...
from ...
group by ...
having diary_num > (
    select avg(diary_num)
    from ...) 

你可以用有意义的东西来填空。