9. MySQL语法 项目实战汇总

1、查找结果中自动加序号列

#MySQL查询结果中自动加序号列
SELECT 
    (@rowNO:=@rowNo + 1) AS rowno,
    a.id,
    a.realname,
    a.nickname,
    a.mobile
FROM
    (SELECT 
        *
    FROM
        usersinfo u
    WHERE
        u.createdate = '2017-05-23') a,
    (SELECT @rowNO:=0) b;

2、从结果集中查找自己的排名信息

select c.rowno, c.id from (SELECT 
    (@rowNO:=@rowNo + 1) AS rowno,
    a.id,
    a.realname,
    a.nickname,
    a.mobile
FROM
    (SELECT 
        *
    FROM
        usersinfo u
    WHERE
        u.createdate = '2017-05-23') a,
    (SELECT @rowNO:=0) b) c where c.id = 2;

3、比较两个日期大小，条件中应该根据哪个日期来统计信息

　　一般出现这种情况判断，我首先想到的是case when 语法，这种情况就是把业务逻辑判断交给了SQL语法来做了，但是出现数据量特别大的时候，就会出现慢查询情况。

　　针对这种情况，可以采用并集情况，来判断究竟该取那个日期来统计，详例如下：

　　

    /**
     * 查询所有的省或地市团队的总步数、按照省或地市团队进行分组
     *
     * @param activityid 活动id
     * @return
     */
    @Cacheable(value = "Activity.findAllProvincialOrCityUserStep")
    public List<ExclusiveProvincialAndCityListDTO> findAllProvincialOrCityUserStep(Long activityid, String type) {
        try {
//            StringBuffer buffer = new StringBuffer("select count(distinct ajd.userid) as sumuser," +
//                    "       sum((case" +
//                    "         when date_format(a.starttime, '%Y-%m-%d') > ajd.createdate then" +
//                    "          (select ifnull(sum(todaysteps), 0)" +
//                    "             from Steprecordinfo s" +
//                    "            where s.userid = ajd.userid and s.moveoractivity = 0" +
//                    "              and date_format(s.stepdate, '%Y%m%d') >= date_format(a.starttime, '%Y%m%d')" +
//                    "              and date_format(s.stepdate, '%Y%m%d') <= date_format(a.endtime, '%Y%m%d'))" +
//                    "         else" +
//                    "          (select ifnull(sum(todaysteps), 0)" +
//                    "             from Steprecordinfo s" +
//                    "            where s.userid = ajd.userid and s.moveoractivity = 0" +
//                    "              and date_format(s.stepdate, '%Y%m%d') >= date_format(ajd.createdate, '%Y%m%d')" +
//                    "              and date_format(s.stepdate, '%Y%m%d') <= date_format(a.endtime, '%Y%m%d'))" +
//                    "       end)) as sumstep,p.id as code,p.name as name" +
//                    "  from Activityjoindetail ajd, Activityinfo a, Pccinfo p");
            StringBuffer buffer = new StringBuffer("select count(distinct ajd.userid) as sumuser," +
                    "       ifnull(sum(s.todaysteps), 0) as sumstep,p.id as code,p.name as name" +
                    "   from Activityjoindetail ajd " +
                    "   left join Activityinfo a on a.id = ajd.activityid " +
                    "   left join Steprecordinfo s on s.userid = ajd.userid and s.moveoractivity = 0 " +
                    "   and date_format(s.stepdate, '%Y%m%d') >= date_format(ajd.createdate, '%Y%m%d') " +
                    "   and date_format(s.stepdate, '%Y%m%d') >= date_format(a.starttime, '%Y%m%d') " +
                    "   and date_format(s.stepdate, '%Y%m%d') <= date_format(a.endtime, '%Y%m%d')" +
                    "   left join Pccinfo p ");
            if (type.equals("P")) {
                buffer.append(" on ajd.provincialcomp = p.id where a.id = ?" +
                        " group by ajd.provincialcomp order by sumstep desc, p.orders");
            }
            if (type.equals("C")) {
                buffer.append(" on ajd.citycomp = p.id where a.id = ?" +
                        " group by ajd.citycomp order by sumstep desc, ajd.citycomp");
            }
            Query query = this.entityManager.createNativeQuery(buffer.toString());
            //设置请求参数
            query.setParameter(1, activityid);
            //将查询结果集转为Map
            query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            //获取查询结果集
            List<Map<String, Object>> maps = query.getResultList();
            List<ExclusiveProvincialAndCityListDTO> rungrouppushList = EntityAndDTO.
                    mapConvertToBean(maps, ExclusiveProvincialAndCityListDTO.class);
            return rungrouppushList;
        } catch (Exception e) {
            LOGGER.error("查询所有的省的总步数、按照省进行分组失败，原因：" + CommonUtil.getExceptionStackTrace(e));
            return null;
        }
    }