将以下查询作为一个查询加入是否可行

问题描述：

select a.usr_id, a.usr_desc as ,a.usr_type,b.usr_desc as usr_prof_name 
from sc_usr_prof_m a, sc_usr_type_m b 
where a.usr_type=b.usr_type 
and a.current_status='A' 
order by a.usr_id;

select distinct a.usr_id,a.usr_desc,b.dept_name 
from sc_usr_prof_m a ,sc_user_depts b 
where a.usr_id=b.usr_id 
and a.usr_status='E' 
and a.current_status='A';

select a.usr_id,a.usr_desc,b.curr_code,b.min_amt,b.max_amt 
from sc_usr_prof_m a, sc_auth_limit_m b 
where a.usr_id=b.usr_id 
and a.usr_status='E' 
and a.current_status='A' 
and b.max_amt not in ('0') 
order by b.usr_id;

select a.usr_id,a.usr_desc,b.msg_type 
from sc_usr_prof_m a, sc_user_msgs_m b 
where a.usr_id=b.usr_id 
and a.usr_status='E' 
and a.current_status='A' 
and b.swift_enable='Y' 
order by a.usr_id;

欢迎来到SO，我格式化了您的查询，并根据您的标题添加了一个简单的问题给身体，但是您应该为您的实际帖子添加更多内容。如果您给出了一个表结构示例，一些示例数据以及您期望结果数据集看起来像样本的示例，那么这也将有所帮助。请花一些时间阅读[如何问]（http://*.com/help/how-to-ask）部分。 – gmiley

可行的，是的。明智的，可能不是。第一个查询过滤来自'sc_usr_prof_m'的一组不同的记录，因此将其与其他查询结合可以更改结果集。每个查询连接到不同的表，这些表大概没有'usr_id'之外的公共连接列，以便生成笛卡儿积。 – APC

答

没有KNO在你的餐桌结构旁边，很难准确地说出哪种方式会给你带来最好的结果。你可能将查询重写为一个，将各个表连接在一起，但似乎有很多不同的表选择您选择。不知道你的餐桌设计，很难说这是否是最好的行动方案。另外，如果您只是想将结果集组合成单个数据集，基本上将每个查询的结果合并为单个输出，那么您可以使用UNION [ALL]。您需要确保每个查询的选择列表中的列都正确匹配/对齐。这并不理想，但应该给你想要的东西。

with combined_results as (
    select a.usr_id as usr_id, 
     a.usr_desc as usr_desc, 
     a.usr_type as usr_type, 
     b.usr_desc as usr_prof_name, 
     null as dept_name, 
     null as cur_code, 
     0 as min_amt, 
     0 as max_amt, 
     null as msg_type 
    from sc_usr_prof_m a, sc_usr_type_m b 
    where a.usr_type=b.usr_type 
    and a.current_status='A' 
    union all 
    select distinct 
     a.usr_id as usr_id, 
     a.usr_desc as usr_desc, 
     null as usr_type, 
     null as usr_prof_name, 
     b.dept_name as dept_name, 
     null as cur_code, 
     0 as min_amt, 
     0 as max_amt, 
     null as msg_type 
    from sc_usr_prof_m a ,sc_user_depts b 
    where a.usr_id=b.usr_id 
    and a.usr_status='E' 
    and a.current_status='A' 
    union all 
    select a.usr_id as usr_id, 
     a.usr_desc as usr_desc, 
     null as usr_type, 
     null as usr_prof_name, 
     null as dept_name, 
     b.curr_code as curr_code, 
     b.min_amt as min_amt, 
     b.max_amt as max_amt, 
     null as msg_type 
    from sc_usr_prof_m a, sc_auth_limit_m b 
    where a.usr_id=b.usr_id 
    and a.usr_status='E' 
    and a.current_status='A' 
    and b.max_amt not in ('0') 
    union all 
    select a.usr_id as usr_id, 
     a.usr_desc as usr_desc, 
     null as usr_type, 
     null as usr_prof_name, 
     null as dept_name, 
     null as cur_code, 
     0 as min_amt, 
     0 as max_amt, 
     b.msg_type as msg_type 
    from sc_usr_prof_m a, sc_user_msgs_m b 
    where a.usr_id=b.usr_id 
    and a.usr_status='E' 
    and a.current_status='A' 
    and b.swift_enable='Y' 
) 
select * 
from combined_results 
order by usr_id;

我也建议你写你的加入：

select a.col1, a.col2, b.col3, b.col4 
from my_a_table a 
join my_b_table b 
on b.col1 = a.col1 
where a.col1 = 123;

而不是如何目前你正在做它：

select a.col1, a.col2, b.col3, b.col4 
from my_a_table a, my_b_table b 
where b.col1 = a.col1 
and a.col1 = 123;

如果没有其他原因，而不是可读性，尤其是在具有多个连接的较大查询中。

帮我解决这个问题 – SMILY

将以下查询作为一个查询加入是否可行

相关推荐