web | Java

写出HQL 1)ORDER表,字段:Date,Order_id,User_id,amount(数据样例:2017-01-01,10029028,1000003251,33.57) 2)给出 2017年每个月的订单数、用户数、总成交金额

千锋教育

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;