問題描述

我正在將所有 SQL Server 查詢轉換為 MySQL，其中包含 WITH 的查詢都失敗了.舉個例子:

I am converting all my SQL Server queries to MySQL and my queries that have WITH in them are all failing. Here's an example:

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 t1
ORDER BY t1.article_date DESC
LIMIT 1, 3

推薦答案

MySQL 8.0 之前的版本 不支持 WITH 子句(SQL Server 中的 CTE；Oracle 中的子查詢分解)，因此您只能使用:

MySQL prior to version 8.0 doesn't support the WITH clause (CTE in SQL Server parlance; Subquery Factoring in Oracle), so you are left with using:

• 臨時表
• 派生表
• 內聯視圖(實際上 WITH 子句代表什么 - 它們可以互換)

對該功能的請求可以追溯到 2006 年.

The request for the feature dates back to 2006.

如前所述，您提供了一個糟糕的示例 - 如果您不以任何方式更改列的輸出，則無需執行子選擇:

As mentioned, you provided a poor example - there's no need to perform a subselect if you aren't altering the output of the columns in any way:

  SELECT * 
    FROM ARTICLE t
    JOIN USERINFO ui ON ui.user_userid = t.article_ownerid
    JOIN CATEGORY c ON c.catid =  t.article_categoryid
   WHERE t.published_ind = 0
ORDER BY t.article_date DESC 
   LIMIT 1, 3

這是一個更好的例子:

SELECT t.name,
       t.num
  FROM TABLE t
  JOIN (SELECT c.id
               COUNT(*) 'num'
          FROM TABLE c
         WHERE c.column = 'a'
      GROUP BY c.id) ta ON ta.id = t.id

這篇關于你如何使用“WITH"?MySQL中的條款?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！