如何在MySQL中使用“WITH”子句？

Question

如何在MySQL中使用“WITH”子句？

我正在将我所有的SQLServer查询转换为MySQL，并将我的查询转换为WITH他们都失败了。下面是一个例子：

WITH t1 AS(
     SELECT article.*, userinfo.*, category.*
     FROM question     INNER JOIN userinfo ON userinfo.user_userid = article.article_ownerid     
     INNER JOIN category ON article.article_categoryid = category.catid     
     WHERE article.article_isdeleted = 0)SELECT t1.*FROM t1ORDER BY t1.article_date DESCLIMIT 1, 3

Answer 1

MySQL开发团队宣布8.0版将MySQL中常见的表达式(CTE)..因此，可以编写这样的查询：WITH RECURSIVE my_cte AS(&nbsp; SELECT 1 AS n&nbsp; UNION ALL&nbsp; SELECT 1+n FROM my_cte WHERE n<10)SELECT * FROM my_cte;+------+| n&nbsp; &nbsp; |+------+|&nbsp; &nbsp; 1 ||&nbsp; &nbsp; 2 ||&nbsp; &nbsp; 3 ||&nbsp; &nbsp; 4 ||&nbsp; &nbsp; 5 ||&nbsp; &nbsp; 6 ||&nbsp; &nbsp; 7 ||&nbsp; &nbsp; 8 ||&nbsp; &nbsp; 9 ||&nbsp; &nbsp;10 |+------+10 rows in set (0,00 sec)