mysql - 从具有多个子查询的Select语句创建 View

SELECT z.`id`, z.`ukupno` ,s.uk, (ukupno-uk) as r from zbirni z
 join
(SELECT k.`iddzid`,  (sum(kolicina*cjena)) as uk FROM   kasa k   join kasa_detalji d on d.idd=k.id group by  iddzid) as s
on s.iddzid=z.id
 where (ukupno-uk)>0

但是我有错误
脚本行：1视图的SELECT在FROM子句中包含一个子查询

最佳答案

您可以使用联接重写查询，以避免使用子查询和Mysql视图的限制

SELECT
z.`id`,
z.`ukupno` ,
SUM(kolicina * cjena) uk,
(ukupno - SUM(kolicina * cjena)) AS r
FROM zbirni z
 JOIN kasa k  ON(z.id=k.`iddzid`)
 JOIN kasa_detalji d ON (d.idd=k.id)
GROUP BY z.`id`
HAVING r > 0

或为每个子查询创建视图，并在您的视图中使用它，例如

CREATE VIEW calc_sum
AS
SELECT k.`iddzid`,
SUM(kolicina*cjena) AS uk FROM   kasa k
JOIN kasa_detalji d ON d.idd=k.id
GROUP BY  iddzid

在查询中使用calc_sum视图

CREATE VIEW your_view_name
SELECT
z.`id`,
z.`ukupno` ,
s.uk,
(ukupno-uk) AS r
FROM zbirni z
 JOIN  calc_sum AS s ON s.iddzid=z.id
WHERE (ukupno-uk)>0

关于mysql - 从具有多个子查询的Select语句创建 View ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25817950/