mysql“粘贴"两个结果一起(并排) [英] mysql "paste" two results together (side by side)

问题描述

我想在Unix中使用paste命令，它需要两个文件并打印第一行，第一个文件，然后是分隔符，然后是第二个文件的第一行，然后是换行符，然后是第一个文件的第二行，分隔符是第二行第二个文件，等等.

I want something like paste command in unix, it takes two files and prints first line, of first file, then separator, then first line from second file, then newline, then second line from first file separator second line from second file, etc.

所以我想要在sql中这样的事情，从两个表中获取列，输出结果，从第一张和第二张表中获取第一行(作为一行)的列，然后从两个表中获取第二行，等等.第一个表的第二行，第二个表的第二行，等等.这甚至可能吗?很难在网上搜索...

So I want something like this in sql, take columns from two tables, output result, columns of first rows (as one row) from first and second table, then second rows from both tables etc. Without cross join stuff like first row from first table with second row from second table etc. Is this even possible? Very hard to search for this on the net...

Table 1:          Table 2:
column bla        column cla
a                 80
z                 7
f                 15
k

Expected result:
column bla, column cla
a, 80
z, 7
f, 15
k, NULL

非常简单:)，除了一点都不...

Very simple :), except not at all...

请不要@variables

please no @variables

推荐答案

MYSQL中没有ROWNUMBER工具，但是您可以像这样模仿它:

There's no ROWNUMBER facility in MYSQL but you can mimick it like this :

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r

因此您可以进行2个查询，以返回每个表的行号:

So you can make 2 queries returning the row number for each table:

SELECT bla, @rownum := @rownum + 1 AS rank
  FROM table_1 , (SELECT @rownum := 0) r1


SELECT bla, @rownum := @rownum + 1 AS rank
  FROM table_2 , (SELECT @rownum := 0) r2     

并在行号上将它们连接在一起

And join them together on the row number

SELECT
    CONCAT( T1.bla,',',T2.cla) 
FROM
    (   SELECT bla, @rownum := @rownum + 1 AS rank
          FROM table_1 , (SELECT @rownum := 0) r1
    ) T1
INNER JOIN
    (       SELECT cla, @rownum := @rownum + 1 AS rank
      FROM table_2 , (SELECT @rownum := 0) r2     
    ) T2
    ON T1.rank = T2.rank

当然有了INNER join来获得您期望的结果，我猜想您在两个表中的行数都相同，否则对我来说这没有意义.

Of course with that INNER join to get the results your are expecting, I have guessed that you have the same number of rows in both tables, otherwise this has no sense to me.

基思(Keith)或多或少地发布了相同的方法，我有点晚了

I am a bit late as keith posted more or less the same approach

这篇关于mysql“粘贴"两个结果一起(并排)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！