MYSQL - 不能创建视图
问题描述:
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;
我得到这个错误,当我创建视图有没有办法做到这一点?此查询工作,但我不能在我的数据库中创建视图MYSQL - 不能创建视图
ERROR 1349(HY000):视图的SELECT包含在FROM子句中
答
子查询的错误或多或少解释本身。
这里是从documentation的摘录,你需要运行MySQL 5.7.7或更新的版本能在一个视图中使用子查询。
视图定义是受到以下限制:
的MySQL 5.7.7之前,SELECT语句不能包含在一个子查询 FROM子句。 ....
你或许可以重写查询以这样的事:
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;
尽量把'where'后加入 – N1gthm4r3
怎么样?我不知道该怎么办 – James