有没有一种简单的方法通过mysql中的行号联接行? [英] Is there a simple way join rows by their row number in mysql?

问题描述

所以，我有两个表，第一个有4条记录，第二条包含例如两排.它们没有任何关系，只是希望它们以图片中显示的方式一起显示. 我知道可以通过计算行数并使用它们来加入它们，但是我很好奇是否有一种方法可以在不使用变量的情况下做同样的事情.谢谢您的建议，对不起我的英语:)

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 :)

推荐答案

是的，有一种方法可以不使用变量，而只是使用严格的SQL
观看此演示: http://www.sqlfiddle.com/#!2/2eeb2/4

不幸的是，MySql没有实现像Oracle，Postgre和MS-SQL这样的分析功能:
ROW_NUMBER() OVER (partition by ... order by ... )也不像Oracle那样是rownum伪列，并且在MySql中这种查询的性能非常差.

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; 

这篇关于有没有一种简单的方法通过mysql中的行号联接行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！