mysql - 在 SQL 语句中使用局部变量时出现问题

Question

我有这个 SQL 语句:

SELECT DNI, puntaje, @row := @row+1 as Puesto
FROM postulante, (SELECT @row := 0) r
WHERE idCa = 2 
  AND idPer = 1 
  AND @row <= (SELECT vacantes 
                FROM carrera_abierta 
                WHERE idCa=2 AND idPer=1)
ORDER BY puntaje DESC ;

我想限制列值返回的行数。但是当我运行时: It doesn't work :

Of course the result of the subquery is a small value :

出了什么问题？

Answer 1

您需要使用子查询:

SELECT * 
FROM (SELECT DNI, puntaje, @row := @row+1 as Puesto
      FROM postulante, (SELECT @row := 0) r
      WHERE idCa = 2 
        AND idPer = 1 
      ORDER BY puntaje DESC) sub
WHERE Puesto <= (SELECT vacantes 
                FROM carrera_abierta 
                WHERE idCa=2 AND idPer=1)
ORDER BY Puesto;