MySQL-笔试题一

一、一个电商客户消费记录表data_outer的表字段为yearmonth年月、user_id用户ID、price月消费（若该月没有消费则不存在）,一共记录201910-202002五个月的消费。
该表的一条记录为201910,29025780,41.68表示用户ID为29025780的用户在2019年10月份消费了41.68元
按照要求写出相应的SQL语句
1.找出在前三个月消费都在51-100元，而后两个没有消费的用户ID

select a.user_id from 
(select user_id from data_outer 
where yearmonth='201910' and price between 51 and 100)a,
(select user_id from data_outer 
where yearmonth='201912' and price between 51 and 100)b, 
(select user_id from data_outer
 where yearmonth='201911' and price between 51 and 100)c,
(select user_id from data_outer 
where yearmonth not in('202002','202001'))d 
where a.user_id=b.user_id and b.user_id=c.user_id and c.user_id=d.user_id;

2.找出五个月以来用户ID的后四位数字满足AABB或ABAB或AAAA的用户ID，A、B表示0-9其中的数字

select * from(select distinct if(((a=b) and (c=d