两表全连接与聚合函数有关问题

两表全连接与聚合函数问题
select m.学号,SUM(欠费合计) from table1 as m where m.学号 not like '%人' group by m.学号 order by m.学号

select n.* from table2 as n where n.欠费金额 != 0 AND n.学号 is not null order by n.学号

table1与table2按学号是否相等如何实现全连接？？？
[解决办法]

select *
from 
(
select m.学号,SUM(欠费合计) as t 
from table1 as m 
where m.学号 not like '%人' 
group by m.学号 
)a
full join 
(
select n.* from table2 as n 
where n.欠费金额  != 0 AND n.学号 is not null 
)b
 on a.学号 = b.学号

[解决办法]

引用:

问题②：进一步优化：有没有方法在实现全连接后，按某两个字段（表1的欠费合计与表2的欠费金额）是否相等，过滤掉相等的，只显示不相等的

select *
from 
(
    select m.学号,SUM(欠费合计) as t 
    from table1 as m 
    where m.学号 not like '%人' 
    group by m.学号 
)a
full join 
(
    select n.* from table2 as n 
    where n.欠费金额  != 0 AND n.学号 is not null 
)b
 on a.学号 = b.学号
where isnull(a.欠费,0) &lt;&gt; isnull(b.欠费,0)

两表全连接与聚合函数有关问题

热点推荐