如何选择一个表的同一列作为两个不同的列按日期分开两次

owfi6suc  于 2021-06-25  发布在  Mysql
关注(0)|答案(0)|浏览(165)

我有下面的mysql查询,但是执行大约需要5分钟,如何减少执行时间

select
    revenue_center_group.description as rc_group,
        revenue_center.description as revenue_center,
        count(ytd.members) as total_ytd_members,
        count(lytd.members) as total_lytd_members
    from revenue_center
    left join revenue_center_group on revenue_center_group.id = revenue_center.revenue_center_group_id
    left join (
        select membership.id as members,
            membership.rc_id as rc_id
    from membership
    where membership.status =1 
    and DATE(membership.join_date)>= (SELECT DATE_FORMAT((SELECT DATE_FORMAT(date(now()) - INTERVAL 1 MONTH, '%Y-%m-%d')) ,'%Y-01-01 00:00:00'))
    and DATE(membership.join_date)<= (SELECT DATE_FORMAT(LAST_DAY(date(now()) - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59'))ytd on revenue_center.id = ytd.rc_id 
    left join (
        select
            membership.id as members,
            membership.rc_id as rc_id
    from membership
    where membership.status =1 
    and DATE(membership.join_date)>= (SELECT DATE_FORMAT((SELECT DATE_FORMAT((SELECT DATE_FORMAT(date(now()) - INTERVAL 1 MONTH, '%Y-%m-%d'))
  - INTERVAL 1 YEAR, '%Y-%m-%d')) ,'%Y-01-01 00:00:00'))
and DATE(membership.join_date)<= (SELECT DATE_FORMAT((select DATE_FORMAT(LAST_DAY(date(now()) - INTERVAL 1 MONTH), '%Y-%m-%d')
- INTERVAL 1 YEAR),'%Y-%m-%d 23:59:59'))lytd on revenue_center.id = lytd.rc_id 
    group by revenue_center_group.description,revenue_center.description with rollup;

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题