如何在mysql查询中将10行分成2列

omqzjyyz  于 2021-06-15  发布在  Mysql
关注(0)|答案(2)|浏览(446)


在mysql表中有10行,我需要在一列中将10行拆分为5行,在另一列中再拆分为5行。

什么样的查询才能得到这样的结果?

wvyml7n5

wvyml7n51#

在mysql中这样做非常可怕,而在应用程序语言中这样做更好。
粗略地说,您可以执行以下操作,但它缺乏任何类型的可伸缩性:

SELECT a.value, b.value
FROM tbl a
JOIN tbl b ON a.id=b.id-5
WHERE a.id < 6
1l5u6lss

1l5u6lss2#

请尝试下面的查询,它将动态地将行拆分为列,不带任何参数,只需用列名替换“time”。

SELECT b.*, c.* from 
      (SELECT CEILING(COUNT(id) / 2) as totalId from test) as a JOIN 
      (SELECT id as id1, Time as column1 FROM test) as b LEFT JOIN
      (SELECT id as id2, Time as column2 FROM test) as c
      ON b.id1 = c.id2-a.totalId 
      WHERE b.id1 < a.totalId+1

您可以将*替换为列名,如下所示:

SELECT b.column1, c.column2 from 
      (SELECT CEILING(COUNT(id) / 2) as totalId from test) as a JOIN 
      (SELECT id as id1, Time as column1 FROM test) as b LEFT JOIN
      (SELECT id as id2, Time as column2 FROM test) as c
      ON b.id1 = c.id2-a.totalId 
      WHERE b.id1 < a.totalId+1

相关问题