MySQL 错误 1349 我错过了什么? [英] MySQL ERROR 1349 What I am missing?

问题描述

我收到 MySQL 1349 错误，但似乎错误不正确:

ERROR 1349: View 的 SELECT 在 FROM 子句中包含子查询

创建视图时可以没有任何子查询吗?

这是我的 SQL:

创建视图`wordpress`.`ffi_be_v_book_details` AS (选择 ffi_be_courses.* , COALESCE( `Total` , 0 ) AS `Total`来自`ffi_be_courses`左连接(SELECT * , COUNT( `Course`) 作为`Total`来自 ffi_be_courses右连接(选择课程"来自`ffi_be_bookcourses`左加入`ffi_be_sale` ON ffi_be_bookcourses.SaleID = ffi_be_sale.SaleIDWHERE DATE_ADD( ffi_be_sale.Upload, INTERVAL(选择`BookExpireMonths`FROM `ffi_be_settings`) 月) >CURDATE() 和 ffi_be_sale.Sold = '0'按 ffi_be_bookcourses.SaleID 分组) AS `q1` ON ffi_be_courses.Code = q1.CourseGROUP BY q1.Course) AS `q2` ON ffi_be_courses.Code = q2.CodeWHERE ffi_be_courses.Type = '艺术'ORDER BY ffi_be_courses.Name ASC)

感谢您的宝贵时间.

解决方案

您错过了 MySQL 中的视图不允许 from 子句中的子查询这一事实.但是，它们在 select 和 where 和 having 子句中是允许的.

文档非常清楚:p><块引用>

子查询不能用在视图的 FROM 子句中.

在您的情况下，您可以将 from 子句重写为 select 子句中的相关子查询.您还可以使用多层视图来做您想做的事.

SQL 中的 SELECT 语句有以下子句:SELECT、FROM、WHERE、GROUP BY、HAVING 和 ORDER BY(根据标准).此外，MySQL 添加了 LIMIT 和 INTO OUTFILE 等内容.您可以从 MySQL 描述 SELECT 子句的方式看到这一点="nofollow">文档.您还可以在几乎所有数据库的文档中看到这一点.

join 等操作是 FROM 子句的一部分(类似地，WITH ROLLUP 是 GROUP BY 和 DESC 是 ORDER BY 的一部分).这些可能看起来像是晦涩难懂的句法约定，但当存在上述限制时，它就变得很重要.

可能造成混淆的一个原因是缩进样式如下:

选择...从 t1内连接 t2在 ...

连接语句在 select 下排列的位置.这是误导.我会这样写:

选择从 t1 加入t2在 ...

只有 select 子句在 select 下排列.

I am getting a MySQL 1349 error, but it appears as though the error is incorrect:

ERROR 1349: View's SELECT contains a subquery in the FROM clause

Can I not have ANY subqueries when creating a view?

Here is my SQL:

CREATE VIEW  `wordpress`.`ffi_be_v_book_details` AS (
  SELECT ffi_be_courses. * , COALESCE(  `Total` , 0 ) AS  `Total` 
  FROM  `ffi_be_courses` 
  LEFT JOIN (
    SELECT * , COUNT(  `Course` ) AS  `Total` 
    FROM ffi_be_courses
    RIGHT JOIN (
      SELECT  `Course` 
      FROM  `ffi_be_bookcourses` 
      LEFT JOIN  `ffi_be_sale` ON ffi_be_bookcourses.SaleID = ffi_be_sale.SaleID
      WHERE DATE_ADD( ffi_be_sale.Upload, INTERVAL( 
        SELECT  `BookExpireMonths` 
        FROM  `ffi_be_settings` ) MONTH ) > CURDATE( ) AND ffi_be_sale.Sold =  '0'
      GROUP BY ffi_be_bookcourses.SaleID
    ) AS  `q1` ON ffi_be_courses.Code = q1.Course
    GROUP BY q1.Course
  ) AS  `q2` ON ffi_be_courses.Code = q2.Code
  WHERE ffi_be_courses.Type =  'Arts'
  ORDER BY ffi_be_courses.Name ASC
)

Thank you for your time.

解决方案

You are missing the fact that views in MySQL do not allow subqueries in the from clause. They are allowed in the select and where and having clauses, however.

The documentation is quite clear:

Subqueries cannot be used in the FROM clause of a view.

In your case, you can probably rewrite the from clause as a correlated subquery in the select clause. You can also use multiple layers of views to do what you want.

EDIT:

A SELECT statement in SQL has the following clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY (according to the standard). In addition, MySQL adds things like LIMIT, and INTO OUTFILE. You can see this in the way that MySQL describes the SELECT clause in the documentation. You can also see this in the documentation for almost any database.

Operations such as join are part of the FROM clause (similarly WITH ROLLUP is part of the GROUP BY and DESC is part of the ORDER BY). These may seem like arcane syntactic conventions, but it becomes important when there is a restriction like the one above.

Perhaps one reason for the confusion is an indentation style that goes like:

select . . .
from t1
inner join t2
     on . . .

Where the join statements line up under the select. This is misleading. I would write this as:

select 
from t1 join
     t2
     on . . .

Only the select clauses line up under the select.

这篇关于MySQL 错误 1349 我错过了什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！