mysql - #1349 - View 的 SELECT 在 FROM 子句中包含子查询

Question

我需要修改 MySQL 代码来传递此错误并获得相同的结果。

<小时/>

我使用 localhost 和 Xampp ..

• Apache/2.4.29 (Win32) OpenSSL/1.1.0g PHP/7.2.1
• 服务器类型:MariaDB
• 服务器版本:10.1.30-MariaDB - mariadb.org 二进制发行版
• 数据库客户端版本:libmysql - mysqlnd 5.0.12-dev - 20150407
• PhpMyAdmin 版本信息:4.7.4，最新稳定版本:4.9.1

CREATE VIEW product_variation_stock_view AS
                SELECT 
                    product_variations.product_id AS product_id,
                    product_variations.id AS product_variation_id,
                    COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) AS stock,
                    CASE WHEN COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity), 0), 0) > 0
                        THEN true
                        ELSE false
                    END in_stock
                FROM product_variations
                LEFT JOIN(
                    SELECT stocks.product_variation_id AS id,
                    SUM(stocks.quantity) AS quantity
                    FROM stocks
                    GROUP BY stocks.product_variation_id
                ) AS stocks USING (id)
                LEFT JOIN (
                    SELECT
                        product_variation_order.product_variation_id AS id,
                        SUM(product_variation_order.quantity) AS quantity
                    FROM product_variation_order
                    GROUP BY product_variation_order.product_variation_id
                ) AS product_variation_order USING (id)
                GROUP BY product_variations.id

MySQL 说:

#1349 - View's SELECT contains a subquery in the FROM clause

Answer 1

升级到mysql-8，我的问题解决了。

<小时/>

To Upgrade MySql included in XAMPP (I did it on a Windows system):

1. Rename your Mysql directory to some other name.
2. Download the .msi file from the MariaDB link https://downloads.mariadb.org/.
3. Run .msi. Change the install directory to your Mysql location under XAMPP.
4. After completion, copy the old my.ini from the original mysql/bin directory to your new mysql/bin directory.
5. You should be able to start Mysql from the XAMPP control panel.

我在这里找到了解决方案 http://www.mynotebucket.com/update-mysql-under-xmpp/