mysql - 错误代码: 1052 - Column 'idk' in field list is ambiguous

Question

您好，我尝试创建查询并添加此查询，查询错误:

JOIN matakuliah ON nilai.idk = matakuliah.idk
WHERE matakuliah.semester = 1

完整查询:

SET @sql_dinamis = ( SELECT GROUP_CONCAT( DISTINCT CONCAT( 'max( IF(idk = ', idk, ',huruf,null) ) AS A', idk ) ) FROM nilai  );

SET @SQL = CONCAT( 'SELECT mahasiswa.nim, mahasiswa.nama, ', @sql_dinamis, ' 
FROM nilai
JOIN mahasiswa ON nilai.idm = mahasiswa.idm
JOIN matakuliah ON nilai.idk = matakuliah.idk
WHERE matakuliah.semester = 1
GROUP BY nilai.idm' );
PREPARE stmt 
FROM
@SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

错误信息:

FROM nilai
JOIN mahasiswa ON nilai.idm = mahasiswa.idm
JOIN matakuliah ON nilai.idk = matakuliah.idk
WHERE matakuliah.semester = 1
GROUP BY nilai.idm' )
> OK
> Time: 0s


PREPARE stmt 
FROM
@SQL
> 1052 - Column 'idk' in field list is ambiguous
> Time: 0s

帮我解决这个问题。

这是我的数据库 https://irhas.online/test/test2.txt请帮助我。

Answer 1

您的查询引用了 idk(来自 @sql_dinamis 部分)，但是您的 nilai 和 matakuliah > 其中有 idk 字段，因此 SQL 不知道您想要哪一个。您需要更改

中的 idk

SET @sql_dinamis = ( SELECT GROUP_CONCAT( DISTINCT CONCAT( 'max( IF(idk = ', idk, ',huruf,null) ) AS A', idk ) ) FROM nilai  );

为 nilai.idk 或 matakuliah.idk 例如

SET @sql_dinamis = ( SELECT GROUP_CONCAT( DISTINCT CONCAT( 'max( IF(nilai.idk = ', idk, ',huruf,null) ) AS A', idk ) ) FROM nilai  );

这对我有用(请参阅 DBFiddle )