mysql - 如何获取exists子句中的数据

Question

我正在尝试从表rem2获取数据，但没有成功。我该怎么做？

SELECT *, rem2.id FROM remuneracao_performance_competencia AS competencia
JOIN remuneracao_performance AS rem1 ON competencia.remuneracao_performance_id = rem1.id
WHERE EXISTS (
    SELECT 1 FROM remuneracao_performance AS rem2 WHERE
        rem1.id <> rem2.id and rem1.cargo_id = rem2.cargo_id AND rem1.frequencia_campanha = rem2.frequencia_campanha AND rem1.modelo_remuneracao = rem2.modelo_remuneracao AND competencia.remuneracao_performance_id <> rem2.id AND rem2.inicio_vigencia_campanha <= competencia.inicio_vigencia and rem2.inicio_vigencia_campanha > rem1.inicio_vigencia_campanha
        ORDER BY rem2.inicio_vigencia_campanha DESC)
ORDER BY cargo_id ASC

错误信息是

Error Code: 1051. Unknown column 'rem2.id' in 'field list

Answer 1

我将查询更改为:

select distinct competencia.*, rem2.id from remuneracao_performance_competencia as competencia
join remuneracao_performance as rem1 on competencia.remuneracao_performance_id = rem1.id,
     remuneracao_performance as rem2
where 
rem1.id <> rem2.id and
rem1.cargo_id = rem2.cargo_id and rem1.frequencia_campanha = rem2.frequencia_campanha and
rem1.modelo_remuneracao = rem2.modelo_remuneracao and competencia.remuneracao_performance_id <> rem2.id and
rem2.inicio_vigencia_campanha <= competencia.inicio_vigencia and
rem2.inicio_vigencia_campanha > rem1.inicio_vigencia_campanha
group by competencia.id, rem1.cargo_id