嵌套子查询和join查询报错

[zhonyue314]

Question

表结构
CREATE TABLE link_user_calss (
id int(11) NOT NULL,
class_id int(11) DEFAULT NULL,
t_id int(11) DEFAULT NULL,
class_honor varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

sql 语句
SELECT
*
FROM
(
SELECT
luc.cid,
luc.classHonor,
b.t_id AS tId
FROM
( SELECT class_id AS cid, GROUP_CONCAT(class_honor ORDER BY class_honor SEPARATOR ';') AS classHonor FROM link_user_calss GROUP BY class_id ) luc
JOIN link_user_calss b ON b.class_id = luc.cid
GROUP BY
luc.cid,
luc.classHonor,
b.t_id
) w
LEFT JOIN link_user_calss lw ON w.tId = lw.t_id
报错
Caused by: java.lang.RuntimeException: com.alibaba.druid.sql.parser.ParserException: parse error. detail message is :
Relation condition column must in select columns.
source sql is :
SELECT * FROM ( SELECT luc.cid, luc.classHonor, b.t_id AS tId FROM ( SELECT class_id AS cid, GROUP_CONCAT(class_honor ORDER BY class_honor SEPARATOR ';') AS classHonor FROM link_user_calss GROUP BY class_id ) luc JOIN link_user_calss b ON b.class_id = luc.cid GROUP BY luc.cid, luc.classHonor, b.t_id ) w LEFT JOIN link_user_calss lw ON w.tId = lw.t_id
at com.alibaba.otter.canal.client.adapter.es.core.ESAdapter.init(ESAdapter.java:91) ~[client-adapter.es7x-1.1.5-jar-with-dependencies.jar:na]
at com.alibaba.otter.canal.client.adapter.es7x.ES7xAdapter.init(ES7xAdapter.java:50) ~[client-adapter.es7x-1.1.5-jar-with-dependencies.jar:na]
... 42 common frames omitted

[zhonyue314]

public Map<FieldItem, List> getRelationTableFields() {
if (relationTableFields == null) {
synchronized (SchemaItem.class) {
if (relationTableFields == null) {
relationTableFields = new LinkedHashMap<>();

                    getRelationFields().forEach(relationFieldsPair -> {
                        FieldItem leftFieldItem = relationFieldsPair.getLeftFieldItem();
                        FieldItem rightFieldItem = relationFieldsPair.getRightFieldItem();
                        FieldItem currentTableRelField = null;
                        if (getAlias().equals(leftFieldItem.getOwner())) {
                            currentTableRelField = leftFieldItem;
                        } else if (getAlias().equals(rightFieldItem.getOwner())) {
                            currentTableRelField = rightFieldItem;
                        }

                        if (currentTableRelField != null) {
                            List<FieldItem> selectFieldItem = getSchemaItem().getColumnFields()
                                .get(leftFieldItem.getOwner() + "." + leftFieldItem.getColumn().getColumnName());
                            if (selectFieldItem != null && !selectFieldItem.isEmpty()) {
                                relationTableFields.put(currentTableRelField, selectFieldItem);
                            } else {
                                selectFieldItem = getSchemaItem().getColumnFields()
                                    .get(rightFieldItem.getOwner() + "."
                                         + rightFieldItem.getColumn().getColumnName());
                                if (selectFieldItem != null && !selectFieldItem.isEmpty()) {
                                    relationTableFields.put(currentTableRelField, selectFieldItem);
                                } else {
                                    throw new UnsupportedOperationException(
                                        "Relation condition column must in select columns.");
                                }
                            }
                        }
                    });
                }
            }
        }
        return relationTableFields;
    }

拿着最外层的select字段取最里面一层检查on 条件，真尼玛人才