1.需求：

不同系统（pc，Android，ios）可以同时登录，同种系统（pc与pc，Android与Android，ios与ios）形成登录互斥。

2.多系统单点登录存在的问题：

1)怎么区分系统？

可以通过request中User-Agent获取设备信息，判断是否存在系统关键词区分系统；移动端也可以在请求头中添加指定字段确认是什么系统。

2)怎么保证单点？

需要保存已登录的系统的信息和登录时间才能知道当前系统是否已有设备登录

3)怎么形成互斥？

在新设备登录时需要能够获取到就设备的信息，把旧设备信息删除或替换，所有token不可变。

3.现有环境：

java jkd1.8，springboot2.0，mysql5.7，redis5.0

4.初步方案：

1）token生成：根据用户使用网站过程中不会改变的信息去生成用户唯一token，用作redis中的key，用户登录时获取登录设备，将登录设备和登录时间用作value保存到redis中。

伪代码：

function String createToken(String tel,String pasword){

return md5Encoding(tel+password);

}

function String createValue(HttpservlertRequest request){

return getDeviceType(request)+(new Date().toString);

}

function boolean saveToken(String token,String value){

jedis.set(token,value,"NX","EX",1800);

}

2）鉴权：使用拦截器拦截请求，判断有无token。有token则根据token查询redis中所对应的value；获取当前操作设备，判断设备是否已登录（是否在value中）,如果存在，判断是否已过期，若没有过期则鉴权通过。

伪代码：

String token = request.getParameter("token");

String value = jedis.get(token);

String deviceType = getDeviceType(request);

if(value.get(deviceType)){

if(value.get(deviceType).get("time") > new Date()){

       return true;

}

}

return false;

3）token过期：在保存token时将设备的过期时间一起保存，请求时判断设备登录是否已过期，若没有过期则请求成功，同时更新设备过期时间和redis中token过期时间。

伪代码：

if(value.get(deviceType).get("time") > new Date()){

value.get(deviceType).set("time") = new Date()+30m

jedis.set(token,value,"XX","EX",1800);

       return true;

}

4）退出登录：判断退出的设备是否存在登录状态，存在则删除设备，设备退出后如果已经没有设备登录，则在redis中删除token。

5.具体实现：

1)创建类保存已登录信息：

import java.util.Date;

/**
 * @author wxm
 * @date 2019/9/13.
 * 登录信息
 */
public class LoginInfo {

    /**
     * 用户id
     */
    private String userId;
    /**
     * 安卓设备信息
     */
    private String android;
    /**
     * 安卓设备最后活动时间
     */
    private Date androidTime;
    /**
     * iphone设备登录信息
     */
    private String iphone;
    /**
     * iphone设备最新活动时间
     */
    private Date iphoneTime;
    /**
     *  windows设备登录信息
     */
    private String windows;
    /**
     * windows设备最后活动时间
     */
    private Date windowsTime;
    /**
     * ipad设备登录信息
     */
    private String ipad;
    /**
     * ipad设备最后活动时间
     */
    private Date ipadTime;
    /**
     * 其它设备登录信息
     */
    private String other;
    /**
     * 其它设备最后活动时间
     */
    private Date otherTime;
    /**
     * 微信设备登录信息
     */
    private String wechat;
    /**
     * 微信设备登录最后活动时间
     */
    private Date wechatTime;

    public String getWechat() {
        return wechat;
    }

    public void setWechat(String wechat) {
        this.wechat = wechat;
    }

    public Date getWechatTime() {
        return wechatTime;
    }

