mysql - 查询以显示 2008 年批处理中已发布最大帖子数的校友用户(角色 -‘Alumni’)的姓名，按姓名排序

Question

我有 4 个表:

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 子句中加入正确的条件。

Answer 1

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 |