Существует ли общий способ обхода, чтобы выразить производный список столбцов в Oracle (и MySQL)?

Многие базы данных SQL поддерживают то, что стандарт SQL вызывает <derived column list>. Такие базы данных включают по меньшей мере CUBRID, Derby, Firebird, HSQLDB, Postgres, SQL Server и Sybase SQL Anywhere. A (упрощенный) из спецификации 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> ]

Это означает, что я могу выразить такие вещи (например, в Postgres, который соответствует стандартам)

-- 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)

Теперь, согласно документации Oracle, я не могу переименовать столбцы, используя спецификацию <derived column list>. Я мог бы, конечно, переименовать таблицы и столбцы отдельно, например:

-- 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;

Но для этого требуется больше знаний о производной таблице (имена фактических столбцов), чем предыдущий синтаксис. Кроме того, переименованные столбцы будут доступны только после проецирования (например, в предложении ORDER BY), а не в любых других предложениях, включая сам проект.

Существует ли более общий способ переименования таблиц И столбцов, как предлагает стандарт SQL, в Oracle (а также MySQL)? В частности, это может быть полезно для таких вещей, как переназначение массива, переименование таблицы pivot/unpivot, встраивание сложных подзапросов, переименование результатов из табличных функций и т.д.

N.B: Пожалуйста, не сосредотачивайтесь на приведенных выше примерах слишком много. Они действительно здесь, чтобы проиллюстрировать проблему. Реальные запросы гораздо сложнее, поэтому я ищу очень общий способ реализации переименования в u(b)

ПРИМЕЧАНИЕ. Я все еще ищу решение, которое работает в базе данных, например MySQL. Связанный с этим вопрос:
Как выбрать неустановленный числовой литерал из подвыборки

Ответ 1

Для решения MySQL вы можете использовать UNION для установки имен всех столбцов в терминах запроса с нулевой строкой, а затем затем запросить что-то более сложное:

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

Только первый термин запроса UNION определяет имена столбцов всего запроса. Имена столбцов (или их отсутствие) в последующих запросах не влияют на конечные имена столбцов.

Вам нужно знать количество столбцов, но должно быть довольно просто сохранить два условия запроса отдельно. Насколько я знаю, он работает как в Oracle, так и в MySQL (однако я тестировал его только в MySQL, а не в Oracle).

Ответ 2

Поскольку вы ДОЛЖНЫ знать количество столбцов, но не обязательно имена столбцов, вы можете использовать предложение WITH, чтобы переименовать эти столбцы, как вам угодно. Например (WITH работает в Oracle и SQL Server, не имеет экземпляра MySQL):

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

Здесь мы не знаем имена столбцов во внутреннем select, но мы можем переименовать их во внешнем разделе WITH.

Другой пример использования PIVOT в 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;

Опять же, нам все равно, что внутренние имена столбцов выбора (внутренний стержень создает несколько нечетных имен столбцов), нам просто нужно знать, сколько столбцов и мы можем переименовать.

Ответ 3

Как было предложено пользователем tbone here, обычные табличные выражения являются хорошим решением моей проблемы, по крайней мере для Oracle. Для полноты, вот мой пример, написанный с использованием CTE в 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