2021-05-01

手写HQL

请用sql写出所有用户中在今年10月份第一次购买商品的金额，表ordertable字段（购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid）

1）建表

create table ordertable(

   userid string,

   money int,
    
   paymenttime string,

   orderid string)

row format delimited fields terminated by '\t';

2)清楚最小时间值

-- 先清洗日期数据
select 
	userid,
	min(paymenttime)
from 
	ordertable
where
	data_format(paymenttime,"yyyy-MM") = "2017-10"
group by
	userid

3)join

select
	t1.userid,
	t1.min,
	t2.money,
	t2.orderid
from	
	(select 
		userid,
		min(paymenttime) min
	from 
		ordertable
	where
		data_format(paymenttime,"yyyy-MM") = "2017-10"
	group by
		userid) t1
join
	(select 
		userid,
    	paymenttime,
		money,
    	orderid
    from
     	ordertable) t2
on 
     t1.userid = t2.userid
     and
     t1.paymenttime=t2.paymenttime;