如何选择填有常量的多行?

Question

在SQL语句中，不需要引用表格就可以选择常量是完全合法的：

SELECT 1, 2, 3

后一个返回的结果集是包含值的单行。我想知道是否有一种方法可以使用常量表达式同时选择多行，类似于：

SELECT ((1, 2, 3), (4, 5, 6), (7, 8, 9))

我希望得到像上面的结果，该结果应该能够工作并返回一个3行3列的结果集。

- Blagovest Buyukliev

---

你上面想象的语法比官方语法更漂亮（并且更符合INSERT INTO），只是这么说。 - Pete Alvin

@PeteAlvin 在Postgres中，想象中的语法已经有了意义（选择一个带有元组的单行）。 - Kirill Bulygin

下面的 SQL Server 答案适用于 SQL Server，并且几乎与此语法匹配。https://dev59.com/HXI95IYBdhLWcg3wrACw#53269562 - BenPen

Answer 1

SELECT 1, 2, 3
UNION ALL SELECT 4, 5, 6
UNION ALL SELECT 7, 8, 9

- Dewfy

---

我曾将其与SQL Server一起使用，它可以正常运行，但我必须使用AS为第一个SELECT语句中的列指定别名。 - Sled

谢谢@ArtB，这个评论可能会帮助其他开发者获得正确的语法。 - Dewfy

如果在每个SELECT语句后面添加FROM dual，并在可能存在的UNION ALL之前添加values，则可以完美地在Oracle APEX 5.1中创建具有静态内容的“经典报表”表格。 - VELFR

Answer 2

在 PostgreSQL 中，您可以执行以下操作：

SELECT  *
FROM    (
        VALUES
        (1, 2),
        (3, 4)
        ) AS q (col1, col2)

在其他系统中，只需使用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

在Oracle、SQL Server和PostgreSQL中，您还可以生成任意行数的记录集（可由外部变量提供）：

SELECT  level
FROM    dual
CONNECT BY
        level <= :n

在 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

在 SQL Server 中，

SELECT  l
FROM    generate_series(1, $n) l

在 PostgreSQL 中。

- Quassnoi

---

回答了我略有不同的问题：如何在Oracle中执行“SELECT 1”（“SELECT 1 FROM Dual”可行）。 - Aasmund Eldhuset

Answer 3

对于Microsoft SQL Server或PostgreSQL，您可以尝试此语法：

SELECT constants FROM (VALUES ('[e-mail]'), ('[e-mail]'), ('[e-mail]')) AS MyTable(constants)

您也可以在这里查看SQL Fiddle：http://www.sqlfiddle.com/#!17/9eecb/34703/0

- bigtunacan

---

这在SQL Server 2010中绝对可行。多列也可以：从(VALUES (1,'[e-mail]'), (2,'[e-mail]'), (3, '[e-mail]')) AS MyTable(constants,email)选择常量和电子邮件。 - BenPen

你如何命名列？例如，我有'[e-mail]' AS email，但是我收到错误消息Incorrect syntax near the keyword 'AS'。 - Michael

@Michael 如果你有完整的查询，会更有帮助。 - bigtunacan

Answer 4

以下的裸 VALUES 命令在PostgreSQL中对我有效：

VALUES (1,2,3), (4,5,6), (7,8,9)

- Tregoreg

---

也可以在T-SQL中作为多行插入子句使用。首先插入到表变量或临时表中可能有效，但需要多个步骤。 - brianary

Answer 5

Oracle。 感谢这篇文章 PL/SQL - Use "List" Variable in Where In Clause

我编写了一个示例语句，以便轻松手动输入值（在测试应用程序时由测试人员重复使用）：

WITH prods AS (
    SELECT column_value AS prods_code 
    FROM TABLE(
        sys.odcivarchar2list(
            'prod1', 
            'prod2'
        )
    )
)
SELECT * FROM prods

- Petr Szturc

---

这真是救命稻草。需要注意的一点是：如果你遇到了太多值错误，可以在WITH子句中使用UNION ALL。 - ScrappyDev

Answer 6

尝试在 Oracle 中使用 connect by 子句，类似这样：

select level,level+1,level+2 from dual connect by level <=3;

关于connect by子句的更多信息，请访问此链接：由于oraclebin网站现在存在恶意软件，已删除URL。

- Sushant Butta

Answer 7

SELECT * 
FROM DUAL 
CONNECT BY ROWNUM <= 9;

- grokster

---

我已经寻找那个黑客技巧很久了！！！非常感谢！ - Laurent K

Answer 8

以下是使用MySQL请求直接创建自定义行的方法SELECT：

SELECT ALL *
FROM (
    VALUES
        ROW (1, 2, 3),
        ROW (4, 5, 6),
        ROW (7, 8, 9)
) AS dummy (c1, c2, c3)

提供一个名为dummy的表格：

c1   c2   c3
-------------
 1    2    3
 4    5    6
 7    8    9

已测试过，适用于 MySQL 8

- JCH77

---

不错啊，伙计，对于MySQL来说，这就是正确的选择（还有MySQL 8）。 - StPaulis

如果您删除ROW关键字，它将适用于SQL Server：SELECT ALL * FROM ( VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)) AS dummy (c1, c2, c3); - undefined

Answer 9

以下是我使用一种巧妙的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 );

合并只会插入原始表中缺失的行，这很方便，如果你想重新运行插入脚本。

- Nicholas Sushkin

Answer 10

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

- Vitaliy Ulantikov