英文:
Two columns combined "as" one in SQL
问题
I have three columns
Field A = A, B, C
Field B = D, E, F
Field C = G, H, I
如何将它们合并为一行，而不使用"union all"？
像这样：
select Field A as 1 from TableZ
union all
select Field B as 1 from TableZ
union all
select Field C as 1 from TableZ
我不想使用联合是因为我正在进行的实际查询很大，所以不想重复代码3次。重要的是，我不希望连接这三列，我只想将它们添加到单行中。
英文:
I have three columns
Field A = A,B,C
Field B = D,E,F
Field C = G,H,I
How can I combine them into one single row WITHOUT using "union all"?
Like this:
select Field A as 1 from TableZ
union all
select Field B as 1 from TableZ
union all
select Field C as 1 from TableZ
The reason I do not want to use a union is because the real query I am making is big, so I don't want to repeat the code 3 times. Important to say is that I do not wish to concat the three columns, I just want to add them in single row.
答案1
得分: 2
如果您不想重复您的查询 - 使用通用表达式，通常称为CTE。
以下是一个示例：
WITH t1 as (
-- 我非常复杂的查询
从TableZ中选择my_fields
)
从t1中选择FieldA作为f1
联合全部选择FieldB作为f1从t1
联合全部选择FieldC作为f1从t1
英文:
If you don't want to repeat your query - use Common Table Expressions, commonly known as CTE.
Here is an example:
WITH t1 as (
-- My very long ang complicated query
SELECT my_fields
FROM TableZ
)
select FieldA as f1 from t1
union all
select FieldB as f1 from t1
union all
select FieldC as f1 from t1
答案2
得分: 1
以下是您要翻译的内容：
假设您想要对这些列进行转置，而不是“将它们合并为一行”（请参见我的评论），我提供了与markalex的解决方案的替代方法：
with original_query as (
SELECT FieldA, FieldB, FieldC
FROM TableZ
),
distribute (discriminator) as (
select 1 from rdb$database
union all select 2 from rdb$database
union all select 3 from rdb$database
)
select
case discriminator
when 1 then FieldA
when 2 then FieldB
when 3 then FieldC
end as "1"
from original_query
cross join distribute
这里的助手CTE“distribute”与您的原始查询进行了交叉连接，然后您使用“distribute.discriminator”的值来决定显示哪一列。
dbfiddle链接：https://dbfiddle.uk/A6asW5iZ
英文:
Assuming you want to transpose those columns instead of "combine them into one single row" (see also my comment), I offer an alternative to the solution of markalex:
with original_query as (
SELECT FieldA, FieldB, FieldC
FROM TableZ
),
distribute (discriminator) as (
select 1 from rdb$database
union all select 2 from rdb$database
union all select 3 from rdb$database
)
select
case discriminator
when 1 then FieldA
when 2 then FieldB
when 3 then FieldC
end as "1"
from original_query
cross join distribute
Here the helper CTE distribute is cross-joined with your original query, and you then use the values of distribute.discriminator to decide which column to show.
dbfiddle: https://dbfiddle.uk/A6asW5iZ
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论