mysql统计用户的预约量 昨天 上周 总金额 总销量

思路 先统计所有用户 并使用left join 连接用户 昨天的销量(按用户id统计昨天销量)sql on 用户id=用户id ,其他的上周销量统计同理连接 返回最终每个用户的数据 并可以根据 销量排序 

 

 

select  a.*,IFNULL(b.last_day_num,0) as last_week_num ,IFNULL(c.last_day_num,0) as last_week_num ,

IFNULL(d.total_num,0) as order_num,IFNULL(d.total_price,0) as order_price from (

select merchants_id,merchants_name,merchants_city,merchants_country from tst_merchants

where is_delete='0'

<if test=' merchants_name != null and merchants_name != "" '>

and merchants_name like concat('%',#{merchants_name},'%')

</if>

<if test=' merchants_contact_mobile != null and merchants_contact_mobile != "" '>

and merchants_contact_mobile like concat('%',#{merchants_contact_mobile},'%')

</if>

<if test=' merchants_contact_name != null and merchants_contact_name != "" '>

and merchants_contact_name like concat('%',#{merchants_contact_name},'%')

</if>

<if test=' merchants_id != null and merchants_id != "" '>

and merchants_id = #{merchants_id}

</if>

) as a

left join 

(

select m.merchants_id as merchants_id,count(*) as last_week_num

from tst_member_order as m

inner join tst_house_config as n

on m.config_id = n.config_id

and n.is_delete = '0'

WHERE m.is_delete = '0' and

YEARWEEK(date_format(m.create_time,'%Y-%m-%d')) = YEARWEEK(now())-1 

group by m.merchants_id

)as b 

on a.merchants_id= b.merchants_id

left join 

(

select m.merchants_id as merchants_id,count(*) as last_day_num 

from tst_member_order as m

inner join tst_house_config as n

on m.config_id = n.config_id

and n.is_delete = '0'

WHERE m.is_delete = '0' 

and date(m.create_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)

group by m.merchants_id

)as c

on a.merchants_id= c.merchants_id

left join 

(

select m.merchants_id as merchants_id,count(*) as total_num,ifNull(sum(b.total_price),0) as total_price

from tst_member_order as m

inner join tst_house_config as n

on m.config_id = m.config_id

and n.is_delete = '0'

WHERE m.is_delete = '0' 

group by m.merchants_id

)as d

on a.merchants_id= d.merchants_id

<if test=' field != "" and order != ""  '>

order by #{field} #{order}

</if>