来自两个独立查询的内部联接结果

Question

我必须从三个不同的表中查询结果，到目前为止，我已经能够分别获得单独的结果。现在我想加入这些结果。以下两段代码是我的查询。我应该以某种方式将两者嵌套吗？我想在底部查询的s.STORE_NUM和str.NUM上进行内部联接，但我如何分别声明这两个？

SELECT
S.STORE_NUM,
M.YEAR_FISCAL,
--M.YEAR_445,
M.MONTH_NAME_445,
M.WEEK_START_DT_MON,
SUM(SALES_NET) - SUM(S.SALES_GCARD_NET) as SALES_WT_GC,
SUM(S.TRAFFIC_COUNT) AS TRAFFIC,
SUM(S.TICKETS_NET) AS  TRANS,
SUM(S.QTY_NET) AS UNITS,
-- Calculation level:
SUM(S.QTY_NET)/SUM(S.TRAFFIC_COUNT) AS UOT,
SUM(S.QTY_NET)/SUM(S.TICKETS_NET) AS UPT,
SUM(S.TICKETS_NET)/SUM(S.TRAFFIC_COUNT) AS CONVPCT
FROM DNA_PUBLIC..RETAIL_SALES_BY_STORE_BY_DAY S
JOIN DNA_PUBLIC.ADMIN.DAY_MAP M
ON S.DATE_DATA = M.DATE_DT
WHERE  S.DATE_DATA BETWEEN '08/24/2020' AND '09/27/2020'
AND S.SALES_NET > 0
AND S.TRAFFIC_COUNT > 0
AND S.QTY_NET > 0
AND S.TICKETS_NET > 0
AND S.STORE_NUM = 2Ah 
GROUP BY 1,2,3,4
ORDER BY S.STORE_NUM, WEEK_START_DT_MON 

和

SELECT r.str_num, r.str_nam, r.str_dst, s.region_name, s.div_name, r.STR_ADDR, r.str_cty, r.str_zip5 
FROM admin.retail_store_names r JOIN admin.store_dim s ON r.str_num = s.store_num

Answer 1

我想在底部查询的s.STORE_NUM和str.NUM上进行内部联接，但我如何分别声明这两者？

您可以通过将查询转换为子查询并加入外部查询来连接这两个结果集。order by子句转到外部查询：

select t1.*, t2.*
from (
-- first query goes here (without the "order by")
) t1
inner join (
-- second query goes there
) t2 on t2.str_num = t1.store_num
order by t1.str_num, t2.week_start_dt_mon

Answer 2

你也可以做-

SELECT
S.STORE_NUM,
M.YEAR_FISCAL,
--M.YEAR_445,
M.MONTH_NAME_445,
M.WEEK_START_DT_MON,
max(r.str_num) As str_num,
max(r.str_nam) As str_nam,
max(r.str_dst) As str_dst,
max(st.region_name) As region_name,
max(st.div_name) As div_name,
max(r.STR_ADDR) As STR_ADDR,
max(r.str_cty) As str_cty,
max(r.str_zip5) As str_zip5,
SUM(SALES_NET) - SUM(S.SALES_GCARD_NET) as SALES_WT_GC,
SUM(S.TRAFFIC_COUNT) AS TRAFFIC,
SUM(S.TICKETS_NET) AS  TRANS,
SUM(S.QTY_NET) AS UNITS,
-- Calculation level:
SUM(S.QTY_NET)/SUM(S.TRAFFIC_COUNT) AS UOT,
SUM(S.QTY_NET)/SUM(S.TICKETS_NET) AS UPT,
SUM(S.TICKETS_NET)/SUM(S.TRAFFIC_COUNT) AS CONVPCT
FROM DNA_PUBLIC.RETAIL_SALES_BY_STORE_BY_DAY S
JOIN DNA_PUBLIC.ADMIN.DAY_MAP M
ON S.DATE_DATA = M.DATE_DT
JOIN admin.retail_store_names r
ON S.Store_num = r.Str_num
JOIN admin.store_dim st
ON r.str_num = s.store_num
WHERE  S.DATE_DATA BETWEEN '08/24/2020' AND '09/27/2020'
AND S.SALES_NET > 0
AND S.TRAFFIC_COUNT > 0
AND S.QTY_NET > 0
AND S.TICKETS_NET > 0
AND S.STORE_NUM = 2Ah 
GROUP BY 1,2,3,4
ORDER BY S.STORE_NUM, M.WEEK_START_DT_MON