mysql列转行的问题，求助。

[aa6875811]

现在有一张表，如上图所示。经过如下的sql语句处理：
select airline,airlinenum, MAX(CASE beforedates when 1 then price END) AS beforedate1price,MAX(CASE beforedates when 2 then price END) AS beforedate2price
from airline6 GROUP BY airline,airlinenum
得到如下的数据。

得到的beforedate1price和beforedate2price都是group by之后最大的那个。而我现在想要得到inserttime为最大所对应的那个价格，sql该怎么写？

[qq_17367431]

SELECT airline,airlinenum,beforedate1price,beforedate2price FROM (SELECT airline,airlinenum,MAX(CASE beforedates when 1 then price END) AS beforedate1price,MAX(CASE beforedates when 2 then price END) AS beforedate2price,inserttime FROM airline6 GROUP BY inserttime) AS a JOIN (SELECT max(inserttime) AS inserttime FROM airline6 GROUP BY beforedates) AS b ON a.inserttime = b.inserttime 输出结果： airline airlinenum beforedate1price beforedate2price 东方航空 MU583 3541 东方航空 MU583 2563 大概是这意思么

[ACMAIN_CHM]

http://blog.csdn.net/acmain_chm/article/details/4283943 MySQL交叉表 在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx( id int primary key, c1 c...

[qq_17367431]

SELECT airline,airlinenum,GROUP_CONCAT(beforedate1price) AS beforedate1price,GROUP_CONCAT(beforedate2price) AS beforedate2price FROM (SELECT airline,airlinenum,MAX(CASE beforedates when 1 then price END) AS beforedate1price,MAX(CASE beforedates when 2 then price END) AS beforedate2price,inserttime FROM airline6 GROUP BY inserttime) AS a JOIN (SELECT max(inserttime) AS inserttime FROM airline6 GROUP BY beforedates,airline,airlinenum) AS b ON a.inserttime = b.inserttime GROUP BY airline,airlinenum

[aa6875811]

引用 楼主 aa6875811 的回复:

现在有一张表，如上图所示。经过如下的sql语句处理：
select airline,airlinenum, MAX(CASE beforedates when 1 then price END) AS beforedate1price,MAX(CASE beforedates when 2 then price END) AS beforedate2price
from airline6 GROUP BY airline,airlinenum
得到如下的数据。

得到的beforedate1price和beforedate2price都是group by之后最大的那个。而我现在想要得到inserttime为最大所对应的那个价格，sql该怎么写？

不是这样，我想要的结果是这样的。
输出结果：

beforedate1price 跟beforedate2price都是inserttime最大所对应的price

[文修]

跪求楼主结贴给分