mysql - 如何根据条件显示评论

Question

我有下面的架构。一个快速的解释是：

1. 鲍勃给这部电影评分，5/5
2. 詹姆斯给这部电影评分，1/5
3. 梅西给这部电影评分，5/5
4. 没有人评价电影复仇者联盟。

逻辑：

1. 如果我是 personA，请查找我已阻止的所有人。
2. 查找特定电影的评论。
3. 任何对该电影发表评论且personA已屏蔽的人，将其从结果中删除

---

CREATE TABLE movies (
  id integer AUTO_INCREMENT primary key,
  name varchar(100) NOT NULL
);

CREATE TABLE customer (
  id integer AUTO_INCREMENT primary key,
  name varchar(100) NOT NULL
);

CREATE TABLE reviews (
  id integer AUTO_INCREMENT primary key,
  rating integer NOT NULL,
  cus_id integer NOT NULL,
  movie_id integer NOT NULL,
  FOREIGN KEY (cus_id) REFERENCES customer(id),
  FOREIGN KEY (movie_id) REFERENCES movies(id)
);

CREATE TABLE blocked(
  id integer AUTO_INCREMENT primary key,
  cus_id integer NOT NULL, -- This is the person blocking
  blocked_cus_id integer NOT NULL, -- This is the person who is blocked
  FOREIGN KEY (cus_id) REFERENCES customer(id),
  FOREIGN KEY (blocked_cus_id) REFERENCES customer(id)
);

INSERT INTO movies (id, name) VALUES (1, 'up'), (2, 'avengers');
INSERT INTO customer (id, name) VALUES (1, 'bob'), (2, 'james'), (3, 'macy');
INSERT INTO reviews (id, rating, cus_id, movie_id) VALUES (1, 5, 1, 1), (2, 1, 2, 1), (3, 5, 3, 1);
INSERT INTO blocked (id, cus_id, blocked_cus_id) VALUES (1, 1, 2);

我正在尝试获取电影 id 的评论列表1。我修改了从这里获得帮助的声明：如何根据条件删除结果以计算平均和特定电影，但问题是它只显示 1 条评论，即使有 2-3 条评论。

SELECT 
  r.rating,
  c.name
FROM movies m
LEFT JOIN reviews r 
    ON m.id = r.movie_id
    AND NOT EXISTS (
        SELECT 1 
        FROM blocked b
        WHERE b.blocked_cus_id = r.cus_id AND b.cus_id = 1 -- viewing the database as bob
    )
JOIN customer c ON r.cus_id = c.id
WHERE m.id = 1
GROUP BY m.id, m.name;

在上述声明中，我只发布了第一个评论。

+--------+------+
| rating | name |
+--------+------+
|      5 | bob  |
+--------+------+

我想得到下面的结果，因为这是从阻止詹姆斯的鲍勃的角度来看的，因此我们不显示詹姆斯的评论：

+--------+------+
| rating | name |
+--------+------+
|      5 | bob  |
|      5 | macy |
+--------+------+

当我将数据库视为 macy 时，我应该看到每个人的评论，因为 macy 没有阻止任何人：

+--------+-------+
| rating | name  |
+--------+-------+
|      5 | bob   |
|      1 | james |
|      5 | macy  |
+--------+-------+

SQL 小提琴：http ://www.sqlfiddle.com/#!9/7a6e71/4

标签： mysqlsqldatabasejoinselect

Answer 1

我的意思是下面的查询是你需要的：

select 
    m.id movie_id, 
    m.name movie_name, 
    r.name reviwed_by,
    r.rating
from movies m
left join (
  select movie_id, name, rating, b.cus_id blocked_by
  from reviews r
  join customer c on c.id = r.cus_id
  left join blocked b on b.cus_id = r.cus_id
)r on m.id = r.movie_id
where m.id = 1 and (r.blocked_by is null or r.blocked_by <> 1)
;

SQLize.online的结果（客户 1 对电影 1 的评论）

+==========+============+============+========+
| movie_id | movie_name | reviwed_by | rating |
+==========+============+============+========+
| 1        | up         | james      | 1      |
+----------+------------+------------+--------+
| 1        | up         | macy       | 5      |
+----------+------------+------------+--------+