你能定义“字面意思”吗? SQL 中的表?
是否有任何 SQL 子查询语法可以让您从字面上定义临时表?
例如,像
SELECT
MAX(count) AS max,
COUNT(*) AS count
FROM
(
(1 AS id, 7 AS count),
(2, 6),
(3, 13),
(4, 12),
(5, 9)
) AS mytable
INNER JOIN someothertable ON someothertable.id=mytable.id
这样的事情将不必执行两到三个查询:创建临时表,将数据放入其中,然后在联接中使用它。
我正在使用 MySQL,但对其他可以做类似事情的数据库感兴趣。
Is there any SQL subquery syntax that lets you define, literally, a temporary table?
For example, something like
SELECT
MAX(count) AS max,
COUNT(*) AS count
FROM
(
(1 AS id, 7 AS count),
(2, 6),
(3, 13),
(4, 12),
(5, 9)
) AS mytable
INNER JOIN someothertable ON someothertable.id=mytable.id
This would save having to do two or three queries: creating temporary table, putting data in it, then using it in a join.
I am using MySQL but would be interested in other databases that could do something like that.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(8)
我想您可以使用多个
SELECT
与UNION
组合来执行子查询。I suppose you could do a subquery with several
SELECT
s combined withUNION
s.您可以在 PostgreSQL 中执行此操作:
http://www.postgresql.org/ docs/8.2/static/sql-values.html
You can do it in PostgreSQL:
http://www.postgresql.org/docs/8.2/static/sql-values.html
在 Microsoft T-SQL 2008 中,格式为:
即如 Jonathan 上面提到的,但没有“table”关键字。
请参阅:
In Microsoft T-SQL 2008 the format is:
I.e. as Jonathan mentioned above, but without the 'table' keyword.
See:
在标准 SQL 中(SQL 2003 - 请参阅 http://savage.net.au/SQL/)可以使用:
再多一点追逐,您还可以使用:
这些在 MySQL 中是否工作是一个单独的问题 - 但您总是可以要求添加它,或者自己添加它(这就是开源的美妙之处)。
In standard SQL (SQL 2003 - see http://savage.net.au/SQL/) you can use:
With a bit more chasing, you can also use:
Whether these work in MySQL is a separate issue - but you can always ask to get it added, or add it yourself (that's the beauty of Open Source).
从 MariaDB v10.3.3 和 MySQL v8.0.19 开始,您现在可以完全做到这一点!
请参阅文档:MariaDB、MySQL
MariaDB:
我在这里使用了
WITH
因为 MariaDB没有为VALUES ...
提供漂亮的列名称。 您可以在没有列名的联合中使用它:尽管文档似乎没有提到它,您甚至可以将它用作顶级查询:
实际的列名实际上只是第一行值,因此你甚至可以这样做(尽管它不太优雅,并且可能会遇到重复的列名错误):
MySQL:
我现在没有可供测试的 MySQL v8.0.19 实例,但根据根据文档,其中任何一个都应该有效:
与 MariaDB 不同,MySQL 提供自动列名称 column_0、column_1、column_2 等,并且还支持在引用子查询时重命名所有子查询的列。
我不确定,但是这个开发工作日志页面似乎表明MySQL还实现了更短的语法(省略“ROW”,如 MariaDB),或者他们将在不久的将来实现。
Since MariaDB v10.3.3 and MySQL v8.0.19 you can now do exactly that!
See docs: MariaDB, MySQL
MariaDB:
I used a
WITH
here because MariaDB doesn't supply nice column names forVALUES ...
. You can use it in a union without column names:And although the docs don't appear to mention it, you can even use it as a top-level query:
The actual column names are in fact the just first row of values, so you can even do this (though it's inelegant, and you can run into duplicate column name errors):
MySQL:
I don't have an instance of MySQL v8.0.19 to test against right now, but according to the docs either of these should work:
Unlike MariaDB, MySQL supplies automatic column names column_0, column_1, column_2, etc., and also supports renaming all of a subquery's columns when referencing it.
I'm not sure, but this dev worklog page seems to suggest that MySQL has also implemented the shorter sytax (omitting "ROW", like MariaDB), or that they will in the near future.
我找到了这个链接 Temporary Tables With MySQL
I found this link Temporary Tables With MySQL
总之,是的。 在我看来,如果您的 SQL 产品支持通用表表达式 (CTE),即比使用子查询更容易看懂,而且相同的 CTE 可以多次使用,例如,这可以在 SQL 中“创建”一个由 0 到 999 之间的唯一整数组成的序列表,那就更好了Server 2005 及更高版本:
除非您实际上对序列表执行一些有用的操作,例如解析基表中 VARCHAR 列中的字符。
但是,如果您多次或在多个查询中使用此表,该表仅包含文字值,那么为什么不首先将其设为基表呢? 我使用的每个数据库都有一个整数序列表(通常有 100K 行),因为它通常非常有用。
In a word, yes. Even better IMO if your SQL product supports common table expressions (CTEs) i.e. easier on the eye than using a subquery plus the same CTE can be used multiple times e.g. this to 'create' a sequence table of unique integers between 0 and 999 in SQL Server 2005 and above:
except you'd actually do something useful with the Sequence table e.g. parse the characters from a VARCHAR column in a base table.
HOWEVER, if you are using this table, which consists only of literal values, multiple time or in multiple queries then why not make it a base table in the first place? Every database I use has a Sequence table of integers (usually 100K rows) because it is so useful generally.
创建临时表 ( ID int, Name char(100) ) SELECT ....
阅读更多信息: http://dev.mysql.com/doc/refman/5.0/en/create-table.html
(靠近底部)
这样做的好处是如果出现任何问题填充表(数据类型不匹配)时,表会自动删除。
早期的答案使用了 FROM SELECT 子句。 如果可能的话,使用它,因为它可以省去清理桌子的麻烦。
FROM SELECT 的缺点(可能并不重要)是创建的数据集有多大。 临时表允许建立索引,这可能是至关重要的。 以便后续查询。 似乎违反直觉,但即使对于中等大小的数据集(约 1000 行),为查询操作创建索引也会更快。
CREATE TEMPORARY TABLE ( ID int, Name char(100) ) SELECT ....
Read more at : http://dev.mysql.com/doc/refman/5.0/en/create-table.html
( near the bottom )
This has the advantage that if there is any problem populating the table ( data type mismatch ) the table is automatically dropped.
An early answer used a FROM SELECT clause. If possible use that because it saves the headache of cleaning up the table.
Disadvantage ( which may not matter ) with the FROM SELECT is how large is the data set created. A temporary table allows for indexing which may be critical. For the subsequent query. Seems counter-intuitive but even with a medium size data set ( ~1000 rows), it can be faster to have a index created for the query to operate on.