Syntax error: WITH is not valid input in this posi

问题:

So I have this similar request

    WITH customers_in_usa AS (
        SELECT 
           customerName, state
        FROM
           customers
        WHERE
           country = 'USA'
    ) SELECT 
        customerName
    FROM
        customers_in_usa
    WHERE
        state = 'CA'
    ORDER BY customerName;

But when wrote it I caught an error: 'WITH is not valid input in this position' error_picture. Can you help me to understand what's wrong in this code?

回答1:

WITH customers_in_usa AS is for now invalid MySQL code. MySQL will support CTE's in the future in MySQL version 8..

You could rewrite your SQL code, that should give the same results.

SELECT 
    customerName
  , state
FROM 
   customers 
WHERE
   country = 'USA'
 AND
   state = 'CA'
ORDER BY
   customerName

回答2:

MySQL doesn't support common table expressions and WITH syntax until version 8.0.1.

Version 8.0 is still not production-ready as I write this (but it's in Release Candidate status, so it'll be GA pretty soon).