    public void setWechatTime(Date wechatTime) {
        this.wechatTime = wechatTime;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getAndroid() {
        return android;
    }

    public void setAndroid(String android) {
        this.android = android;
    }

    public Date getAndroidTime() {
        return androidTime;
    }

    public void setAndroidTime(Date androidTime) {
        this.androidTime = androidTime;
    }

    public String getIphone() {
        return iphone;
    }

    public void setIphone(String iphone) {
        this.iphone = iphone;
    }

    public Date getIphoneTime() {
        return iphoneTime;
    }

    public void setIphoneTime(Date iphoneTime) {
        this.iphoneTime = iphoneTime;
    }

    public String getWindows() {
        return windows;
    }

    public void setWindows(String windows) {
        this.windows = windows;
    }

    public Date getWindowsTime() {
        return windowsTime;
    }

    public void setWindowsTime(Date windowsTime) {
        this.windowsTime = windowsTime;
    }

    public String getIpad() {
        return ipad;
    }

    public void setIpad(String ipad) {
        this.ipad = ipad;
    }

    public Date getIpadTime() {
        return ipadTime;
    }

    public void setIpadTime(Date ipadTime) {
        this.ipadTime = ipadTime;
    }

    public String getOther() {
        return other;
    }

    public void setOther(String other) {
        this.other = other;
    }

    public Date getOtherTime() {
        return otherTime;
    }

    public void setOtherTime(Date otherTime) {
        this.otherTime = otherTime;
    }

    @Override
    public String toString() {
        return "LoginInfo{" +
                "userId='" + userId + '\'' +
                ", android='" + android + '\'' +
                ", androidTime=" + androidTime +
                ", iphone='" + iphone + '\'' +
                ", iphoneTime=" + iphoneTime +
                ", windows='" + windows + '\'' +
                ", windowsTime=" + windowsTime +
                ", ipad='" + ipad + '\'' +
                ", ipadTime=" + ipadTime +
                ", other='" + other + '\'' +
                ", otherTime=" + otherTime +
                ", wechat='" + wechat + '\'' +
                ", wechatTime=" + wechatTime +
                '}';
    }
}

