连接查询结果

Question

我是sql新手，在连接查询结果表和现有表时遇到了问题。我一直试图将查询结果命名为res_tab，但似乎行不通。我只希望能够将查询结果与现有的表连接起来。这是我到目前为止所知道的：

(select distinct op_id
from cmpr_dept_vmdb.cust_promotion
where promo_id in ('TB4M40', 'TB4M41', 'TB4M42')
and regstrn_status_cd = 'R') as res_tab;

select elite_hist.op_id
from cmpr_dept_vmdb.elite_hist_detail as elite_hist
where elite_hist.instant_elt_promo_cd in ('F1', 'F2', 'F3')
inner join elite_hist
on res_tab.op_id = elite_hist.op_id

它返回以下错误:语法错误:需要')‘和'as’关键字之间的内容

Answer 1

SQL select语法为

[SELECT] ...
[FROM] .....
[JOIN] ....
[WHERE] ....
[GROUP BY] .....

你看起来想要这样join。

select elite_hist.op_id
from cmpr_dept_vmdb.elite_hist_detail as elite_hist
inner join 
(
    select distinct op_id
    from cmpr_dept_vmdb.cust_promotion
    where promo_id in ('TB4M40', 'TB4M41', 'TB4M42')
    and regstrn_status_cd = 'R'
) as res_tab;
on res_tab.op_id = elite_hist.op_id
where elite_hist.instant_elt_promo_cd in ('F1', 'F2', 'F3')

Answer 2

您似乎想要正确使用带相关性的subquery方法

select distinct elite_hist.op_id
from cmpr_dept_vmdb.elite_hist_detail as elite_hist
where instant_elt_promo_cd in ('F1', 'F2', 'F3') and 
      exists (select 1 
              from cmpr_dept_vmdb.cust_promotion as res_tab
              where res_tab.op_id = elite_hist.op_id and
                    res_tab.instant_elt_promo_cd in ('F1', 'F2', 'F3') and
                    res_tab.regstrn_status_cd = 'R
             );