Предложение MySQL "WITH"
Я пытаюсь использовать MySQL для создания представления с предложением "WITH"
WITH authorRating(aname, rating) AS
SELECT aname, AVG(quantity)
FROM book
GROUP BY aname
Но похоже, что MySQL не поддерживает это.
Я думал, что это довольно стандартно, и я уверен, что Oracle поддерживает это. Есть ли способ заставить MySQL использовать предложение «WITH»? Я пробовал это с движком MyISAM и innoDB. Оба они не работают.
Ответы 8
Oracle поддерживает СО.
Это выглядело бы так.
WITH emps as (SELECT * FROM Employees)
SELECT * FROM emps WHERE ID < 20
UNION ALL
SELECT * FROM emps where Sex = 'F'
@ysth WITH сложно использовать в Google, потому что это обычное слово, которое обычно исключается из поиска.
Вы хотели бы взглянуть на ВЫБРАТЬ документы, чтобы увидеть, как работает факторинг подзапроса.
Я знаю, что это не отвечает на OP, но я убираю любую путаницу, которая могла начаться.
Ах я вижу. Это предложение select, которое предшествует select. Можно ли его использовать и в CREATE VIEW? Чем это отличается от присоединения к подзапросу? Я не вижу в Интернете примеров, где имя после WITH имеет параметры - как они работают?
Это совсем другое. Обратите внимание, что одна и та же подзапрос используется дважды без необходимости определять ее дважды. Конечно, вы можете скопировать / вставить туда тот же запрос, но это простой пример. Представьте, что предложение WITH продолжалось для страницы и использовалось 4 раза в основном запросе. тогда вы это оцените.
Я связался с документами, которые должны объяснить синтаксис. Насколько в поле зрения. Конечно, там это работает.
Обновление: MySQL 8.0 наконец-то получает функцию общих табличных выражений, включая рекурсивные CTE.
Об этом сообщается в блоге: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/
Ниже мой предыдущий ответ, который я написал в 2008 году.
MySQL 5.x не поддерживает запросы, использующие синтаксис WITH, определенный в SQL-99, также называемый Общие табличные выражения..
Это был запрос функции для MySQL с января 2006 года: http://bugs.mysql.com/bug.php?id=16244
Другие продукты СУБД, поддерживающие общие табличные выражения:
- Oracle 9i выпуска 2 и новее:
http://www.oracle-base.com/articles/misc/with-clause.php - Microsoft SQL Server 2005 и более поздние версии:
http://msdn.microsoft.com/en-us/library/ms190766(v=sql.90).aspx - IBM DB2 UDB 8 и новее:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000879.htm - PostgreSQL 8.4 и новее:
https://www.postgresql.org/docs/current/static/queries-with.html - Sybase 11 и новее:
http://dcx.sybase.com/1100/en/dbusage_en11/commontblexpr-s-5414852.html - SQLite 3.8.3 и новее:
http://sqlite.org/lang_with.html - HSQLDB:
http://hsqldb.org/doc/guide/dataaccess-chapt.html#dac_with_clause - Firebird 2.1 и выше (первая СУБД с открытым исходным кодом, поддерживающая рекурсивные запросы): http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes210.html#rnfb210-cte
- База данных H2 (но рекурсивная Только):
http://www.h2database.com/html/advanced.html#recursive_queries - Informix 14.10 и новее: https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_with.htm
SQLite поддерживает Предложение WITH с версия 3.8.3, выпущенного 03.02.2014.
Я добавил в список H2 и Firebird.
@a_horse_with_no_name, спасибо за дополнения. Возможно, нам удастся заставить MySQL повысить приоритет этого старого запроса функции!
@BillKarwin: Я не верю, что MySQL когда-либо реализует функцию современной СУБД Любые (проверка ограничений, оконная функция, индекс по выражениям, частичный индекс, отложенные ограничения ...).
@a_horse_with_no_name, они, кажется, придают гораздо более высокий приоритет масштабируемости. Они долгое время сосредоточились на том, чтобы сделать свои внутренние устройства более масштабируемыми, чтобы использовать преимущества современного оборудования. Но я думаю, что они пренебрегли возможностями SQL.
@a_horse_with_no_name, MySQL 5.7 теперь поддерживает индексы для «виртуальных столбцов», что дает вам решение для индексов выражений. Просто подождите еще десять лет, пока не появятся другие упомянутые вами функции. :-)
MySQL теперь принадлежит Oracle. Зачем Oracle хотеть сделать MySQL эквивалентом Oracle DB? Они хотят, чтобы MySQL был ограничен небольшими проектами, чтобы в больших проектах использовалась база данных Oracle.
@BlakeMcBride, Вы ошибаетесь, ваш комментарий FUD и фактически не имеет под собой никаких оснований. Oracle также владеет другими продуктами для баз данных, которые делают то, чего не удается Oracle DB. Примеры: TimesTen, BerkeleyDB. Они приобрели эти базы данных, чтобы расширить свой рынок. MySQL доминирует на рынке веб-приложений, а Oracle DB - нет, поэтому они приобрели MySQL. Oracle не имеет смысла ограничивать MySQL. Я разговаривал с разработчиками Oracle MySQL на конференции в апреле, и они фактически работают над внедрением WITH для MySQL.
@BillKarwin, мне очень жаль. Часть информации я получил от Билла Карвина, который сказал, что они никогда не будут реализовывать современные функции БД. Я заметил, что ты теперь ас Oracle. Хм.
@BlakeMcBride, в прошлом я писал, что MySQL, похоже, слишком сосредоточен на улучшении внутренней масштабируемости за счет застоя в функциях языка SQL. Тогда это было правдой, но после приобретения Oracle у них появилось больше инженерных ресурсов, так что теперь они могут делать и то, и другое.
@BillKarwin, надеюсь, вы правы, но Oracle уже довольно давно владеет MySQL, и кажется, что такие вещи, как CTE и оконные функции, давно назрели. Следовательно, трудно не предположить, что Oracle на самом деле не заинтересована возвращать SQL в MySQL.
@Mark, я посетил группу инженеров MySQL на конференции в апреле 2016 года, и они сказали, что теперь работают над функциями CTE для MySQL 8.
Oracle не пренебрегает и / или не ограничивает MySQL. MySQL больше ориентирован на производительность OLTP и используется для этого многими предприятиями AAA, но ему не хватает аналитических возможностей. Понятно, что Oracle пытается дифференцировать свои продукты, добавляя функции в MySQL, когда чувствует, что он отстает от внешних конкурентов с открытым исходным кодом, таких как PostgreS.
Обновлен ответ выше с помощью Informix 14.10, которая добавила поддержку в 2019 году.
У вас правильный синтаксис:
WITH AuthorRating(AuthorName, AuthorRating) AS
SELECT aname AS AuthorName,
AVG(quantity) AS AuthorRating
FROM Book
GROUP By Book.aname
Однако, как отмечали другие, MySQL не поддерживает эту команду. WITH был добавлен в SQL: 1999; новейшая версия стандарта SQL - SQL: 2008. Вы можете найти дополнительную информацию о базах данных, поддерживающих различные функции SQL: 1999, на Википедия.
MySQL традиционно немного отстает в поддержке стандарта SQL, в то время как коммерческие базы данных, такие как Oracle, SQL Server (недавно) и DB2, следовали им чуть более внимательно. PostgreSQL также обычно хорошо соответствует стандартам.
Вы можете взглянуть на дорожную карту MySQL; Я не совсем уверен, может ли эта функция поддерживаться, но она отлично подходит для создания читаемых сводных запросов.
Возможно, вас заинтересует что-то вроде этого:
select * from (
select * from table
) as Subquery
не могли бы вы объяснить подзапрос? Могу ли я выбрать * из ((выбрать * из таблицы1) ОБЪЕДИНЕНИЕ ВСЕХ (выбрать * из таблицы2)) Группировать по чему-то?
@Kathy Привет, Subquery - это имя, которое я использовал для самой производной таблицы. Когда вы используете from ( ... ), вы создаете что-то вроде временной таблицы (производной таблицы), и для нее требуется имя. Вот почему я использовал as Subquery. Отвечая на ваш вопрос, да, вы можете, но вам нужно будет указать имя внешней производной таблицы (непосредственно перед Group By). Надеюсь, это помогло.
Вы когда-нибудь пробовали временную таблицу? Это решило мою беседу:
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);
Затем вы можете использовать эту таблицу при каждом выборе в этом сеансе:
select * from abc inner join users on ...;
Замечу: stackoverflow.com/questions/343402/… вы не можете открыть таблицу дважды :-(
Мое решение для небольших наборов данных в таблицах: создать таблицу abc2 как abc; вставить в abc2 выбрать * из abc;
Основываясь на ответе @Mosty Mostacho, вот как вы могли бы сделать что-то эквивалентное в MySQL для конкретного случая определения того, какие записи не существуют в таблице и не находятся в какой-либо другой базе данных.
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
Вы можете использовать текстовый редактор с возможностями макросов для преобразования списка значений в указанное в кавычках предложение select union.
MariaDB теперь поддерживает WITH. MySQL пока нет. https://mariadb.com/kb/en/mariadb/with/
WITH authorRating as (select aname, rating from book)
SELECT aname, AVG(quantity)
FROM authorRating
GROUP BY aname
Во всяком случае, мое замешательство не прояснилось. Вы хотите сказать, что предложения WITH нет, но есть инструкция WITH?