如何在MYSQL查询中的其他选择中进行选择？

Question

How can I select from in other select in MYSQL query? Something like this

SET @row_number = 0;

SELECT a.num FROM

(SELECT 
    (@row_number:=@row_number + 1) AS num, id
FROM
    main) as a where a.id=6

I want to know the number of record where id=6 if it's the first row, second row or third one

please anyone can help me.

Answer 1

If your query has the filter where a.id = 6, then the row with id = 6 will always be the first row of the result set.

I am interpreting your question to mean: "if I sorted by id ascending, what row number is the row with id = 6 going to be on". If so, you can use a simple aggregation:

SELECT COUNT(*)
FROM main m
WHERE m.id <= 6;

Your query seems inspired by enumerating all the rows. You could do this version as well:

select m.*
from (select m.*, (@rn := @rn + 1) as rn
      from main m cross join
           (select @rn := 0) params
      order by id
     ) m
where id = 6;

The first version should be more efficient, particularly with an index on id.