 2)用户登录:

    // token过期时间(s)
    public static final int LOGIN_TIMEOUT_SECOND = 1800;
    // Android
    public static final String DEVICE_TYPE_ANDROID = "Android";
    // 微信
    public static final String DEVICE_TYPE_MICRO_MESSENGER = "MicroMessenger";
    // iPhone
    public static final String DEVICE_TYPE_IPHONE = "iPhone";
    // iPad
    public static final String DEVICE_TYPE_IPAD = "iPad";
    // Android
    public static final String DEVICE_TYPE_WINDOWS = "Windows";
    // unknown
    public static final String DEVICE_TYPE_UNKNOWN = "unknown";
    // Linux
    public static final String DEVICE_TYPE_LINUX = "Linux";
    /**
     * 登录
     */
    public String login(String account, String password, HttpServletRequest request) {
        // 获取设备类型
        String devType = getDevType(request.getHeader("User-Agent"));
        User preLogin = userMapper.getUserByAccount(account);
        // 序列化：protostuff 初始化
        RuntimeSchema<LoginInfo> schaema = RuntimeSchema.createFrom(LoginInfo.class);
        LoginInfo loginInfo = schaema.newMessage();
        Jedis jedis = jedisPool.getResource();
        // 获取token
        String tokenCode =  MD5Utils.MD5(preLogin.getTel() + preLogin.getPassword());
        // 获取redis中数据
        byte[] loginInfor = jedis.get(tokenCode.getBytes());
        if(loginInfor == null || loginInfor.length == 0){
            // 第一个设备登录时将userId存入
            loginInfo.setUserId(preLogin.getId());
        }else{
            // 反序列化登录信息到loginInfo
            ProtostuffIOUtil.mergeFrom(loginInfor, loginInfo, schaema);
        }
        // 保存客户端信息
        switch (devType){
            case DEVICE_TYPE_ANDROID:
                loginInfo.setAndroid(request.getHeader("User-Agent"));
                loginInfo.setAndroidTime(new Date());
                break;
            case DEVICE_TYPE_IPAD:
                loginInfo.setIpad(request.getHeader("User-Agent"));
                loginInfo.setIpadTime(new Date());
                break;
            case DEVICE_TYPE_IPHONE:
                loginInfo.setIphone(request.getHeader("User-Agent"));
                loginInfo.setIphoneTime(new Date());
                break;
            case DEVICE_TYPE_LINUX:
                loginInfo.setAndroid(request.getHeader("User-Agent"));
                loginInfo.setAndroidTime(new Date());
                break;
            case DEVICE_TYPE_MICRO_MESSENGER:
                loginInfo.setWechat(request.getHeader("User-Agent"));
                loginInfo.setWechatTime(new Date());
                break;
            case DEVICE_TYPE_WINDOWS:
                loginInfo.setWindows(request.getHeader("User-Agent"));
                loginInfo.setWindowsTime(new Date());
                break;
            default:
                loginInfo.setOther(request.getHeader("User-Agent"));
                loginInfo.setOtherTime(new Date());
        }
        try {
            // 序列化对象
            byte[]  bytes = ProtostuffIOUtil.toByteArray(loginInfo, schaema, LinkedBuffer.allocate(LinkedBuffer.DEFAULT_BUFFER_SIZE));
            // 存入redis
            jedis.del(tokenCode.getBytes());
            jedis.set(tokenCode.getBytes(), bytes, "NX".getBytes(), "EX".getBytes(), UserBll.LOGIN_TIMEOUT_SECOND);
        }finally {
            jedis.close();
        }
        return tokenCode;
    }

    /**
     *  根据userAgent获取设备类型
     */
    public static String getDevType(String userAgent){
        String dev_type = "";
        if (userAgent.contains(DEVICE_TYPE_MICRO_MESSENGER)) {
            dev_type = DEVICE_TYPE_MICRO_MESSENGER;
        }else{
            if (userAgent.contains(DEVICE_TYPE_ANDROID) || userAgent.contains(DEVICE_TYPE_LINUX)) {
                dev_type = DEVICE_TYPE_ANDROID;
            }else if (userAgent.contains(DEVICE_TYPE_IPHONE)) {
                dev_type = DEVICE_TYPE_IPHONE;
            }else if (userAgent.contains(DEVICE_TYPE_IPAD)) {
                dev_type = DEVICE_TYPE_IPAD;
            }else if(userAgent.contains(DEVICE_TYPE_WINDOWS)){
                dev_type = DEVICE_TYPE_WINDOWS;
            }else{
                dev_type = DEVICE_TYPE_UNKNOWN;
            }
        }
        return dev_type;
    }

 3)拦截鉴权：

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.chinacarbon.api.model.ReturnModel;
import com.chinacarbon.bll.UserService;
import com.chinacarbon.utils.DateUtil;
import com.dyuproject.protostuff.LinkedBuffer;
import com.dyuproject.protostuff.ProtostuffIOUtil;
import com.dyuproject.protostuff.runtime.RuntimeSchema;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.HandlerInterceptor;
import org.springframework.web.servlet.ModelAndView;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.PrintWriter;
import java.util.Date;

/**
 * 2019-07-04
 * redis登录验证
 * @author wxm
 */
@Component
public class LoginInterceptor implements HandlerInterceptor {

    private Logger logger = LoggerFactory.getLogger(LoginInterceptor.class);

    @Autowired
    private JedisPool jedisPool;

    @Override
    public void postHandle(HttpServletRequest request,
                           HttpServletResponse response, Object handler,
                           ModelAndView modelAndView) throws Exception {

    }

    @Override
    public void afterCompletion(HttpServletRequest request,
                                HttpServletResponse response, Object handler, Exception ex)
            throws Exception {

    }

