spel sql jpa 通过interface 进行字段id中文映射

最新推荐文章于 2022-09-20 17:10:04 发布

原创

最新推荐文章于 2022-09-20 17:10:04 发布 · 532 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #java #数据库 #jpa

目的是将某一个字段返回数据通过处理进行pid—>转换为pidName的复杂处理
方案有两个：
1.通过sql直接进行处理
2.通过返回interface进行处理
方案一：一坨屎 sql 在查询中通过left join 多张表 COALESCE 进行判断进行展示

前方高能

    @Query(value = "SELECT id, gameId, pid, NewUser, newSourcePercent , totalSourcePercent, USER, sumUser, channelId, subChannelId , dt, gameName, channelName, subChannelName , " +
            "COALESCE(gssctr.prName, gssctr.drName, gssctr.evName, '未知') AS plateName " +
            "FROM ( SELECT gssctr.id AS id, gssctr.game_id AS gameId, gssctr.pid AS pid, gssctr.n_user AS NewUser , " +
            "IF(gsctr.USER = 0, 0, FORMAT(gssctr.n_user * 100 / gsctr.USER, 2)) AS newSourcePercent , " +
            "IF(gsctr.USER = 0, 0, FORMAT(gssctr.USER * 100 / gsctr.USER, 2)) AS totalSourcePercent , " +
            "gssctr.USER AS USER, gsctr.USER AS sumUser, gssctr.channel_id AS channelId, gssctr.sub_channel_id AS subChannelId, gssctr.dt AS dt , g.NAME AS gameName, c.NAME AS channelName