MySQL 如何选择几个硬编码的 SQL 行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6345635/
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 several hardcoded SQL rows?
提问by Eugene
If you execute this query
如果您执行此查询
SELECT 'test-a1' AS name1, 'test-a2' AS name2
the result will be a one row-selection with two columns having these values:
结果将是具有以下值的两列的单行选择：
test-a1, test-a2
How can I modify the above query to have a selection with several rows, e.g.
如何修改上述查询以选择多行，例如
test-a1, test-a2
test-b1, test-b2
test-c1, test-c2
I know how to do this with UNION but I feel that there exists a more simple way to do it.
我知道如何用 UNION 做到这一点，但我觉得有一种更简单的方法来做到这一点。
PS. Sorry for such a basic question, it is very hard to google it.
附注。对不起，这么基本的问题，很难用谷歌搜索。
采纳答案by HLGEM
UNION ALL is the best bet. It's faster than UNION and you will have mutually exclusive rows.
UNION ALL 是最好的选择。它比 UNION 快，并且您将拥有互斥的行。
回答by Muthu
Values keyword can be used as below.
Values 关键字可以如下使用。
select * from
(values ('test-a1', 'test-a2'), ('test-b1', 'test-b2'), ('test-c1', 'test-c2')) x(col1, col2)
回答by openshac
SELECT 'test-a1' AS name1, 'test-a2' AS name2
UNION ALL
SELECT 'test-b1', 'test-b2'
UNION ALL
SELECT 'test-c1', 'test-c2'
回答by Alok Chaudhary
Extending the answer of @openshacfor oracle, as the below mentioned code works for oracle:
为 oracle扩展@opensac的答案，因为下面提到的代码适用于 oracle：
SELECT 'test-a1' AS name1, 'test-a2' AS name2 from dual
UNION ALL
SELECT 'test-b1', 'test-b2' from dual
UNION ALL
SELECT 'test-c1', 'test-c2' from dual
回答by ajaali
You can use a temp table, fill it up with your results and then select from it
您可以使用临时表，用您的结果填充它，然后从中选择
create table #tmpAAA (name1 varchar(10), name2 varchar(10))
insert into #tmpAAA (name1, name2)
values ('test_a', 'test_b'),
('test_c', 'test_d'),
('test_e', 'test_f'),
('test_g', 'test_h'),
('test_i', 'test_j');
select * from #tmpAAA;
This will return
这将返回
name1 name2
==================
test_a test_b
test_c test_d
test_e test_f
test_g test_h
test_i test_j
回答by TizzyFoe
I'd love to hear is anyone has a better solution. In the past I've used this:
我很想听到有人有更好的解决方案。过去我用过这个：
Select top 3 'Hardcode'
from tableWithLotsOfRows
Would you mind switching abc, with 123?
你介意把 abc 换成 123 吗？
select top 3
'test-A'+convert(varchar, row_number() over (order by PrimaryKey)),
'test-B'+convert(varchar, row_number() over (order by PrimaryKey))
from tableWithLotsOfRows
that should return something like:
应该返回如下内容：
TestA1, Test-B1
TestA2, Test-B2
TestA3, Test-B3
回答by vencedor
in mysql you could use union
在 mysql 你可以使用 union
select * from
(select 2006 as year union
select 2007 as year union
select 2008
) as years
回答by Joe Borysko
As of MySQL 8.0.19, it is possible to do
从 MySQL 8.0.19 开始，可以做到
SELECT
column_0 AS name1,
column_1 AS name2
FROM
(VALUES
ROW('test-a1','test-a2'),
ROW('test-b1','test-b2'),
ROW('test-c1','test-c2')
) AS hardcodedNames
Which returns
哪个返回
name1 name2 ================== test-a1 test-a2 test-b1 test-b2 test-c1 test-c2
A note on column names
关于列名的说明
The columns of the table output from VALUES have the implicitly named columns column_0, column_1, column_2, and so on, always beginning with 0.
VALUES 表输出的列具有隐式命名的列 column_0、column_1、column_2 等，始终以 0 开头。
Documentation here: https://dev.mysql.com/doc/refman/8.0/en/values.html.
