用户表

app表

用户记录表

现在要想查出用户点击的记录的列表，并且按照用户，时间排序，去掉重复的数据

select distinct a.* from (select n.name,u.phone,n.startmoney,n.endmoney,n.type from loan_record r
inner join loan_user u on u.id=r.uid
inner join loan_name n on n.id=r.appid
where r.uid in (select distinct uid from loan_record order by createdate desc) order by u.phone,r.createdate desc) as a
where a.name='微贷'

等价于：

select distinct a.* from (select r.uid,n.id,n.name,n.img,n.type,n.summary,n.count,n.star from loan_name n,loan_record r
where n.id=r.appid order by r.uid,r.createdate desc) as a;

 

 

 

如果不去掉重复的，直接这样就行了

select u.phone,n.name,n.startmoney,n.endmoney,n.type from loan_record r
inner join loan_user u on u.id=r.uid
inner join loan_name n on n.id=r.appid
order by r.createdate desc;

 

同时记录下之前写的代码，读者可忽略

select * from (select * from (
 select r.uid as uid,r.cid as cid,c.rank as rank,b.bookname as bookName,b.cover as cover,r.read_date as readDate,
 b.id as bid,b.majorcate as majorCate,b.is_over as isOver
 from record r
 inner join user u on u.id=r.uid and r.uid=#{record.uid}
 inner join chapter c on c.id=r.cid
 inner join book b on b.id=c.bid
 group by r.uid,r.cid
 order by c.rank)aa ORDER BY uid asc,rank desc)cc group by bid ORDER BY readDate desc