Sql语句

1.IFNULL语句判断一个name为空，则返回username

SELECT  (CASE WHEN IFNULL(trim(name),'') = '' THEN username ELSE name END) AS name from ta_user where id=209;
//或者：
SELECT (IF(IFNULL(trim(name),'') = '' , username , name )) AS name from ta_user where id=209;

2.Mybatis获取多条数据，根据id获取设备列表

<select id="getBatchAdressById" resultType="string">
    select adress from ta_user where id in
    <foreach collection="list" open="(" close=")" separator="," item="item">#{item}</foreach>
</select>

3.获取生日

SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) from ta_user where id=#{id}

4.Mybatis获取前十条数据

<select id="getUsersByDepartment_id" resultType="com.jfsystem.manage.dwpc.model.PushUser">
    select id,name,height,department_id,weight,birthday,TIMESTAMPDIFF(YEAR, birthday, CURDATE()) as age,phone,sex FROM ta_user where department_id in
    <foreach collection="list" open="(" close=")" separator="," item="item">#{item}</foreach>
    ORDER BY RAND() LIMIT 10;
</select>

5.查询语句

SELECT
    ta_user.id,
    ta_user.username,
    ta_user.`password`,
    ta_user.num,
    ta_user.`name`,
    ta_user.height,
    ta_user.weight,
    ta_user.phone,
    ta_user.birthday,
    ta_user.sex,
    ta_user.department_alias,
    ta_user.title,
    ta_user.photo_path,
    ta_user.mac,
    ta_user.department_id,
    ta_user.authority,
    ta_user.rfid_code,
    ta_user.rfid_qr,
    ta_user.flag,
    ta_user.adress,
    ta_user.watch_code,
    ta_user.roleid,
    ta_user.role,
    ta_user.version 
FROM
    ta_user 
WHERE
    ta_user.watch_code LIKE '%0-15%' 
GROUP BY
    ta_user.id
HAVING
    ta_user.sex = 1 
ORDER BY
    ta_user.id ASC 
LIMIT 1,1;