拉取参加活动的家长手机号

####主题:拉取参加活动的家长手机号
####执行时间xxx,shape:(xxx,xxx),新脱敏库
####日期:2019-03-25
####需求方:潇竹
####输出方式:excel
####created by:汪国强
####update by:
####checked by:王娟
SELECT
        a.phone 手机号
FROM
(
SELECT
        max(tcp.pay_date) 新签时间,
        s.student_id 学生id,
        (tc.sum-666)*10 m1,  ####合同登记总金额
        sum(tcp.sum)/100 m2, ####合同到账金额
        u.phone
FROM
        view_tms_contract_payment tcp
LEFT JOIN
        view_tms_contract tc on tc.contract_id = tcp.contract_id
LEFT JOIN
        view_student s on s.student_intention_id = tc.student_intention_id
LEFT JOIN
        parental_student_rel ps on ps.student_id = s.student_id
LEFT JOIN
        view_user_info u on u.user_id = s.student_id
WHERE
        tcp.pay_status in (2,4) and tc.`status` not in (7,8) and tc.new_sign = 1 and u.account_type = 1 and tc.contract_id like 'x%'
GROUP BY
        tc.contract_id
HAVING
        m1 <= m2
)a
WHERE
        a.新签时间 >= '2019-03-12' and a.新签时间 < '2019-03-25'
文档更新时间: 2019-03-29 11:14   作者:admin