SQL语句编写

1、条件查询

 <select id="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

        <if test="auditResult != null">
            AND ad.audit_result = #{auditResult}
        </if>

        AND app.app_name LIKE concat('%', #{appName}, '%')
        AND p.apply_dept LIKE concat('%', #{applyDept}, '%')
    </select>

<select id="selectLogininforList" parameterType="SysLogininfor" resultMap="SysLogininforResult">
		select info_id, user_name, ipaddr, login_location, browser, os, status, msg, login_time from sys_logininfor
		<where>
			<if test="ipaddr != null and ipaddr != ''">
				AND ipaddr like concat('%', #{ipaddr}, '%')
			</if>
			<if test="status != null and status != ''">
				AND status = #{status}
			</if>
			<if test="userName != null and userName != ''">
				AND user_name like concat('%', #{userName}, '%')
			</if>
			<if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
				and date_format(login_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')
			</if>
			<if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
				and date_format(login_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
			</if>
		</where>
		order by info_id desc
	</select>

2、多表排序

<select id="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、表内字段相加排序

 <select id="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>

4、批量删除