mysql - 查询以显示已/已发布最大帖子数的2008年批次的校友用户(Role-“Alumni”)的姓名，按名称排序

我有四张桌子：

CREATE TABLE ROLE (
  id   INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE PROFILE (
  id     INT PRIMARY KEY,
  batch  VARCHAR(10)
);

CREATE TABLE USER (
  id          INT PRIMARY KEY,
  name        VARCHAR(50),
  role_id     INT REFERENCES ROLE(id),
  profile_id  INT REFERENCES PROFILE(id)
);

CREATE TABLE POST (
  id       INT PRIMARY KEY,
  content  VARCHAR(4000),
  user_id  INT REFERENCES USER(id)
);

编写一个SQL查询来显示校友用户（角色-校友）的名字（2008名），他们有/发布了最多的职位，按名称排序。
我试过这个：

select user.name
from   user
       inner join role
       on user.role_id=role.id
       inner join profile
       on user.profile_id=profile.id
       inner join post
       on user.id=post.user_id
where  profile.batch="2008"
group by user.name
having count(post.content)=3
order by user.name;

我不能把正确的条件放在having子句中。

最佳答案

SQL Fiddle
MySQL 5.6计划设置：

CREATE TABLE ROLE (
  id   INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE PROFILE (
  id     INT PRIMARY KEY,
  batch  VARCHAR(10)
);

CREATE TABLE USER (
  id          INT PRIMARY KEY,
  name        VARCHAR(50),
  role_id     INT REFERENCES ROLE(id),
  profile_id  INT REFERENCES PROFILE(id)
);

CREATE TABLE POST (
  id       INT PRIMARY KEY,
  content  VARCHAR(4000),
  user_id  INT REFERENCES USER(id)
);

INSERT INTO ROLE    VALUES ( 1, 'Role1' );
INSERT INTO PROFILE VALUES ( 1, '2008'  );
INSERT INTO USER    VALUES ( 1, 'Alice', 1, 1 );
INSERT INTO USER    VALUES ( 2, 'Bob', 1, 1 );
INSERT INTO USER    VALUES ( 3, 'Carol', 1, 1 );
INSERT INTO POST    VALUES ( 1, 'Post 1', 1 );
INSERT INTO POST    VALUES ( 2, 'Post 2', 1 );
INSERT INTO POST    VALUES ( 3, 'Post 1', 2 );
INSERT INTO POST    VALUES ( 4, 'Post 1', 3 );
INSERT INTO POST    VALUES ( 5, 'Post 2', 3 );

问题1：

SELECT name
FROM   (
  SELECT name,
         CASE WHEN @prev_value =  num_posts THEN @rank_count
              WHEN @prev_value := num_posts THEN @rank_count := @rank_count + 1
         END AS rank
  FROM   (
    SELECT u.name,
           ( SELECT COUNT( 1 ) FROM post t WHERE t.user_id = u.id ) AS num_posts
    from   user u
           inner join role r
           on u.role_id=r.id
           inner join profile p
           on u.profile_id=p.id,
           ( SELECT @prev_value := NULL, @rank_count := 0 ) i
    where  p.batch="2008"
    ORDER BY num_posts DESC
  ) posts
) ranks
WHERE rank = 1
ORDER BY name ASC

Results：

|  name |
|-------|
| Alice |
| Carol |

关于mysql - 查询以显示已/已发布最大帖子数的2008年批次的校友用户(Role-“Alumni”)的姓名，按名称排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36106319/