mysql虚拟行号,有没有一种简单的方法通过mysql中的行号联接行？

So, I have two tables, the first has e.g. 4 records, and the second one contains e.g. two rows. They are not related in any way, just want them to be shown together in a way shown on the picture.

I know it could be done by joining them by calculating their row number and using those, but I am curious if is there a way to do the same thing without using a variable. Thank you for your advise, and sorry for my english :)

解决方案

Yes, there is a way to do it without using variables, just stright SQL

See this demo: http://www.sqlfiddle.com/#!2/2eeb2/4

Unfortunately, MySql doesn't implement analytic functions like Oracle, Postgre and MS-SQL:

ROW_NUMBER() OVER (partition by ... order by ... ) nor rownum pseudocolumn like Oracle, and a performace of queries like this in MySql is very poor.

SELECT *

FROM (

SELECT emp.*,

(SELECT count(*)

FROM emp e

WHERE e.empno <= emp.empno

) rownum

FROM emp

) e

LEFT JOIN (

SELECT dept.*,

(SELECT count(*)

FROM dept d

WHERE d.deptno <= dept.deptno

) rownum

FROM dept

) d

ON e.rownum = d.rownum;