MYSQL - 无法创建 View

Question

SELECT branches.brid, 
COALESCE(a.cnt, 0) AS Assigned,
COALESCE(c.cnt, 0) AS Completed,
COALESCE(p.cnt, 0) AS Pending,
COALESCE(x.cnt, 0) AS Expired
FROM branches WHERE access = 'User'
LEFT JOIN
(SELECT brid, count(*) from task GROUP BY brid) a ON branches.brid = a.brid
LEFT JOIN
(SELECT brid, count(*) from task WHERE stat = 'Completed' GROUP BY brid) c ON branches.brid = c.brid
LEFT JOIN 
(SELECT brid, count(*) from task WHERE stat = 'Pending' GROUP BY brid) p ON branches.brid = p.brid
LEFT JOIN
(SELECT brid, count(*) from task WHERE stat = 'Expired' GROUP BY brid) x ON branches.brid = x.brid
ORDER BY branches.brid ASC;

我在创建 View 时遇到此错误，有什么办法可以做到这一点吗？此查询有效，但我无法在数据库中创建 View

错误 1349 (HY000): View 的 SELECT 在 FROM 子句中包含子查询

Answer 1

该错误或多或少可以解释自己。

这是 documentation 的摘录，您需要运行 MySQL 5.7.7 或更高版本才能在 View 中使用子查询。

view definition is subject to the following restrictions:

Before MySQL 5.7.7, the SELECT statement cannot contain a subquery in the FROM clause. ....

您可以将查询重写为如下所示:

SELECT branches.brid, 
COUNT(t.stat) AS Assigned,
SUM(CASE WHEN  t.stat = 'Completed' THEN 1 ELSE 0 END) AS Completed,
SUM(CASE WHEN  t.stat = 'Pending' THEN 1 ELSE 0 END) AS Pending,
SUM(CASE WHEN  t.stat = 'Expired' THEN 1 ELSE 0 END) AS Expired
FROM branches
LEFT JOIN task t ON branches.brid = t.brid
WHERE access = 'User'
GROUP BY branches.brid
ORDER BY branches.brid ASC;