Oracle。 感谢这篇文章PL/SQL -在Where in子句中使用“List”变量

我把我的示例语句放在一起，以便轻松地手动输入值(测试人员在测试应用程序时重用):

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

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

对于Microsoft SQL Server或PostgreSQL，您可能想要尝试此语法

SELECT constants FROM (VALUES ('foo@gmail.com')， ('bar@gmail.com')， ('baz@gmail.com')) AS MyTable(常量)

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

在Oracle SQL中，您可以使用JSON_TABLE从JSON创建表:

SELECT
   t.emp_name,
   t.emp_age
FROM
   JSON_TABLE (
   '{
     "employees": [{name:"Alice", age: 19}, {name: "Bob", age: 23}]
  }',
  '$'
  COLUMNS (
     NESTED PATH '$.employees[*]'
     COLUMNS (
        emp_name VARCHAR2(50) PATH '$.name',
        emp_age NUMBER(3,0) PATH '$.age'
     )
  )
) t;

在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 |
+---+---+---+---+