Mysql查询左连接和组连接

Question

我编写了mysql查询来从mysql获取数据

SELECT p.TITLE AS `Post Title`,
       GROUP_CONCAT(DISTINCT u.USERNAME) AS Writers,
       GROUP_CONCAT(DISTINCT c.CategoryName) AS Categories
FROM Posts p
JOIN Posts_Writers pw ON pw.PostID = p.PostID
JOIN Users u ON u.USERID = pw.USERID
JOIN Posts_Categories pc ON pc.PostID = p.PostID
JOIN Categories c ON c.Id = pc.CategoryId
GROUP BY p.TITLE


CREATE TABLE Posts (
  `PostID` INTEGER,
  `TITLE` VARCHAR(10)
);

INSERT INTO Posts
  (`PostID`, `TITLE`)
VALUES
  ('1', 'SOME TITLE');

CREATE TABLE Users (
  `USERID` INTEGER,
  `USERNAME` VARCHAR(4)
);

INSERT INTO Users
  (`USERID`, `USERNAME`)
VALUES
  ('1', 'Alaa'),
  ('2', 'John');

CREATE TABLE Posts_Writers (
  id INT auto_increment primary key,
  PostID INT,
  USERID INT
);

INSERT INTO Posts_Writers (PostId, USERID) VALUES
(1, 1),
(1, 2);

CREATE TABLE Categories (
  `Id` INTEGER,
  `CategoryName` VARCHAR(9)
);

INSERT INTO Categories
  (`Id`, `CategoryName`)
VALUES
  ('1', 'Business'),
  ('2', 'Marketing');

CREATE TABLE Posts_Categories (
  id INT auto_increment primary key,
  PostID INT,
  CategoryID INT
);

INSERT INTO Posts_Categories (PostID, CategoryID) VALUES
(1, 1),
(1, 2);

查询工作正常

问题是:

我通过 group_concat 从 mysql 中获取所有类别，如下所示(商业、营销)

但是假设我想过滤某个类别的结果

我想获取商业类别的结果

我尝试添加这些条件

WHERE c.CategoryName = 'Business' 

和

AND c.CategoryName = 'Business'

过滤器有效，结果符合预期

但是。问题是类别的输出只是“商业”

不是商业，像以前一样营销

这是 SQLFiddle

https://www.db-fiddle.com/f/67sGFv3xcERojvypmjdF8/1

我希望始终获得完整的类别列表

这就是我想要的结果

Business,Marketing

Answer 1

如果您始终想要类别的完整列表(在过滤之前)，您可以使用原始查询作为子查询来获取列表

SELECT p.TITLE AS `Post Title`,
       GROUP_CONCAT(DISTINCT u.USERNAME) AS Writers,
       t.Categories
FROM Posts p
JOIN Posts_Writers pw ON pw.PostID = p.PostID
JOIN Users u ON u.USERID = pw.USERID
JOIN Posts_Categories pc ON pc.PostID = p.PostID
JOIN Categories c ON c.Id = pc.CategoryId
JOIN (
  SELECT p.TITLE,
         GROUP_CONCAT(DISTINCT u.USERNAME) AS Writers,
         GROUP_CONCAT(DISTINCT c.CategoryName) AS Categories
  FROM Posts p
  JOIN Posts_Writers pw ON pw.PostID = p.PostID
  JOIN Users u ON u.USERID = pw.USERID
  JOIN Posts_Categories pc ON pc.PostID = p.PostID
  JOIN Categories c ON c.Id = pc.CategoryId
  GROUP BY p.TITLE
) t ON t.title = p.TITLE
GROUP BY p.TITLE, t.Categories
WHERE c.CategoryName = 'Business'