sql left join 联合查询示例

select
                a.su_id,
                a.su_code,
                a.su_name,
                a.su_contact,
                a.su_type,
                a.so_id,
                a.wb_type,
b.fs_cretae_time,
b.fs_end_time,
                (select b.su_name from sys_user b where b.su_id=a.so_id) as so_name,------------------------------------------------------------------子查询
                a.su_order,
                (case when exists(select 1 from sys_user b where b.so_id = a.su_id)  then '1' else '0' end) as so_child,------------------------------------子查询
sum(b.fs_transaction_amount) as fs_transaction_amount,----------------------------------------------------------------------------------------聚合函数统计
sum(b.fs_user_id) as fs_user_id


from sys_user a left join fsms_fee_splitting b on a.su_name=b.fs_parent_id---------------------------------------------------------------------------left join 联合查询，已左边为主表
where fs_cretae_time between CONVERT(datetime,'2016/11/1 0:00:00') and CONVERT(datetime,'2016/11/30 0:00:00')-------------------------查询该时间段内数据
group by a.su_id,------------------------------------------------------------------------------------------------------------------------------------使用了sum(),count()等聚合函数，这里必须指定分组
                a.su_code,
                a.su_name,
                a.su_contact,
                a.su_type,
                a.so_id,
                a.wb_type,
b.fs_cretae_time, 
b.fs_end_time,
a.su_order


ORDER BY fs_transaction_amount DESC-------------------------------------------------------------------------------------------------------------------------降序排序