    @Override
    public boolean preHandle(HttpServletRequest request,
                             HttpServletResponse response, Object handler) throws Exception {
        // 获取token
        String token = request.getParameter("token");
        // 缺少权限参数
        if(StringUtils.isEmpty(token)){
            response.sendRedirect("/user/login");
            return false;
        }else {
            // 验证权限
            Jedis jedis = jedisPool.getResource();
            try {
                String devType = UserService.getDevType(request.getHeader("User-Agent"));
                byte[] loginInfor = jedis.get(token.getBytes());
                if(loginInfor == null || loginInfor.length == 0){
                    // 权限不足
                    response.setCharacterEncoding("utf-8");
                    response.setContentType("application/json; charset=utf-8");
                    PrintWriter writer = response.getWriter();
                    writer.write(JSON.toJSONString(new ReturnModel(-1011, null,"登录已过期！")));
                    return false;
                }else{
                    // 序列化：protostuff 初始化
                    RuntimeSchema<LoginInfo> schaema = RuntimeSchema.createFrom(LoginInfo.class);
                    LoginInfo loginInfo = schaema.newMessage();
                    // 反序列化登录信息到loginMap
                    ProtostuffIOUtil.mergeFrom(loginInfor, loginInfo, schaema);
                    // 判断当前种类设备是否已登录
                    switch (devType){
                        case UserService.DEVICE_TYPE_ANDROID:
                            if(loginInfo.getAndroid() != null){
                                Long secound = DateUtil.SecondsBetween(loginInfo.getAndroidTime(),new Date());
                                if(secound < UserService.LOGIN_TIMEOUT_SECOND) {
                                    loginInfo.setAndroidTime(new Date());
                                    byte[]  bytes = ProtostuffIOUtil.toByteArray(loginInfo, schaema, LinkedBuffer.allocate(LinkedBuffer.DEFAULT_BUFFER_SIZE));
                                    jedis.set(token.getBytes(), bytes, "XX".getBytes(), "EX".getBytes(), UserService.LOGIN_TIMEOUT_SECOND);
                                    return true;
                                }
                            }
                            break;
                        case UserService.DEVICE_TYPE_IPAD:
                            if(loginInfo.getIpad() != null){
                                Long secound = DateUtil.SecondsBetween(loginInfo.getIpadTime(),new Date());
                                if(secound < UserService.LOGIN_TIMEOUT_SECOND) {
                                    loginInfo.setIpadTime(new Date());
                                    byte[]  bytes = ProtostuffIOUtil.toByteArray(loginInfo, schaema, LinkedBuffer.allocate(LinkedBuffer.DEFAULT_BUFFER_SIZE));
                                    jedis.set(token.getBytes(), bytes, "XX".getBytes(), "EX".getBytes(), UserService.LOGIN_TIMEOUT_SECOND);
                                    return true;
                                }
                            }
                            break;
                        case UserService.DEVICE_TYPE_IPHONE:
                            if(loginInfo.getIphone() != null){
                                Long secound = DateUtil.SecondsBetween(loginInfo.getIphoneTime(),new Date());
                                if(secound < UserService.LOGIN_TIMEOUT_SECOND) {
                                    loginInfo.setIphoneTime(new Date());
                                    byte[]  bytes = ProtostuffIOUtil.toByteArray(loginInfo, schaema, LinkedBuffer.allocate(LinkedBuffer.DEFAULT_BUFFER_SIZE));
                                    jedis.set(token.getBytes(), bytes, "XX".getBytes(), "EX".getBytes(), UserService.LOGIN_TIMEOUT_SECOND);
                                    return true;
                                }
                            }
                            break;
                        case UserService.DEVICE_TYPE_LINUX:
                            if(loginInfo.getAndroid() != null){
                                Long secound = DateUtil.SecondsBetween(loginInfo.getAndroidTime(),new Date());
                                if(secound < UserService.LOGIN_TIMEOUT_SECOND) {
                                    loginInfo.setAndroidTime(new Date());
                                    byte[]  bytes = ProtostuffIOUtil.toByteArray(loginInfo, schaema, LinkedBuffer.allocate(LinkedBuffer.DEFAULT_BUFFER_SIZE));
                                    jedis.set(token.getBytes(), bytes, "XX".getBytes(), "EX".getBytes(), UserService.LOGIN_TIMEOUT_SECOND);
                                    return true;
                                }
                            }
                            break;
                        case UserService.DEVICE_TYPE_MICRO_MESSENGER:
                            if(loginInfo.getWechat() != null){
                                Long secound = DateUtil.SecondsBetween(loginInfo.getWechatTime(),new Date());
                                if(secound < UserService.LOGIN_TIMEOUT_SECOND) {
                                    loginInfo.setWechatTime(new Date());
                                    byte[]  bytes = ProtostuffIOUtil.toByteArray(loginInfo, schaema, LinkedBuffer.allocate(LinkedBuffer.DEFAULT_BUFFER_SIZE));
                                    jedis.set(token.getBytes(), bytes, "XX".getBytes(), "EX".getBytes(), UserService.LOGIN_TIMEOUT_SECOND);
                                    return true;
                                }
                            }
                            break;
                        case UserService.DEVICE_TYPE_WINDOWS:
                            if(loginInfo.getWindows() != null){
                                Long secound = DateUtil.SecondsBetween(loginInfo.getWindowsTime(),new Date());
                                if(secound < UserService.LOGIN_TIMEOUT_SECOND) {
                                    loginInfo.setWindowsTime(new Date());
                                    byte[]  bytes = ProtostuffIOUtil.toByteArray(loginInfo, schaema, LinkedBuffer.allocate(LinkedBuffer.DEFAULT_BUFFER_SIZE));
                                    jedis.set(token.getBytes(), bytes, "XX".getBytes(), "EX".getBytes(), UserService.LOGIN_TIMEOUT_SECOND);
                                    return true;
                                }
                            }
                            break;
                        case UserService.DEVICE_TYPE_UNKNOWN:
                            if(loginInfo.getOther() != null){
                                Long secound = DateUtil.SecondsBetween(loginInfo.getOtherTime(),new Date());
                                if(secound < UserService.LOGIN_TIMEOUT_SECOND) {
                                    loginInfo.setOtherTime(new Date());
                                    byte[]  bytes = ProtostuffIOUtil.toByteArray(loginInfo, schaema, LinkedBuffer.allocate(LinkedBuffer.DEFAULT_BUFFER_SIZE));
                                    jedis.set(token.getBytes(), bytes, "XX".getBytes(), "EX".getBytes(), UserService.LOGIN_TIMEOUT_SECOND);
                                    return true;
                                }
                            }
                            break;
                        default:
                            response.setCharacterEncoding("utf-8");
                            response.setContentType("application/json; charset=utf-8");
                            PrintWriter writer = response.getWriter();
                            writer.write(JSON.toJSONString(new ReturnModel(-1011, null,"未知设备！")));
                            return false;
                    }
                }
            }finally {
                jedis.close();
            }
        }
        // 权限不足
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json; charset=utf-8");
        PrintWriter writer = response.getWriter();
        writer.write(JSON.toJSONString(new ReturnModel(-1011, null,"登录已过期！")));
        return false;
    }
}

 4）退出登录

