MySQL查询有关创建视图的问题 [英] MySQL Query Problem regarding in creating a view

问题描述

大家好..



我想使用此查询在我的MySQL数据库中创建一个视图：

Hi to all ..

I want to creat a View in my MySQL Database using this query:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`clouddust`@`%` SQL SECURITY INVOKER VIEW `viewstocktransfersum` AS
select `tblstocktransfer`.`DateReference` AS `DateReference`,
`tblstocktransfer`.`InvoiceDate` AS `InvoiceDate`,
`tblstocktransfer`.`RefNo` AS `RefNo`,`tblstocktransfer`.`TransNo` AS `TransNo`,
`tblstocktransfer`.`WarehouseCodeFrom` AS `WarehouseCodeFrom`,
`tblstocktransfer`.`WHNameFrom` AS `WHNameFrom`,
`tblstocktransfer`.`WarehouseCodeTo` AS `WarehouseCodeTo`,`tblstocktransfer`.`WHNameTo` AS `WHNameTo`,
`tblproductlist`.`ProductCode` AS `ProductCode`,
`tblproductlist`.`Description` AS `Description`,
`tblproductlist`.`ProductCategory` AS `ProductCategory`,
`tblproductlist`.`UnitOfMeasure` AS `UnitOfMeasure`,
m.`Qty` AS Qty,
`tblstocktransfer`.`Status` AS `Status`,
`tblstocktransfer`.`StoreId` AS `StoreId`
from ((`tblstocktransfer`
INNER JOIN (SELECT TransNo, StoreId, ProductCode, SUM(tblstocktransferdetail.Qty) AS Qty
FROM `tblstocktransferdetail` GROUP BY StoreId, TransNo, ProductCode) m
on(`tblstocktransfer`.`TransNo` = `m`.`TransNo` AND tblstocktransfer.StoreId = m.StoreId)))
INNER JOIN tblproductlist ON (m.ProductCode = tblproductlist.ProductCode AND m.StoreId = tblproductlist.StoreId);

我的问题是这个查询返回一个错误视图的SELECT包含FROM子句中的子查询。但是当我通过不创建视图来运行此查询时，它会返回正确的结果。请帮我解决这个问题。



谢谢大家！上帝保佑你总是!!

My problem is that this query returns an error "View's SELECT contains a sub query in the FROM clause". But when i run this query by not creating a view, it returns a correct result. Please help me through this.

Thank you all! God bless you always!!

推荐答案

一个简单的谷歌搜索错误解释了MySql视图的局限性，并提供了一些建议的解决方法。例如，请参阅 http://stackoverflow.com/questions/ 8428641 / views-select-contains-a-subquery-in-the-clause [ ^ ]

A simple google search of the error explains the limitation of MySql views and also provides some suggested workarounds. For example, see http://stackoverflow.com/questions/8428641/views-select-contains-a-subquery-in-the-from-clause[^]

这篇关于MySQL查询有关创建视图的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！