mybatis动态SQL——实现横标纵标转化

- 第一步 Mapper里面

@Mapper
public interface ShopCompareMapper {
//ShopCompareProvider.class中前面为类名，后为编译后生成的.class文件
//shopcomsql为ShopCompareProvider这个类里面的方法名

    @SelectProvider(type = ShopCompareProvider.class, method = "shopcomsql")
    List<Map<String, Object>> shopcompare(Map<String, Object> map);
//Map<String, Object> map 是传进来的参数，是键值对的数组，string为键，object为值，这里的Map<String, Object> map几乎适用于所有情形
}

注意这里传参时如果有多个参数，记得要使用@Param注解

- 第二步 写 xxxProvider类

public class ShopCompareProvider {

    public String shopcomsql(Map<String, Object> map){
//获取参数里的数据
        String [] shops = (String[]) map.get("shop");
//准备拼SQL语句
        String tem = """
                select 
                left(subject_name,locate('-',subject_name)-1) '科目总名',
                subject_name '科目名称',
                count(case when 
                """;
//%s为传进的参数，与下面给的参数是一一对应的
        String tem1 = "shop_name = '%s' ";
        String tem2 = " then 1 end) '累计执行', " ;
        String tem3 = """
               count(case when shop_name = '%s' then 1 end) '%s',
               """ ;
        String tem4 = """
               count(case when shop_name = '%s' then 1 end) '%s'
               """ ;
        String a = " or ";
        String end = """
                from yycwxt_test.core_flows
                where subject_name is not null
                group by  科目总名, 科目名称
                """;
//定义SQL生成器，之后一点一点的给他添加成一句完整的SQL语句
        StringBuilder sql = new StringBuilder();
        sql.append(tem);
        for (int i = 0; i < shops.length; i++) {
//通过for循环对应赋值
            sql.append(String.format(tem1, shops[i]));
            if (i<shops.length-1){
                sql.append(a);
            }
        }
        sql.append(tem2);
        for (int i = 0; i < shops.length; i++) {
            sql.append(String.format(tem3, shops[i],shops[i]));
            if (i == shops.length-1){
                sql.append(String.format(tem4, shops[i], shops[i]));
            }
        }
        sql.append(end);
        return sql.toString();
    }
}

- 参考

public class SubCompareProvider {
    public String subcomsql(Map<String, Object> map){
        String [] subjects = (String[]) map.get("subject");
//        Integer [] lens = (Integer []) map.get("len");
        String tem = """
                select 
                left((select fullname where code like '%s%%'),locate('-',fullname)-1) '科目总名',
                        (select fullname where code like '%s%%') '科目名称',
                        count(case when subject_name = fullname then 1 end) '累计执行',
                        count(case when shop_name = '天猫-榆园食品专营店' then 1 end) '天猫榆园食品专营店',
                        count(case when shop_name = '淘宝-榆园东北特产店' then 1 end) '淘宝榆园东北特产店',
                        count(case when shop_name = '美团-榆园食品专营店' then 1 end) '美团榆园食品专营店',
                        count(case when shop_name = '抖音-榆园食品专营店' then 1 end) '抖音榆园食品专营店',
                        count(case when shop_name = '天猫-福宁旗舰店' then 1 end) '天猫福宁旗舰店',
                        count(case when shop_name = '抖音-青花美食坊' then 1 end) '抖音青花美食坊',
                        count(case when shop_name = '京东-榆园食品专营店' then 1 end) '京东榆园食品专营店',
                        count(case when shop_name = '快手-榆园食品专营店' then 1 end) '快手榆园食品专营店',
                        count(case when shop_name = '天猫-榆园食品旗舰店' then 1 end) '天猫榆园食品旗舰店',
                        count(case when shop_name = '天猫-青花食品旗舰店' then 1 end) '天猫青花食品旗舰店',
                        count(case when shop_name = '拼多多-榆园调味品专营店' then 1 end) '拼多多榆园调味品专营店',
                        count(case when shop_name = '拼多多-榆园食品专营店' then 1 end) '拼多多榆园食品专营店',
                        count(case when shop_name = '拼多多-榆园调味品专营店' then 1 end) '拼多多榆园调味品专营店',
                        count(case when shop_name = '淘宝-榆园食品特卖店' then 1 end) '淘宝榆园食品特卖店',
                        count(case when shop_name = '拼多多-青花食品旗舰店' then 1 end) '拼多多青花食品旗舰店',
                        count(case when shop_name = '淘宝-青花食品特卖店' then 1 end) '淘宝青花食品特卖店',
                        count(case when shop_name = '微信小商店' then 1 end) '微信小商店',
                        count(case when shop_name = '京东-昊七食品专营店' then 1 end) '京东昊七食品专营店',
                        count(case when shop_name = '抖音-昊七七食品旗舰店' then 1 end) '抖音昊七七食品旗舰店'
                        from yycwxt_test.core_flows join yycwxt_test.core_subject on code = subject_code
                        where subject_code like '%s%%'
                        group by 科目总名, 科目名称
                """;
       String a = """
               union 
               """ ;
        StringBuilder sql = new StringBuilder();
        for (int i = 0; i < subjects.length; i++) {
            sql.append(String.format(tem, subjects[i], subjects[i], subjects[i]));
            if (i<subjects.length-1){
                sql.append(a);
            }
        }
        return sql.toString();
    }
}

参考里的SQL相对简单，更容易理解