在SQL语句中选择常量而不引用表是完全合法的:

SELECT 1, 2, 3

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

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

我想要像上面那样工作,并返回一个3行3列的结果集。


当前回答

在Oracle中

SELECT
  CASE
    WHEN level = 1
    THEN 'HI'
    WHEN level = 2
    THEN 'BYE'
  END TEST
FROM dual
  CONNECT BY level <= 2;

其他回答

在MySQL中,你可以这样做:values (1,2), (3,4);

mysql> values (1,2), (3, 4);
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
| 3 | 4 |
+---+---+
2 rows in set (0.004 sec)

在MySQL 8中,也可以给列命名:

mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
SELECT * 
FROM DUAL 
CONNECT BY ROWNUM <= 9;

试试oracle中的connect by子句,就像这样

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

有关connect by子句的更多信息,请参见此链接:已删除URL,因为oraclebin站点现在是恶意的。

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
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;

就像这样

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;