常用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>

 （3）经常用到的返回值类型 

public List<Map<String,String>> selectAllApiAsset();

 <select id="selectAllApiAsset" resultType="java.util.Map">
        SELECT ka.service_name as "service_name",
              ka.service_id as "service_id"
        FROM kong_association ka
    </select>

（4）sum和 group by搭配使用

group by是需要包含除求和列的所有其他列的，比如这里select三列，sum其中一列，group by剩下两列。

public List<Map<String,Object>> queryVisitsRank(@Param(value="startDay")Date startDay,@Param(value="endDay")Date endDay,@Param(value="tableNameList")List tableNameList);

<select id="queryVisitsRank" resultType="java.util.Map">
        select service_name as "service_name" ,
        sum(day_visits) as visits ,
        service_id as "service_id"
        from (
        <foreach collection="tableNameList" item="item" open="(" close=")" separator="union all" >
            select *
            FROM ${item}
            where (${item}.date BETWEEN #{startDay}AND #{endDay})
        </foreach>) as table_end
        GROUP BY service_name,service_id
        order by visits desc
    </select>

 （5）when和case的使用场景

这种键值对的返回值，适用于做排行榜，热度排名等

public List<Map<String,Object>> queryAccRank(@Param(value="startDay")Date startDay,@Param(value="endDay")Date endDay,@Param(value="tableNameList")List tableNameList);

<select id="queryAccRank" resultType="java.util.Map">
        select
        service_name as "service_name" ,
        service_id as "service_id",
        case
        when sum(total)> 0 then sum(true_count)*100/sum(total)
        else 0
        end as acc
        from (
        <foreach collection="tableNameList" item="item" open="(" close=")" separator="union all" >
            select *
            FROM ${item}
            where (${item}.date BETWEEN #{startDay}AND #{endDay})
        </foreach>) as table_end
        GROUP BY service_name,service_id
        order by acc desc
    </select>

 （6）返回值是列表，但是在xml文件里，返回值定义的类型是列表里的元素类型。

public List<String> countAcc(@Param(value="apiName")String apiName, @Param(value="startDay")Date startDay,@Param(value="endDay")Date endDay,@Param(value="logTableNameList")List logTableNameList);

<select id="countAcc" resultType="java.lang.String">
        select table_end.response_status from (
        <foreach collection="logTableNameList" item="item" open="(" close=")" separator="union all" >
            select *
            FROM ${item}
            where (${item}.started_at BETWEEN #{startDay}AND #{endDay})
            AND ${item}.service_name = #{apiName}
        </foreach>
        )as table_end
    </select>

（7）只返回一条数据

出现计数列：

ApiVisitsDay queryNowDayApiVisits(@Param(value="service_name") String service_name,@Param(value="service_id")String service_id,@Param(value="startDay") Date startDay,@Param(value="endDay") Date endDay);

 <select id="queryNowDayApiVisits" resultMap="ApiVisitsDay">
       select #{service_name}::text as service_name,
              #{service_id}::text as service_id,
              #{startDay}::text as date,
               count(*) as day_visits from (select * from kong_log kl where kl.service_name = #{service_name} and kl.started_at BETWEEN #{startDay}AND #{endDay}) as t
    </select>

出现求和列：

 <select id="queryNowDayApiFlows" resultMap="ApiFlowDay">
       select service_id,
        service_name,
        #{startDay}::text as date,
        sum(CAST(response_size as INT)) as day_flow
      from kong_log kl where kl.service_name = #{service_name}and kl.started_at between #{startDay} and #{endDay}
      group by service_id,service_name,date
    </select>