mysql - 不应存在的“列名重复”错误
问题描述
我正在尝试将数据库从 sqlite 迁移到 mariaDB(我从未使用过),但我似乎无法解决这个问题!
当我尝试运行它时,我总是得到
"#1060 - Duplicate column name 'id_document'"
这是查询:
SELECT
a.id_document AS id_document,
id_user_associatedDoc,
id_user_creator,
stage,
title,
goldStandardAnnotationManual,
count(content) AS answer_count
FROM
(SELECT *
FROM Document join DocumentProject
ON Document.id_document = DocumentProject.id_document
WHERE DocumentProject.id_project = 2) a
LEFT JOIN
(SELECT *
FROM Annotation
WHERE Annotation.id_project = 2) b
ON a.id_document = b.id_document
GROUP BY a.id_document;
我不明白为什么我应该得到这个错误!你能帮我吗?
解决方案
第一个子查询语法返回两个 id_document 列:
(SELECT *
FROM Document
join DocumentProject
ON Document.id_document = DocumentProject.id_document
WHERE DocumentProject.id_project = 2) a
解决此问题的快速方法:
SELECT
a.id_doc /*Change this column as following*/ AS id_document,
id_user_associatedDoc,
id_user_creator,
stage,
title,
goldStandardAnnotationManual,
count(content) AS answer_count
FROM
(SELECT *, Document.id_document as "id_doc" /*define the first id_document column as a different name*/
FROM Document
join DocumentProject
ON Document.id_document = DocumentProject.id_document
WHERE DocumentProject.id_project = 2) a
LEFT JOIN
(SELECT *
FROM Annotation
WHERE Annotation.id_project = 2) b
ON a.id_document = b.id_document
GROUP BY a.id_document;
您还可以预先定义要在子查询中返回的列。例子:
(SELECT Document.id_document, Document.column_2, Document.column_3
FROM Document
join DocumentProject
ON Document.id_document = DocumentProject.id_document
WHERE DocumentProject.id_project = 2) a
这样,您的外部查询应该根据您在子查询中预先定义的内容来选择 a.id_document,而无需更改外部查询的任何内容。
推荐阅读
- python-3.x - 在 python 中为 x 轴条形图添加数据间隔
- deep-learning - 如何更改语义分割的填充?
- javascript - TypeError:this.props.header 不是函数
- gradle - 如何在父文件夹的多模块 gradle 项目中声明公共依赖项
- c# - 如何过滤父级内的子列表并返回过滤了子列表的父级
- visual-studio-2017 - 对 VS 2017 的 ANTLR 语言支持
- mysql - 获取此mysql查询的中值
- python - 如何将样式应用于 Django 表单?
- python - 每次使用cross_val_score时如何洗牌?
- css - 显示隐藏内容 - 外观流畅