select month('date'),count(order_id)
`订单数`,count(distinct user_id)
`用户数`,sum(amount)
`总成交金额` from order where year('date') = '2017' group by month('date')
3)给出2017年11月的新客数(指在11月才有第一笔订单) select count(distinct user_id)
`新客数` from ( select * from order where date_format('date','yyyy-MM') = '2017-11' ) t1 left join ( select * from order where date_format('date','yyyy-MM') < '2017-11' ) t2 on t1.user_id = t2.user_id where t2.user_id is null;