/**
     * 退出登录
     */
    public void loginOut(String token,HttpServletRequest request) {
        // 1.获取登录设备信息
        Jedis jedis = jedisPool.getResource();
        byte[] loginInfor = jedis.get(token.getBytes());
        try {
            if (loginInfor == null || loginInfor.length == 0) {
                logger.warn("一个无效token调用了退出接口！设备信息：" + request.getHeader("User-Agent"));
            } else {
                // 序列化：protostuff 初始化
                RuntimeSchema<LoginInfo> schaema = RuntimeSchema.createFrom(LoginInfo.class);
                LoginInfo loginInfo = schaema.newMessage();
                // 反序列化登录信息到loginInfo
                ProtostuffIOUtil.mergeFrom(loginInfor, loginInfo, schaema);
                // 获取设备类型
                String devType = getDevType(request.getHeader("User-Agent"));
                // 退出设备
                switch (devType) {
                    case DEVICE_TYPE_ANDROID:
                        loginInfo.setAndroid(null);
                        loginInfo.setAndroidTime(null);
                        break;
                    case DEVICE_TYPE_IPAD:
                        loginInfo.setIpad(null);
                        loginInfo.setIpadTime(null);
                        break;
                    case DEVICE_TYPE_IPHONE:
                        loginInfo.setIphone(null);
                        loginInfo.setIphoneTime(null);
                        break;
                    case DEVICE_TYPE_LINUX:
                        loginInfo.setAndroid(null);
                        loginInfo.setAndroidTime(null);
                        break;
                    case DEVICE_TYPE_MICRO_MESSENGER:
                        loginInfo.setWechat(null);
                        loginInfo.setWechatTime(null);
                        break;
                    case DEVICE_TYPE_WINDOWS:
                        loginInfo.setWindows(null);
                        loginInfo.setWindowsTime(null);
                        break;
                    case DEVICE_TYPE_UNKNOWN:
                        loginInfo.setOther(null);
                        loginInfo.setOtherTime(null);
                        break;
                    default:
                        logger.warn("一个未登录的设备获取了token并执行了退出登录接口！设备信息："
                                + request.getHeader("User-Agent"));
                }
                if (loginInfoIsNull(loginInfo)) {
                    // 全部设备已退出登录则删除token
                    jedis.del(token.getBytes());
                } else {
                    // 还有设备未退出登录则更新token
                    // 序列化对象
                    byte[] bytes = ProtostuffIOUtil.toByteArray(loginInfo, schaema, LinkedBuffer.allocate(LinkedBuffer.DEFAULT_BUFFER_SIZE));
                    // 存入redis
                    jedis.del(token.getBytes());
                    jedis.set(token.getBytes(), bytes, "NX".getBytes(), "EX".getBytes(), LOGIN_TIMEOUT_SECOND);
                }
            }
        }finally {
            jedis.close();
        }
    }

    private boolean loginInfoIsNull(LoginInfo loginInfo) {
        if(loginInfo.getAndroid() != null) {
            return false;
        }
        if(loginInfo.getIpad() != null) {
            return false;
        }
        if(loginInfo.getIphone() != null) {
            return false;
        }
        if(loginInfo.getOther() != null) {
            return false;
        }
        if(loginInfo.getWindows() != null) {
            return false;
        }
        if(loginInfo.getWechat() != null) {
            return false;
        }
        return true;
    }

