如何连接SQL中列不匹配的两个表

Question

我有两个不同的表，在选择查询中，我需要带来另一列的SUM，但这两个表都有一些类似的数据，但仍然在查询输出值不来好。

我使用下面的查询:-

查询1

select COUNTRY, SUM (BUDGET_SALES) AS BUDGET_SALES
from ANALYSE.LCM_COMMON_BUDGET_SALES
WHERE WEEK_START_DATE=@WEEK_COMMENCING_VAR
group by COUNTRY

查询2

select distinct cntry AS COUNTRY, SUM (B.BUDGET_SALES) AS BUDGET
from [publish].[LCM_COMMON_HNB_WEEKLY_ACTUAL] A

left join ANALYSE.LCM_COMMON_BUDGET_SALES B
ON --A.bnnr=B.BANNER 
A.cntry=B.COUNTRY 
--and A.Week_End=B.WEEK_END_DATE
and A.Week_Commencing=B.WEEK_START_DATE
WHERE A.Week_Commencing=@WEEK_COMMENCING_VAR
group by cntry

查询1输出

BUDGET_SALES41095952.67

最有可能的是您的表[publish]。[LCM_COMMON_HNB_WEEKLY_ACTUAL]的粒度与表分析不同(可能更低)。LCM_COMMON_BUDGET_SALES所以这就是我要做的:

1. 首先进入相同的谷物-国家和星期。您应独立核实国家每周预算金额是否符合预期

With CountryWeeklyActual as
(
select Country, WEEK_START_DATE, SUM(BUDGET_SALES) as ActualBudgetSum
from [publish].[LCM_COMMON_HNB_WEEKLY_ACTUAL]
where WEEK_START_DATE = @WEEK_COMMENCING_VAR
group by Country, WEEK_START_DATE
), CountryWeeklyCommon as
(
select COUNTRY, Week_Commencing, SUM(BUDGET_SALES) as CommonBudgetSum
from ANALYSE.LCM_COMMON_BUDGET_SALES
where Week_Commencing = @WEEK_COMMENCING_VAR
Group by COUNTRY, Week_Commencing 
)

2. 相同粒度级别的连接

select wa.COUNTRY, wa.WEEK_START_DATE, wa.ActualBudgetSum, wc.Week_Commencing, wc.CommonBudgetSum
From CountryWeeklyActual wa 
left join CountryWeeklyCommon wc
on wa.COUNTRY=wc.Country and wa.WEEK_START_DATE=wc.Week_Commencing

从这里你可以决定你需要包括哪些字段。