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

Question

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 View 的 SELECT 在 FROM 子句中包含一个子查询

Answer 1

您可以使用连接重写查询以避免使用子查询和 Mysql View 的限制

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

或者为每个子查询创建 View 并在你的 View 中使用它，比如

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 View

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