常用SQL语句整理

一、基础知识

左边是代码里定义的类属性字段名，右边是数据库里定义的表某个属性的字段名

<resultMap id="KongLog" type="com.aisino.loghttpserver.entity.KongLog">
        <result property="id" column="id"/>
        <result property="serviceid" column="service_id"/>
        <result property="servicename" column="service_name"/>
        <result property="consumerid" column="consumer_id"/>
        <result property="latencies" column="latencies"/>
        <result property="requesturl" column="request_url"/>
        <result property="clientip" column="client_ip"/>
    </resultMap>

 二、常见使用场景

1.多表查询

（1）如何获取多张表

List<String> tableNameList = apiVisitsMapper.getVisitsTableNameList(); //获取数据库中满足某个样式的全部表名

<select id="getVisitsTableNameList"  resultType="java.lang.String" >
  select table_name
   from information_schema."tables" t
   where table_name like '%api_visits_day%'
   order by table_name desc
</select>

(2)sql查询参数中，有链表。如何实现循环查找
List<ApiVisitsDay> list = apiVisitsMapper.queryPeriod(service_name,service_id,startDay,endDay,tableNameList);
对链表里的每一个item都进行遍历筛选，最后把所有结果合并在一个表里，select进行返回。

<select id="queryPeriod" resultMap="ApiVisitsDay">
        select * from (
        <foreach collection="tableNameList" item="item" open="(" close=")" separator="union all" >
            SELECT ${item}.service_name as service_name,
            ${item}.service_id as service_id,
            ${item}.day_visits as day_visits,
            ${item}.date as date
            FROM ${item}
            where (${item}.date BETWEEN #{startDay}AND #{endDay})
            AND (${item}.service_name = #{service_name} or ${item}.service_id = #{service_id})
        </foreach>
        ) as table_end
    </select>