recursive - MySQL cláusula "CON"

with mysql example

Question

Intento usar MySQL para crear una vista con la cláusula "CON"

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

Pero no parece que MySQL lo soporte.

Pensé que esto era bastante estándar y estoy seguro de que Oracle apoya esto. ¿Hay alguna forma de obligar a MySQL a usar la cláusula "CON"? Lo he intentado con el motor MyISAM e innoDB. Ambos no funcionan.

Answer 1

¿Alguna vez has probado la Tabla temporal? Esto resolvió mi convern:

create temporary table abc (
column1 varchar(255)
column2 decimal
);
insert into abc
select ...
or otherwise
insert into abc
values (''text'', 5.5), (''text2'', 0815.8);

Luego puede usar esta tabla en cada selección en esta sesión:

select * from abc inner join users on ...;

Answer 2

Actualización: MySQL 8.0 finalmente está obteniendo la característica de las expresiones de tabla comunes, incluidos los CTE recursivos.

Aquí hay un blog que lo anuncia: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/

A continuación está mi respuesta anterior, que originalmente escribí en 2008.

MySQL no admite consultas utilizando la sintaxis WITH definida en SQL-99, también llamada Common Table Expressions.

Esta ha sido una solicitud de características para MySQL desde enero de 2006: http://bugs.mysql.com/bug.php?id=16244

Otros productos RDBMS que admiten expresiones de tabla comunes:

• Oracle 9i versión 2 y posterior:
  http://www.oracle-base.com/articles/misc/with-clause.php
• Microsoft SQL Server 2005 y posterior:
  http://msdn.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
• IBM DB2 UDB 8 y posterior:
  http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000879.htm
• PostgreSQL 8.4 y posterior:
  https://www.postgresql.org/docs/current/static/queries-with.html
• Sybase 11 y posterior:
  http://dcx.sybase.com/1100/en/dbusage_en11/commontblexpr-s-5414852.html
• SQLite 3.8.3 y posterior:
  http://sqlite.org/lang_with.html
• HSQLDB:
  http://hsqldb.org/doc/guide/dataaccess-chapt.html#dac_with_clause
• Firebird 2.1 y posterior (el primer DBMS de código abierto para admitir consultas recursivas): http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes210.html#rnfb210-cte
• Base de datos H2 (pero solo recursiva):
  http://www.h2database.com/html/advanced.html#recursive_queries

Otras bases de datos que carecen de soporte para la cláusula WITH (desde febrero de 2014):

• Informix (aunque Informix admite la sintaxis CONNECT BY que una vez utilizó Oracle)
  http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_2033.htm

Answer 3

MariaDB ahora está apoyando CON. MySQL por ahora no lo es. https://mariadb.com/kb/en/mariadb/with/

Answer 4

Oracle no admite CON.

Se vería así.

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

@ysth WITH es difícil de buscar en Google porque es una palabra común que normalmente se excluye de las búsquedas.

Querrá ver los documentos SELECT para ver cómo funciona el factorización de subconsulta.

Sé que esto no responde al OP, pero estoy limpiando cualquier confusión que pueda haber comenzado.

Answer 5

Puede que le interese algo así como esto:

select * from (
select * from table
) as Subquery

Answer 6

Sobre la base de la respuesta de @Mosty Mostacho, aquí se explica cómo puede hacer algo equivalente en MySQL, para un caso específico de determinación de qué entradas no existen en una tabla, y no están en ninguna otra base de datos.

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

Es posible que desee utilizar un editor de texto con capacidades de macro para convertir una lista de valores en la cláusula de unión seleccionada.

Answer 7

Tienes la sintaxis correcta:

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

Sin embargo, como otros han mencionado, MySQL no es compatible con este comando. WITH se agregó en SQL: 1999; la versión más nueva del estándar SQL es SQL: 2008. Puede encontrar más información sobre las bases de datos compatibles con SQL: las diversas funciones de 1999 en Wikipedia .

MySQL tradicionalmente se ha retrasado un poco en soporte del estándar SQL, mientras que las bases de datos comerciales como Oracle, SQL Server (recientemente) y DB2 las han seguido un poco más de cerca. PostgreSQL es típicamente bastante compatible con los estándares también.

Es posible que desee ver la hoja de ruta de MySQL; No estoy del todo seguro de cuándo se admitirá esta característica, pero es genial para crear consultas de resumen legibles.