<selectid="queryPageList"resultType="com.netintech.api.kuap.uap.domain.ext.UapApplicationAuditList">
SELECT ad.*,
app.app_name AS appName,
p.apply_dept AS applyDept,
app.app_type AS appType
FROM uap_application_audit ad,
uap_app_release r,
uap_project_application app,
uap_project p
WHERE
ad.app_release_id = r.id
AND r.app_pk = app.id
AND app.project_id = p.id
<iftest="auditResult != null">
AND ad.audit_result = #{auditResult}
</if>
AND app.app_name LIKE concat('%', #{appName}, '%')
AND p.apply_dept LIKE concat('%', #{applyDept}, '%')
</select>
<selectid="selectLogininforList"parameterType="SysLogininfor"resultMap="SysLogininforResult">
select info_id, user_name, ipaddr, login_location, browser, os, status, msg, login_time from sys_logininfor
<where><iftest="ipaddr != null and ipaddr != ''">
AND ipaddr like concat('%', #{ipaddr}, '%')
</if><iftest="status != null and status != ''">
AND status = #{status}
</if><iftest="userName != null and userName != ''">
AND user_name like concat('%', #{userName}, '%')
</if><iftest="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
and date_format(login_time,'%y%m%d') >= date_format(#{beginTime},'%y%m%d')
</if><iftest="endTime != null and endTime != ''"><!-- 结束时间检索 -->
and date_format(login_time,'%y%m%d') <= date_format(#{endTime},'%y%m%d')
</if></where>
order by info_id desc
</select>
2、多表排序
<selectid="deptApplicationRanking"resultType="com.netintech.vo.RankVo">
SELECT d.total AS count,
d.dept_id AS deptId,
d.dept_name AS deptName
FROM (
SELECT SUM(c.total) AS total,
c.dept_id,
c.dept_name
FROM (
SELECT COUNT(1) AS total,
u.app_department AS dept_id,
u.app_department_name AS dept_name
FROM uap_market_app u
GROUP BY u.app_department,
u.app_department_name
UNION ALL
SELECT COUNT(1) AS total,
p.dept_id AS dept_id,
p.dept_name AS dept_name
FROM uap_project_application p
GROUP BY p.dept_id,
p.dept_name
) c
GROUP BY c.dept_id,
c.dept_name
) d
ORDER BY d.total DESC
LIMIT 10;
</select>
3、表内字段相加排序
<selectid="starAppListByShareApplication"resultType="com.netintech.vo.RankVo">
SELECT r.appname AS deptName, r.appid AS deptId, m.`download` + m.`collection` + m.`to_view` AS COUNT
FROM uap_supermarket_app m,
(SELECT u.`id` AS appid, u.`app_name` AS appname
FROM uap_shared_application u) r
WHERE m.`app_id` = r.appid
ORDER BY COUNT DESC
limit 3
</select>