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

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

lMasterSparkl

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

阅读量484

点赞数

分类专栏： mysql java 文章标签： sql java 数据库 jpa

本文链接：https://blog.youkuaiyun.com/lMasterSparkl/article/details/115908436

版权

目的是将某一个字段返回数据通过处理进行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, sc.NAME AS subChannelName, " +
            "pr.NAME AS prName, dr.NAME AS drName , ev.NAME AS evName " +
            "FROM game_source_sub_channel_total_report gssctr " +
            "LE