MySQL 复杂sql语句使用举例


复杂sql语句使用举例

正文

因为业务需要，写了一个很是复杂的sql查询语句，这里用到了很多sql技巧，以后可以反复看看，深入理解这些小工具的使用背景：

#lv3 搜 lv5
SELECT u.uid, u.utel, u.ustatus,
    jn6.jg_nickname as jglevel6_name, jn5.jg_nickname as jglevel5_name, jn4.jg_nickname as jglevel4_name, jn3.jg_nickname as jglevel3_name,
    la.money_total_history , lb.money_total_position, (la.money_total_history + lb.money_total_position) as total_fee,
    lb.money_total_position as chicang_fee,
    la.money_total_history as pingcang_fee,
    la.ploss_total_history as yinkui_fee,
    la.service_total_history, lb.service_total_position, (la.service_total_history + lb.service_total_position) as service_fee
FROM (
    SELECT wu.uid, wu.utel, wu.ustatus, wu.yid, wu.member_id, wu.tmember_id 
    FROM zzx_wp_userinfo as wu
    INNER JOIN (
        # 搜索获取最近层的机构id
        SELECT suja.id FROM zzx_jg_company as suja
        LEFT JOIN zzx_jg_company as sujb ON suja.pid = sujb.id
        WHERE sujb.jg_type = 5 AND sujb.jg_nickname like '%商%'
    ) sujc ON wu.yid = sujc.id
    INNER JOIN (
        # 过滤获取属于最近层的机构id
        SELECT sujd.id FROM zzx_jg_company as sujd WHERE sujd.pid = 471
    ) suje ON wu.member_id = suje.id
    WHERE wu.ustatus = 1
    LIMIT 0,50
) u
LEFT JOIN zzx_jg_company as jn6 ON u.yid = jn6.id
LEFT JOIN zzx_jg_company as jn5 ON ( SELECT pid FROM zzx_jg_company WHERE id = u.yid ) = jn5.id
LEFT JOIN zzx_jg_company as jn4 ON u.member_id = jn4.id
LEFT JOIN zzx_jg_company as jn3 ON ( SELECT pid FROM zzx_jg_company WHERE id = u.member_id ) = jn3.id
LEFT JOIN
(       
    SELECT a.uid, sum(a.money) as money_total_history, SUM(a.ploss) as ploss_total_history, SUM(a.service_fee) as service_total_history 
    FROM zzx_order_history as a 
    WHERE a.buytime > 0 GROUP BY a.uid
) la ON u.uid = la.uid
LEFT JOIN
(   
    SELECT a.uid, sum(a.money) as money_total_position, SUM(a.service_fee) as service_total_position 
    FROM zzx_order_position as a 
    WHERE a.buytime > 0 GROUP BY a.uid
) lb ON u.uid = lb.uid

where 条件中 NOT EXISTS用法：

SELECT ac.student_id, u.nick as student_name, ac.time_class as class_time, ac.teacher_id, ac.class_id 
    FROM abnormality_class as ac 
    LEFT JOIN abnormality_class_belong as acsb ON ac.student_id = acsb.student_id
    LEFT JOIN user as u ON ac.student_id = u.id
    WHERE acsb.kefu_type = 1 AND (ac.fail_type = 2 OR ac.fail_type = 18) AND ac.is_done = 0
        AND (NOT EXISTS (SELECT 1 FROM leave_error_notice as ten WHERE ac.class_id = ten.class_id))






参考资料

返回