关闭

sql left join 联合查询示例

标签: sqlselect
143人阅读 评论(0) 收藏 举报
分类:
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-------------------------------------------------------------------------------------------------------------------------降序排序
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:161715次
    • 积分:3045
    • 等级:
    • 排名:第11463名
    • 原创:181篇
    • 转载:44篇
    • 译文:1篇
    • 评论:15条
    最新评论