douxiaochun4964 2015-12-30 06:19
浏览 133
已采纳

如何在选择查询中获取行号

i try this query to get row number in selected rows, but the output comes like 23,56,78,.... i need to get 1,2,3 to every selected row. please help me

SET @row=0;
SELECT `table1`.`col1`,`table1`.`col2`,@row:=@row+1
FROM `table1`
LEFT OUTER JOIN `table2` ON `table1`.`col1` = `table2`.`col5`
WHERE `table2`.`col5` IS NOT NULL
GROUP BY `col1` ORDER BY `table1`.`col7` DESC LIMIT 0,10
  • 写回答

2条回答 默认 最新

  • dongzi1209 2015-12-30 06:31
    关注

    Move the query with the ORDER BY clause into a subquery.

    SET @row = 0;
    
    SELECT col1, col2, @row := @row+1
    FROM (SELECT table1.col1, table1.col2
          FROM table1
          LEFT JOIN table2 ON table1.col1 = table2.col5
          WHERE table2.col5 IS NOT NULL
          GROUP BY col1
          ORDER BY table1.col7 DESC
          LIMIT 0, 10) AS subquery
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?