mysql - 如何组合这些表？

Question

我的目标是有一个 5 x 5 的表格，如下所示：

状态	大多数RE	大多数NRE	最小RE	最小NRE
西澳	4499698	空白的	空白的	空白的
德克萨斯州	空白的	91590135	空白的	空白的
直流	空白的	空白的	4780	空白的
直流	空白的	空白的	空白的	438130

我分别找到了查询：

select re.state, max(TotalrenewableE) as Most_RE
from #temp_RE as re
Inner join(
select max(TotalRenewableE) TotalRE
from #temp_RE)MaxTRE
On MaxTRE.TotalRE = RE.TotalRenewableE
Group by re.state 


select nre.state, max(TotalNonrenewableE) as Most_NRE
from #temp_NonRE as nre
Inner join(
select max(totalnonrenewablee) TotalNRE
from #temp_NonRE)MaxTNRE
On MaxTNRE.TotalNRE = NRE.TotalNonrenewableE
Group by nre.state



select re.state, min(TotalrenewableE) as Least_RE
from #temp_RE as re
Inner join(
select min(TotalRenewableE) TotalRE
from #temp_RE)MinTRE
On MinTRE.TotalRE = RE.TotalRenewableE
Group by re.state 



select nre.state, min(TotalNonrenewableE) as Least_NRE
from #temp_NonRE as nre
Inner join(
select min(totalnonrenewablee) TotalNRE
from #temp_NonRE)MinTNRE
On MinTNRE.TotalNRE = NRE.TotalNonrenewableE
Group by nre.state

我努力了：

select nre.state, 
min(TotalNonrenewableE) as Least_NRE,
min(TotalRenewableE) as Least_RE,
max(TotalNonrenewableE) as Most_NRE, 
max(TotalRenewableE) as Most_RE,
from #temp_NonRE as nre, #temp_RE as re
Inner Join(
select min(totalnonrenewablee) TotalNRE
from #temp_NonRE)MinTNRE
On MinTNRE.TotalNRE = #temp_NonRE.TotalNonrenewableE
Inner Join(
select min(TotalRenewableE) TotalRE
from #temp_RE)MinTRE
On MinTRE.TotalRE = RE.TotalRenewableE
Inner Join(
select max(totalnonrenewablee) TotalNRE
from #temp_NonRE)MaxTNRE
On MaxTNRE.TotalNRE = #temp_NonRE.TotalNonrenewableE
Inner Join(
select max(TotalRenewableE) TotalRE
from #temp_RE)MaxTRE
On MaxTRE.TotalRE = RE.TotalRenewableE
Where nre.State = re.State
Group by nre.state

但它给了我这个错误：

无法绑定多部分标识符“#temp_NonRE.TotalNonrenewableE”。

我只是想不出如何将它们组合起来制作上面的表格。

感谢您的辛勤工作

标签： mysqlsqljoin

Answer 1

再次联合所有和分组

select state, max(Most_RE) Most_RE, max(Most_NRE) Most_NRE, max(LeastRE) LeastRE, max(LeastNRE) LeastNRE  
from (
    select re.state, max(TotalrenewableE) as Most_RE, null Most_NRE, null LeastRE, null LeastNRE
    from #temp_RE as re
    Inner join(
    select max(TotalRenewableE) TotalRE
    from #temp_RE)MaxTRE
    On MaxTRE.TotalRE = RE.TotalRenewableE
    Group by re.state 
    
    union all
    
    select nre.state, null, max(TotalNonrenewableE) as Most_NRE, null, null
    from #temp_NonRE as nre
    Inner join(
    select max(totalnonrenewablee) TotalNRE
    from #temp_NonRE)MaxTNRE
    On MaxTNRE.TotalNRE = NRE.TotalNonrenewableE
    Group by nre.state
    
    ...
)
group by state