6.使用的jar包：

1） protostuff 序列化和反序列化工具，性能和内存占用都优于jdk自带序列化工具。

<dependency>

   <groupId>com.dyuproject.protostuff</groupId>

   <artifactId>protostuff-core</artifactId>

   <version>1.0.8</version>

</dependency>

<dependency>

   <groupId>com.dyuproject.protostuff</groupId>

   <artifactId>protostuff-runtime</artifactId>

   <version>1.0.8</version>

</dependency>

 

 

题目描述

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.

有一个登录(login)记录表，简况如下:





第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网

。。。

第4行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网


还有一个用户(user)表，简况如下:




还有一个客户端(client)表，简况如下:



请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序，上面的例子查询结果如下:



查询结果表明:

fh最近的登录日期在2020-10-13，而且是使用pc登录的

wangchao最近的登录日期也是2020-10-13，而且是使用ios登录的

答案1：

select user.name as u_n, client.name as c_n,
login.date
from login 
join user on login.user_id=user.id
join client on login.client_id=client.id
where (login.user_id,login.date) in
(select user_id,max(date) from login group by login.user_id )
order by user.name;

答案2：

select u.name,c.name,l1.date
from login l1,user u,client c
where l1.date=(select max(l2.date) from login l2 where l1.user_id=l2.user_id)
and l1.user_id=u.id
and l1.client_id=c.id
order by u.name

 

SQL练习67：牛客每个人最近的登录日期2

题目链接：牛客网
题目描述

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.

有一个登录(login)记录表，简况如下:



第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网

。。。

第4行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
还有一个用户(user)表，简况如下:



还有一个客户端(client)表，简况如下:



请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序，上面的例子查询结果如下:



查询结果表明:

fh最近的登录日期在2020-10-13，而且是使用pc登录的

wangchao最近的登录日期也是2020-10-13，而且是使用ios登录的


解法

连接login、user、date三个表，通过子查询的方式获得MAX（date）最近的日期，再添加题中条件即可，最后按照name升序排列。
SELECT u.name, c.name, l.date
FROM login l, user u, client c
WHERE l.date = (SELECT MAX(date)
               FROM login l1
               WHERE l.user_id = l1.user_id)
AND l.user_id = u.id
AND l.client_id = c.id
ORDER BY u.name