Clause "WITH" de MySQL

Question

J'essaie d'utiliser MySQL pour créer une vue avec la clause "WITH".

WITH authorRating(aname, rating) AS
   SELECT aname, AVG(quantity)
   FROM book
   GROUP BY aname

Mais il ne semble pas que MySQL supporte cela.

Je pensais que c'était assez standard et je suis sûr qu'Oracle le prend en charge. Existe-t-il un moyen de forcer MySQL à utiliser la clause "WITH" ? J'ai essayé avec le moteur MyISAM et innoDB. Les deux ne fonctionnent pas.

Answer 1

Mise à jour : MySQL 8.0 obtient enfin la fonctionnalité des expressions de table communes, y compris les CTE récursives.

Voici un blog qui l'annonce : http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/

Vous trouverez ci-dessous ma réponse précédente, que j'ai rédigée en 2008.

---

MySQL 5.x ne prend pas en charge les requêtes utilisant l'attribut WITH syntaxe définie dans SQL-99, également appelée Expressions courantes des tableaux.

C'est une demande de fonctionnalité pour MySQL depuis janvier 2006 : http://bugs.mysql.com/bug.php?id=16244

Autres produits SGBDR qui prennent en charge les expressions de table communes :

• Oracle 9i version 2 et ultérieure :
  http://www.oracle-base.com/articles/misc/with-clause.php
• Microsoft SQL Server 2005 et versions ultérieures :
  http://msdn.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
• IBM DB2 UDB 8 et plus :
  http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000879.htm
• PostgreSQL 8.4 et plus :
  https://www.postgresql.org/docs/current/static/queries-with.html
• Sybase 11 et plus :
  http://dcx.sybase.com/1100/en/dbusage_en11/commontblexpr-s-5414852.html
• SQLite 3.8.3 et plus :
  http://sqlite.org/lang_with.html
• HSQLDB :
  http://hsqldb.org/doc/guide/dataaccess-chapt.html#dac_with_clause
• Firebird 2.1 et versions ultérieures (le premier SGBD Open Source à prendre en charge les requêtes récursives) : http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes210.html#rnfb210-cte
• Base de données H2 (mais uniquement récursif) :
  http://www.h2database.com/html/advanced.html#recursive_queries
• Informix 14.10 et versions ultérieures : https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_with.htm

Answer 2

Vous pourriez être intéressé par quelque chose comme ça :

select * from (
    select * from table
) as Subquery

Answer 3

Vous avez la bonne syntaxe :

WITH AuthorRating(AuthorName, AuthorRating) AS
   SELECT aname         AS AuthorName,
          AVG(quantity) AS AuthorRating
   FROM Book
   GROUP By Book.aname

Cependant, comme d'autres l'ont mentionné, MySQL ne prend pas en charge cette commande. WITH a été ajouté dans SQL:1999 ; la version la plus récente de la norme SQL est SQL:2008. Vous pouvez trouver de plus amples informations sur les bases de données qui prennent en charge les diverses fonctionnalités de SQL:1999 sur le site suivant Wikipedia .

MySQL est traditionnellement un peu à la traîne en ce qui concerne la prise en charge du standard SQL, alors que les bases de données commerciales comme Oracle, SQL Server (récemment) et DB2 l'ont suivi d'un peu plus près. PostgreSQL est généralement assez conforme aux normes également.

Vous pouvez consulter la feuille de route de MySQL ; je ne suis pas tout à fait sûr de la date à laquelle cette fonctionnalité sera prise en charge, mais elle est idéale pour créer des requêtes roll-up lisibles.

Answer 4

Oracle prend en charge WITH.

Cela ressemblerait à ceci.

WITH emps as (SELECT * FROM Employees)
SELECT * FROM emps WHERE ID < 20
UNION ALL
SELECT * FROM emps where Sex = 'F'

@ysth WITH est difficile à trouver sur Google car c'est un mot commun généralement exclu des recherches.

Vous devriez regarder le SELECT docs pour voir comment la factorisation des sous-requêtes fonctionne.

Je sais que cela ne répond pas à l'OP mais je nettoie toute confusion qu'ysth a pu créer.

Answer 5

En se basant sur la réponse de @Mosty Mostacho, voici comment vous pourriez faire quelque chose d'équivalent dans MySQL, pour un cas spécifique de détermination des entrées qui n'existent pas dans une table, et qui ne sont pas dans une autre base de données.

select col1 from (
   select 'value1' as col1 union
   select 'value2' as col1 union
   select 'value3' as col1
) as subquery
left join mytable as mytable.mycol = col1
where mytable.mycol is null
order by col1

Vous pouvez utiliser un éditeur de texte doté de fonctionnalités de macro pour convertir une liste de valeurs en clause d'union select citée.