SQL 这个例子中的 WITH 语句是做什么的？我正在尝试随机生成数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1413465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the WITH statement doing in this example? I'm trying to randomly generate data
提问by cheesysam
INSERT INTO files (fileUID, filename)
WITH fileUIDS(fileUID) AS
( VALUES(1) UNION ALL
SELECT fileUID+1 FROM fileUIDS WHERE fileUID < 1000 )
SELECT fileUID,
TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdefgHij', '1234567890' )
FROM fileUIDS;
回答by OMG Ponies
The WITH syntax is the same as using either a local temp table or inline view. To my knowledge, it's only supported in SQL Server (2005+, called Common Table Expressions) and Oracle (9i+, called Subquery Factoring). The intended use is for creating a basic view that is used (ie: joined to) multiple times in a single query.
WITH 语法与使用本地临时表或内联视图相同。据我所知，它仅在 SQL Server（2005+，称为 Common Table Expressions）和 Oracle（9i+，称为 Subquery Factoring）中受支持。预期用途是创建在单个查询中多次使用（即：加入）的基本视图。
Here's a typical example:
下面是一个典型的例子：
WITH example AS (
SELECT q.question_id,
t.tag_name
FROM QUESTIONS q
JOIN QUESTION_TAG_XREF qtf ON qtf.question_id = t.question_id
JOIN TAGS t ON t.tag_id = qtf.tag_id)
SELECT t.title,
e1.tag_name
FROM QUESTIONS t
JOIN example e1 ON e1.question_id = t.question_id
...which will return identical results if you use:
...如果您使用，它将返回相同的结果：
SELECT t.title,
e1.tag_name
FROM QUESTIONS t
JOIN (SELECT q.question_id,
t.tag_name
FROM QUESTIONS q
JOIN QUESTION_TAG_XREF qtf ON qtf.question_id = t.question_id
JOIN TAGS t ON t.tag_id = qtf.tag_id) e1 ON e1.question_id = t.question_id
The example you provided:
您提供的示例：
WITH fileUIDS(fileUID) AS (
VALUES(1)
UNION ALL
SELECT t.fileUID+1
FROM fileUIDS t
WHERE t.fileUID < 1000 )
INSERT INTO files
(fileUID, filename)
SELECT f.fileUID,
TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdefgHij', '1234567890' )
FROM fileUIDS f;
...is a recursive one. It's starting at 1, generating 999 fileuids in total (it would be 1,000 if it had started at 0).
...是一个递归的。它从 1 开始，总共生成 999 个文件 uid（如果它从 0 开始，它将是 1,000）。
回答by Joe Phillips
WITH x AS (...)
This will take the output of the ...and treat it as a table named x, temporarily.
这将获取 的输出...并将其视为x临时名为 的表。
WITH x AS (...)
SELECT * FROM x
This statement will essentially give you the exact same thing as the ...outputs but it will instead be referenced as the table x
这个语句本质上会给你与...输出完全相同的东西，但它会被引用为表x
回答by Mark Callison
It is creating CTE (Common Table Expression). This is a basically a table that you don't have to create, drop, or declare in anyway. It will be automatically deleted after the batch has ran.
它正在创建 CTE（通用表表达式）。这是一个基本上不需要创建、删除或声明的表。批处理运行后，它将自动删除。
Check out http://4guysfromrolla.com/webtech/071906-1.shtmlfor more info.
回答by NotMe
The WITH word is used to create a Common Table Expression (CTE). In this case, it's creating an inline table that the "select fileUID, ..." part is pulling data from.
WITH 字用于创建公用表表达式 (CTE)。在这种情况下，它创建了一个内联表，“select fileUID, ...”部分从中提取数据。
