本文共 3017 字，大约阅读时间需要 10 分钟。

问题描述

由以下表foo、和bar

foo

bar

-- ------------------------------ Table structure for bar-- ----------------------------DROP TABLE IF EXISTS `bar`;CREATE TABLE `bar`  (  `id` int(11) NOT NULL AUTO_INCREMENT,  `goods` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,  `alias` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of bar-- ----------------------------INSERT INTO `bar` VALUES (1, '愉快肥皂', 'ykfz');INSERT INTO `bar` VALUES (2, '快乐肥宅水', 'klfzs');INSERT INTO `bar` VALUES (3, '金坷垃', 'jkl');-- ------------------------------ Table structure for foo-- ----------------------------DROP TABLE IF EXISTS `foo`;CREATE TABLE `foo`  (  `id` int(11) NOT NULL AUTO_INCREMENT,  `inc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `alias` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `bar_id` int(11) NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of foo-- ----------------------------INSERT INTO `foo` VALUES (1, '天天好', 'tth', 1);INSERT INTO `foo` VALUES (2, '天天棒', 'ttb', 2);INSERT INTO `foo` VALUES (3, 'giao', 'g', 3);SET FOREIGN_KEY_CHECKS = 1;

现在需要关联对这两个表进行关联查询

由于foo和bar中都有id和alias字段，所以需要给他们增加别名，得到查询的SQL语句如下。

SELECT	foo.id AS id,	inc,	foo.alias AS alias,	goods,	bar.alias AS goods_alias FROM	foo	JOIN bar ON foo.bar_id = bar.id;

根据上面的SQL我们可以按照

构建一个Mapper和xml

xml

   
       
    	SELECT		foo.id AS id,		inc,		foo.alias AS alias,		goods,		bar.alias AS goods_alias 	FROM		foo		JOIN bar ON foo.bar_id = bar.id;	${ew.customSqlSegment}    
   

${ew.customSqlSegment} 是Mybatis Plus的动态条件构造器的最终条件SQL

Mapper

public interface FooBarMapper extends BaseMapper
   
     {
       List
    
      getPatchItemList(                @Param(Constants.WRAPPER) Wrapper
     
       wrapper);}
     
    
   

但我们执行下面查询语句时

Wrapper
   
     wrapper = Wrappers.
    
     lambdaQuery()                .like(FooBar::getAlias, "b"); // --> alias like '%b%'List
     
       res =                       FooBarMapperMapper.getPatchItemList(wrapper);
     
    
   

会报错 Column ‘alias’ in where clause is ambiguous，提示的意义是说这个 alias 字段是不够明确是暧昧的。

原因： 这是由于连表之后字段中alias重复了，查询结果集中含有两个alias不知道是哪一个才是要查询的，条件语句是针对查询结果集的所以字段重命名是无效的。

解决方式

1.使用明确的字段名称 表名.字段名

Wrapper
   
     wrapper = Wrappers.
    
     Query()                .like("foo.alias", "b");
    
   

2.把查询结果作为子查询，然后在增加条件语句

SELECT	* FROM	(	SELECT		foo.id AS id,		inc,		foo.alias AS alias,		goods,		bar.alias AS goods_alias 	FROM		foo	JOIN bar ON foo.bar_id = bar.id 	) AS t ${ew.customSqlSegment}

这种方式就可以直接使用下面方式进行查询而不需要补全表名：

Wrapper
   
     wrapper = Wrappers.
    
     lambdaQuery()                .like(FooBar::getAlias, "b");
    
   

该种方式是由我同事告诉我的，：P

转载地址：http://vcqof.baihongyu.com/