mysql - 以特定格式显示数据时遇到问题

Question

我有一个包含数据的表格，

Item    sales_description   date        amount  sequence
----    -----------------   ----        ------  --------
 xyz    base cost           2013-03-31  2.50    1
 xyz    packing charges     2013-03-31  5.50    2
 xyz    miscellaneous       2013-03-31  1.50    3

 xyz    base cost           2013-06-30  3.50    1
 xyz    packing charges     2013-06-30  6.50    2
 xyz    miscellaneous       2013-06-30  1.00    3
 xyz    delivery charges    2013-06-30  5.00    4

 xyz    base cost           2013-09-30  5.00    1
 xyz    packing charges     2013-09-30  6.50    2
 xyz    labor charges       2013-09-30  3.50    3
 xyz    miscellaneous       2013-09-30  1.00    4
 xyz    delivery charges    2013-09-30  5.00    5

我正在寻找的输出是

Item    sales_description   date1       amount1     date2       amount2     date3       amount3
----    -----------------   -----       -------     -----       -------     -----       -------
 xyz    base cost           2013-03-31  2.50        2013-06-30  3.50        2013-09-30  5.00
 xyz    packing charges     2013-03-31  5.50        2013-06-30  6.50        2013-09-30  6.50
 xyz    labor charges       NULL        NULL        NULL        NULL        2013-09-30  3.50
 xyz    miscellaneous       2013-03-31  1.50        2013-06-30  1.00        2013-09-30  1.00
 xyz    delivery charges    NULL        NULL        2013-06-30  5.00        2013-09-30  5.00

我尝试编写一个存储过程并将值以上述输出格式存储在临时表中，并从临时表中查询它，但无法实现。

还有其他方法可以得到结果吗？任何建议都会有帮助。

谢谢

Answer 1

这是一个粗略的答案:

编辑:我在这个答案中错过了有关序列号的部分。让我解决这个问题。

SELECT t.Item, t.sales_descriptions, 
 ( SELECT sub1.date, sub1.amount FROM table AS sub1 ORDER BY sub1.date asc limit 1) AS date1,
 ( SELECT sub2.date, sub2.amount FROM table AS sub2 ORDER BY sub2.date asc limit 1,1) AS date2,
 ( SELECT sub2.date, sub2.amount FROM table AS sub2 ORDER BY sub2.date asc limit 2,1) AS date3,
  ...
 FROM table as t
 GROUP BY t.Item, t.sales_description;