MySQL不允许在视图的
这是我正在使用的代码;
CREATE VIEW MostBuys AS
SELECT Kundnummer, AntalBest FROM Ordrar
JOIN (SELECT OrderNr, COUNT(*) AS AntalBest
FROM OrderContent
GROUP BY OrderNr) BesAm ON BesAm.OrderNr = Ordrar.OrderNr;
我试图计算每个特定整数有多少行OrderNr,然后将其与相应的Kundsummer连接起来。这部分是自己工作的;
SELECT Kundnummer, AntalBest FROM Ordrar
JOIN (SELECT OrderNr, COUNT(*) AS AntalBest
FROM OrderContent
GROUP BY OrderNr) BesAm ON BesAm.OrderNr = Ordrar.OrderNr;
但我无法用它创建视图。
FROM
子句中进行子查询。啊!
您可以将其替换为相关的子查询:
这是我正在使用的代码;
CREATE VIEW MostBuys AS
SELECT o.Kundnummer,
(SELECT COUNT(*) AS AntalBest
FROM OrderContent oc
WHERE oc.OrderNr = o.OrderNr
) as AntalBest
FROM Ordrar o
您可以使用聚合来绕过MySQL视图的限制:
CREATE VIEW MostBuys AS
SELECT o.Kundnummer, COUNT(oc.OrderNr) AS AntalBest
FROM Ordrar o
LEFT JOIN OrderContent oc ON oc.OrderNr = o.OrderNr
GROUP BY o.Kundnummer