SQL 如何选择填充常量的多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2201170/
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
How to select multiple rows filled with constants?
提问by Blagovest Buyukliev
Selecting constants without referring to a table is perfectly legal in an SQL statement:
在 SQL 语句中选择常量而不引用表是完全合法的：
SELECT 1, 2, 3
The result set that the latter returns is a single row containing the values. I was wondering if there is a way to select multiple rows at once using a constant expression, something kind of:
后者返回的结果集是包含值的单行。我想知道是否有办法使用常量表达式一次选择多行，例如：
SELECT ((1, 2, 3), (4, 5, 6), (7, 8, 9))
I would want something like the above that works and returns a result set with 3 rows and 3 columns.
我想要类似上面的东西，并返回一个 3 行 3 列的结果集。
回答by Dewfy
SELECT 1, 2, 3
UNION ALL SELECT 4, 5, 6
UNION ALL SELECT 7, 8, 9
回答by Quassnoi
In PostgreSQL, you can do:
在 中PostgreSQL，您可以执行以下操作：
SELECT *
FROM (
VALUES
(1, 2),
(3, 4)
) AS q (col1, col2)
In other systems, just use UNION ALL:
在其他系统中，只需使用UNION ALL：
SELECT 1 AS col1, 2 AS col2
-- FROM dual
-- uncomment the line above if in Oracle
UNION ALL
SELECT 3 AS col1, 3 AS col2
-- FROM dual
-- uncomment the line above if in Oracle
In Oracle, SQL Serverand PostgreSQL, you also can generate recordsets of arbitrary number of rows (providable with an external variable):
在Oracle,SQL Server和 中PostgreSQL，您还可以生成任意行数的记录集（可通过外部变量提供）：
SELECT level
FROM dual
CONNECT BY
level <= :n
in Oracle,
在Oracle，
WITH q (l) AS
(
SELECT 1
UNION ALL
SELECT l + 1
FROM q
WHERE l < @n
)
SELECT l
FROM q
-- OPTION (MAXRECURSION 0)
-- uncomment line above if @n >= 100
in SQL Server,
在SQL Server，
SELECT l
FROM generate_series(1, $n) l
in PostgreSQL.
在PostgreSQL。
回答by Tregoreg
Following bare VALUEScommand works for me in PostgreSQL:
以下裸VALUES命令在 PostgreSQL 中对我有用：
VALUES (1,2,3), (4,5,6), (7,8,9)
回答by Sushant Butta
Try the connect by clause in oracle, something like this
试试oracle中的connect by子句，像这样
select level,level+1,level+2 from dual connect by level <=3;
For more information on connect by clause follow this link : removed URL because oraclebin site is now malicious.
有关 connect by 子句的更多信息，请访问此链接：已删除 URL，因为 oraclebin 站点现在是恶意的。
回答by bigtunacan
For Microsoft SQL Server or PostgreSQL you may want to try this syntax
对于 Microsoft SQL Server 或 PostgreSQL，您可能想尝试这种语法
SELECT constants FROM (VALUES ('[email protected]'), ('[email protected]'), ('[email protected]')) AS MyTable(constants)
SELECT constants FROM (VALUES ('[email protected]'), ('[email protected]'), ('[email protected]')) AS MyTable(constants)
You can also view an SQL Fiddle here: http://www.sqlfiddle.com/#!17/9eecb/34703/0
您还可以在此处查看 SQL Fiddle：http://www.sqlfiddle.com/#!17/9eecb/34703 /0
回答by Petr Szturc
Oracle. Thanks to this post PL/SQL - Use "List" Variable in Where In Clause
甲骨文。感谢这篇文章PL/SQL - 在 Where In Clause 中使用“List”变量
I put together my example statement to easily manually input values (being reused in testing an application by testers):
我将示例语句放在一起，以轻松手动输入值（测试人员在测试应用程序时重复使用）：
WITH prods AS (
SELECT column_value AS prods_code
FROM TABLE(
sys.odcivarchar2list(
'prod1',
'prod2'
)
)
)
SELECT * FROM prods
回答by grokster
SELECT *
FROM DUAL
CONNECT BY ROWNUM <= 9;
回答by Nicholas Sushkin
Here is how I populate static data in Oracle 10+ using a neat XML trick.
这是我使用简洁的 XML 技巧在 Oracle 10+ 中填充静态数据的方法。
create table prop
(ID NUMBER,
NAME varchar2(10),
VAL varchar2(10),
CREATED timestamp,
CONSTRAINT PK_PROP PRIMARY KEY(ID)
);
merge into Prop p
using (
select
extractValue(value(r), '/R/ID') ID,
extractValue(value(r), '/R/NAME') NAME,
extractValue(value(r), '/R/VAL') VAL
from
(select xmltype('
<ROWSET>
<R><ID>1</ID><NAME>key1</NAME><VAL>value1</VAL></R>
<R><ID>2</ID><NAME>key2</NAME><VAL>value2</VAL></R>
<R><ID>3</ID><NAME>key3</NAME><VAL>value3</VAL></R>
</ROWSET>
') xml from dual) input,
table(xmlsequence(input.xml.extract('/ROWSET/R'))) r
) p_new
on (p.ID = p_new.ID)
when not matched then
insert
(ID, NAME, VAL, CREATED)
values
( p_new.ID, p_new.NAME, p_new.VAL, SYSTIMESTAMP );
The merge only inserts the rows that are missing in the original table, which is convenient if you want to rerun your insert script.
合并仅插入原始表中缺失的行，如果您想重新运行插入脚本，这很方便。
回答by Vitaliy Ulantikov
An option for DB2:
DB2 的一个选项：
SELECT 101 AS C1, 102 AS C2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 201 AS C1, 202 AS C2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 301 AS C1, 302 AS C2 FROM SYSIBM.SYSDUMMY1
回答by Dejoto
select (level - 1) * row_dif + 1 as a, (level - 1) * row_dif + 2 as b, (level - 1) * row_dif + 3 as c
from dual
connect by level <= number_of_rows;
something like that
类似的东西
select (level - 1) * 3 + 1 as a, (level - 1) * 3 + 2 as b, (level - 1) * 3 + 3 as c
from dual
connect by level <= 3;
