acumulador - ¿Hay una solución genérica para expresar una lista de columnas derivadas en Oracle(y MySQL)?

acumulador en mysql

Question

Muchas bases de datos SQL admiten lo que el estándar SQL llama una <derived column list> . Dichas bases de datos incluyen al menos CUBRID, Derby, Firebird, HSQLDB, Postgres, SQL Server y Sybase SQL Anywhere. Un extracto (simplificado) de la especificación SQL: 2008

7.6 <table reference>

Format
<table reference> ::=
    <table or query name> [ [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ] ]
  | <derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]

Esto significa que puedo expresar cosas como estas (por ejemplo, en Postgres, que cumple con los estándares)

-- Rename a <table or query name> to u(b)
with t(a) as (select 1)
select * from t as u(b)

-- Rename a <derived table> to u(b)
select * from (select 1) as u(b)

Ahora, de acuerdo con la documentación de Oracle , no puedo cambiar el nombre de las columnas con una especificación de <derived column list> . Por supuesto, podría cambiar el nombre de las tablas y columnas por separado, de esta manera:

-- Rename a <table or query name> to u(b)
with t(a) as (select 1 from dual)
select u.a b from t u;

-- Rename a <derived table> to u(b)
select u.a b from (select 1 a from dual) u;

Pero esto requiere más conocimiento sobre la tabla derivada (nombres de columna reales) que la sintaxis anterior. Además, las columnas renombradas solo estarán disponibles después de la proyección (por ejemplo, en la cláusula ORDER BY ), no en ninguna otra cláusula, incluida la propia proyección.

¿Existe una forma más genérica de cambiar el nombre de las tablas Y las columnas de la manera que sugiere el estándar SQL, en Oracle (y también en MySQL)? En particular, esto podría ser útil para cosas como la anulación de matrices, el cambio de nombre de tablas dinámicas / no dinámicas, la integración de subconsultas complejas, el cambio de nombre de los resultados de las funciones de tablas, etc.

NB: Por favor, no se centre demasiado en los ejemplos anteriores. Realmente están aquí solo para ilustrar el problema. Las consultas del mundo real son mucho más complejas, por lo que estoy buscando una forma muy general de implementar el cambio de nombre a u(b)

NOTA : Todavía estoy buscando una solución que funcione en una base de datos como MySQL. Una pregunta relacionada:
Cómo seleccionar un literal numérico sin alias de una subselección

Answer 1

Como sugirió el usuario tbone aquí , las expresiones de tabla comunes son una buena solución para mi problema, al menos para Oracle. Para completar, aquí está mi consulta de ejemplo escrita usando CTE en Oracle

-- Rename a <derived table> to u(b) with Oracle
with u(b) as (select 1 from dual) 
select u.b from u

-- Rename a <derived table> to u(b) with H2, which only knows recursive CTEs
-- Thanks to a comment by user a_horse_with_no_name
with recursive u(b) as (
  select 1
  union all
  select null where false
)
select u.b from u

Answer 2

Para una solución MySQL, podría usar un UNION para establecer los nombres de todas las columnas en un término de consulta de fila cero, y luego consultar algo más complejo:

SELECT null AS a, null AS b, null AS c FROM dual WHERE false
UNION ALL
SELECT <expr>, <expr>, <expr>
FROM <realtable>...

Solo el primer término de consulta de UNION define los nombres de columna de toda la consulta. Los nombres de columna (o la falta de ellos) en los términos de consulta subsiguientes no afectan los nombres de columna finales.

Debe conocer el número de columnas, pero debería ser bastante fácil mantener los dos términos de consulta separados. Por lo que sé, funciona tanto en Oracle como en MySQL (sin embargo, solo lo he probado en MySQL, no en Oracle).

Answer 3

Ya que DEBE conocer el número de columnas, pero no necesariamente los nombres de las columnas, puede usar la cláusula WITH para cambiar el nombre de estas columnas como desee. Por ejemplo (WITH funciona en Oracle y SQL Server, no tiene a mano la instancia de MySQL):

WITH t(x,y,z) as (select * from TABLE(fn_returning_xcols(3)))
select * from t;

Aquí no conocemos los nombres de las columnas en la selección interna, pero podemos cambiarles el nombre en la cláusula WITH externa.

Otro ejemplo usando un PIVOT en Oracle:

WITH t(a,b,c,d,e) as 
(
 select * from 
 (
  select level as levl from dual connect by level <= 5
 )
 PIVOT(max(levl) as l for levl in (1,2,3,4,5))
)
select * from t;

Nuevamente, no nos importa cuáles son los nombres de las columnas de selección interna (el pivote interno crea nombres de columnas un tanto impares), solo necesitamos saber cuántas columnas y podemos cambiar el nombre.