更快地工会表SQL
问题描述:
我TABLE_A更快地工会表SQL
mode bank_id amount
'Outgoing' 1 100
'Incoming' 1 200
'Outgoing' 1 300
'Incoming' 2 200
我需要把它变成
from_bank_id to_bank_id amount
1 null 100
null 1 200
1 null 300
null 2 200
现在我做这个
SELECT
amount,
NULL AS from_bank_id,
bank_id AS to_bank_id
FROM TABLE_A WHERE mode = 'Incoming'
UNION ALL
SELECT
amount,
NULL AS to_bank_id,
bank_id AS from_bank_id
FROM TABLE_A WHERE mode = 'Outgoing'
这个SQL作品并给予我是我想要的输出。只是伸出手去查看是否有更好的方法来实现这一点而不用查询表格两次?
这是第二部分的第一部分的查询Reducing number of query for inner join when joining base on same table
答
SELECT
(CASE WHEN mode = 'Outgoing' THEN bank_id ELSE NULL END) AS from_bank_id,
(CASE WHEN mode = 'Incoming' THEN bank_id ELSE NULL END) AS to_bank_id,
amount
FROM
table_a
PostgreSQL的CASE
表达允许ELSE NULL
与相同的行为(重点煤矿)被省略:
https://www.postgresql.org/docs/current/static/functions-conditional.html
如果没有WHEN条件成立时,CASE表达式的值是ELSE子句的结果。 如果省略ELSE子句并且没有条件为真,则结果为空。
括号也可以省略:
所以查询可以被语法简化为:
SELECT
CASE WHEN mode = 'Outgoing' THEN bank_id END AS from_bank_id,
CASE WHEN mode = 'Incoming' THEN bank_id END AS to_bank_id,
amount
FROM
table_a
...这是稍微更具有可读性。
答
您可以使用CASE
表达式更高效(并且容易)完成此操作。
select
case
when mode = 'Outgoing' then bank_id
else null
end as from_bank_id
,case
when mode = 'Incoming' then bank_id
else null
end as to_bank_id
,amount
from
TABLE_A
注意有使用'UNION'这本身没有什么错,并且确实是一个'UNION ALL'会更快(在返回之前,而不是缓冲的结果)让结果立即返回。 – Dai