在表中合并多个选择- Oracle SQL Developer

6ie5vjzr 于 2023-08-04 发布在 Oracle

关注(0)|答案(1)|浏览(122)

我有几个选择查询，希望使用它们的组织名称合并它们。
质询如下：

select ORG_ALL_SCORES.Name, ORG_ALL_SCORES.ALL_SCORES, SUM(REWARD_CONVERSION_FORMULA.FROM_REWARD_VALUE) AS Pasmaand FROM ORG_ALL_SCORES LEFT JOIN REWARDS_LIST ON REWARDS_LIST.ORGANIZATION_ID = ORG_ALL_SCORES.ORGANIZATION_ID
INNER JOIN REWARD_CONVERSION_FORMULA ON REWARD_CONVERSION_FORMULA.TO_REWARDS_LIST_ID = REWARDS_LIST.REWARDS_LIST_ID WHERE REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700022%' GROUP BY ORG_ALL_SCORES.NAME, ORG_ALL_SCORES.ALL_SCORES;

字符串
.

select ORG_ALL_SCORES.Name, ORG_ALL_SCORES.ALL_SCORES, SUM(REWARD_CONVERSION_FORMULA.FROM_REWARD_VALUE) AS Hamlonaghl FROM ORG_ALL_SCORES LEFT JOIN REWARDS_LIST ON REWARDS_LIST.ORGANIZATION_ID
= ORG_ALL_SCORES.ORGANIZATION_ID INNER JOIN REWARD_CONVERSION_FORMULA ON REWARD_CONVERSION_FORMULA.TO_REWARDS_LIST_ID = REWARDS_LIST.REWARDS_LIST_ID WHERE REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700041%' GROUP BY ORG_ALL_SCORES.NAME, ORG_ALL_SCORES.ALL_SCORES;

型
.

select ORG_ALL_SCORES.Name, ORG_ALL_SCORES.ALL_SCORES, SUM(REWARD_CONVERSION_FORMULA.FROM_REWARD_VALUE) AS Shahrvand FROM ORG_ALL_SCORES LEFT JOIN REWARDS_LIST ON REWARDS_LIST.ORGANIZATION_ID = ORG_ALL_SCORES.ORGANIZATION_ID
INNER JOIN REWARD_CONVERSION_FORMULA ON REWARD_CONVERSION_FORMULA.TO_REWARDS_LIST_ID = REWARDS_LIST.REWARDS_LIST_ID WHERE REWARD_CONVERSION_FORMULA.DESCRIPTION LIKE '%700061%' GROUP BY ORG_ALL_SCORES.NAME, ORG_ALL_SCORES.ALL_SCORES;

型
.
现在我想有这样的东西：
x1c 0d1x的数据
.
但是，我对每个查询都有单独的结果：

的
和/或

的
我怎么能这么做呢？

oracle

来源：https://stackoverflow.com/questions/76646537/merging-multiple-selects-together-inside-a-table-oracle-sql-developer

1条答案

按热度按时间

57hvy0tb1#

您可以使用pivot函数（如果数据库支持）或case语句进行聚合，以在单个查询中获得所需的结果。例如-

SELECT o.Name, o.ALL_SCORES, 
    SUM(CASE WHEN rcf.DESCRIPTION LIKE '%700061%' then rcf.FROM_REWARD_VALUE else 0 END) AS Shahrvand,
    SUM(CASE WHEN rcf.DESCRIPTION LIKE '%700041%' then rcf.FROM_REWARD_VALUE else 0 END) AS Hamlonaghl,
    SUM(CASE WHEN rcf.DESCRIPTION LIKE '%700022%' then rcf.FROM_REWARD_VALUE else 0 END) AS Pasmaand
FROM ORG_ALL_SCORES o
INNER JOIN REWARDS_LIST rl ON o.ORGANIZATION_ID = rl.ORGANIZATION_ID
INNER JOIN REWARD_CONVERSION_FORMULA rcf ON rl.REWARDS_LIST_ID  = rcf.TO_REWARDS_LIST_ID 
WHERE REGEXP_LIKE(rcf.DESCRIPTION, '700061|700041|700022') 
GROUP BY 1, 2

字符串
或者，有无数其他方法可以将结果合并为后处理，同时继续使用单独的查询。例如，通过使用pandas数据框或ETL工具（如Talend）。Talend提供了一个tMap组件，可以帮助您轻松实现您正在寻找的结果。

赞(0）回复(0）举报 2023-08-04

我来回答

在表中合并多个选择- Oracle SQL Developer

1条答案

相关问题

热门标签

最新问答