sql-server - 如何划分两个查询然后分组？

Question

我需要划分两个查询，但我需要保存“分组依据”类别。通过我的查询，我只得到值及其笛卡尔积。

Select m2.regionname, m2.indicatorname  CAST( m2.a2Value as float) / 
m1.a1Value
from(
select r.name as regionname , ina.name as indicatorname, sum(a.value) as 
a1Value
from Region as "r"
left join city_region as "cr" on r.region_id = cr.region_id
left join Office as "o" on cr.city_id = o.city_id
left join Assets as "a" on o.office_id = a.office_id
left join Indicators as "i" on a.indicator_id = i.indicator_id
left join IndicatorNames as "ina" on i.indicator_name_id =              
ina.indicator__name_id
where a.month between '01-01-2019' and '31-01-2019'
group by r.name, ina.name
) m1 join (
select r.name as regionname , ina.name as indicatorname, sum(a.value) as 
a2Value
from Region as "r"
left join city_region as "cr" on r.region_id = cr.region_id
left join Office as "o" on cr.city_id = o.city_id
left join Assets as "a" on o.office_id = a.office_id
left join Indicators as "i" on a.indicator_id = i.indicator_id
left join IndicatorNames as "ina" on i.indicator_name_id =  
ina.indicator__name_id
where a.month between '01-02-2019' and '27-02-2019'
group by r.name, ina.name) m2 on m1.regionname = m2.regionname

我需要得到 4 行和 3 列，其中包括 region_name、indicator_name 和浮点值。但我只能得到带有值的表

0,0482248520710059
0,0565972222222222
0,0665680473372781
0,078125
0,705627705627706
0,974025974025974
1,01875
1,03550295857988
1,18343195266272
1,21527777777778
1,38888888888889
1,40625
15,1515151515152
17,3160173160173
21,875
25

但这是错误的。

标签： sql-servertsql

Answer 1

ON子句中的这个条件：

on m1.regionname = m2.regionname

将加入许多不相关的行。
您必须设置另一个条件，例如：

on m1.regionname = m2.regionname and m1.indicatorname = m2.indicatorname