题目描述:sql语句查询每个用户最近一天登录的日子,用户的名字,以及用户用的设备的名字,并且查询结果按照user的name升序排序。
考点:group by 子句常见错误:
select 子句中只能存在以下三种元素:常数、聚合函数、group by子句指定列(聚合键)
所以下面这么写法错误:
select u.name as u_n, c.name as c_n, max(l.date) as d #列名c.name as c_n不能包含 from login as l inner join user as u on l.user_id=u.id inner join client as c on l.client_id=c.id group by u.name order by u.name asc
我的代码(多列名IN):
select u.name as u_n, c.name asc_n, l.date from login l join user u on l.user_id=u.id join client c on l.client_id=c.id where (l.user_id,l.date) in (select user_id,max(date) from login group by user_id) order by u_n