mysql实现ROW_NUMBER()

参考文章

直接po代码和图

#mysql实现ROW_NUMBER()
SELECT
	b.*, @rownum := @rownum + 1 AS rank
FROM
	book b,
	(SELECT @rownum := 0) myTempTable;

#mysql实现ROW_NUMBER()
SELECT
	b.*, @rownum := @rownum + 1 AS rank
FROM
	book b,
	(SELECT @rownum := 0) myTempTable;


#
SELECT * FROM booktype,(SELECT 'hi') myTempTable;
#SELECT * FROM booktype, SELECT 'hi' myTempTable; #报错
SELECT * FROM booktype,(SELECT 'hello', 666) AS myTempTable;
SELECT * FROM booktype,(SELECT 'I Love You' AS a, 999 as b) AS myTempTable;
SELECT *, myTempTable.b FROM booktype bt,(SELECT 'I Love You' AS a, 2 as b) AS myTempTable;
SELECT *, myTempTable.b + 6 FROM booktype AS bt,(SELECT 'I Love You' AS a, 2 as b) AS myTempTable;
SELECT *, myTempTable.b * 5 FROM booktype AS bt,(SELECT 'I Love You' AS a, 2 as b) AS myTempTable;
SELECT *, myTempTable.b - 5 AS result FROM booktype AS bt,(SELECT 'I Love You' AS a, 2 as b) AS myTempTable;
SELECT *, myTempTable.b + '***' FROM booktype AS bt,(SELECT 'I Love You' AS a, 2 as b) AS myTempTable;
SELECT *, CONCAT(myTempTable.b, '***') AS result FROM booktype AS bt,(SELECT 'I Love You' AS a, 2 as b) AS myTempTable;

SELECT 666, 888 + 2, 'hello' + 'world', 555 + 'hello', CONCAT('hello','world');
#取别名
SELECT 666 result1, 888 + 2 result2, 'hello' + 'world', 555 + 'hello', CONCAT('hello','world');
SELECT 666 AS result1, 888 + 2 AS 'result2', 'hello' + 'world', 555 + 'hello', CONCAT('hello','world');

#
SELECT @num1;
SELECT @num2 := 88;
SELECT @num2;

SELECT @num3 := 50;
SELECT @num4 := 15;
SELECT @sum := @num3 + @num4;
SELECT @sum;

#
SELECT @myRowNum := 5;
SELECT @myRowNum := 22 rowNum;
SELECT @myRowNum := 3 'rowNum';
SELECT @myRowNum := 36 AS rowNum;
SELECT @myRowNum := 43 AS 'rowNum';
(SELECT @myRowNum := 77 AS 'rowNum');
#(SELECT @myRowNum := 77 AS 'rowNum') myTempTable;#报错
#(SELECT @myRowNum := 77 AS 'rowNum') AS myTempTable;#报错
#(SELECT @myRowNum := 77 AS 'rowNum') AS 'myTempTable';#报错
#
SELECT * FROM booktype;
#SELECT * FROM booktype,(SELECT @myRowNum := 77 AS 'rowNum');#报错
#SELECT * FROM booktype,SELECT @myRowNum := 77 AS 'rowNum';#报错
#SELECT * FROM booktype,(SELECT @myRowNum := 77 AS 'rowNum') AS 'myTempTable';#报错
SELECT * FROM booktype,(SELECT @myRowNum := 77 AS 'rowNum') myTempTable;
SELECT * FROM booktype,(SELECT @myRowNum := 77 AS 'rowNum') AS myTempTable;
SELECT *, @myRowNum FROM booktype,(SELECT @myRowNum := 77 rowNum) AS myTempTable;
SELECT *, @myRowNum FROM booktype,(SELECT @myRowNum := 77 AS 'rowNum') AS myTempTable;
SELECT *, @myRowNum FROM booktype,(SELECT @myRowNum := 77 AS rowNum) AS myTempTable;

SELECT *, @myRowNum AS myNum FROM booktype,(SELECT @myRowNum := 77 AS 'rowNum') AS myTempTable;
SELECT *, @myRowNum AS myNum, @myRowNum + 1 FROM booktype,(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;
SELECT *, @myRowNum , rowNum + 1 FROM booktype,(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

SELECT *, @myRowNum := @myRowNum + 1 FROM booktype,(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;
SELECT *, @myRowNum , @myRowNum := @myRowNum + 1 FROM booktype,(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

SELECT *, @myRowNum , @myRowNum := @myRowNum + 1 rank FROM booktype,(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;
SELECT *, @myRowNum , @myRowNum := @myRowNum + 1 'rank' FROM booktype,(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;
SELECT *, @myRowNum , @myRowNum := @myRowNum + 1 AS rank FROM booktype,(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;
SELECT *, @myRowNum , @myRowNum := @myRowNum + 1 AS 'rank' FROM booktype,(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

#
SELECT bt.*, @myRowNum , @myRowNum := @myRowNum + 1 AS 'rank' 
FROM booktype AS bt,
(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

#
SELECT bt.*, rowNum, myTempTable.rowNum, @myRowNum , @myRowNum := @myRowNum + 1 AS 'rank' 
FROM booktype AS bt,
(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

#
SELECT bt.*, @myRowNum := @myRowNum + 1 AS 'rank' 
FROM booktype AS bt,
(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

#
SELECT * FROM book;
SELECT * FROM booktype;
SELECT * FROM book,booktype;

#
SELECT *, @myRowNum := @myRowNum + 1 FROM booktype,
(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

#取别名
SELECT *, @myRowNum := @myRowNum + 1 rank FROM booktype,
(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

SELECT *, @myRowNum := @myRowNum + 1 'rank' FROM booktype,
(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

SELECT *, @myRowNum := @myRowNum + 1 AS rank FROM booktype,
(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;

SELECT *, @myRowNum := @myRowNum + 1 AS 'rank' FROM booktype,
(SELECT @myRowNum := 0 AS 'rowNum') AS myTempTable;