Informix选择并以矩阵形式输出结果

debugcn 发表于 Dev

亚历山大·巴尔塔萨（Alexander Baltasar）

我有一个名为“ sales_pos”的表，其中包含以下列：

cust_nr ; cust_name ; cust_ans ; date ; month_year ; value_goods

例子：

1234;Jon Doe;New York;31/01/2015;1/2015;250,00
4711;Max Muster;New York;22/03/2015;01/2015;900,00
0812;Will Smith;New York;22/02/2015;01/2015;300,00
1234;Jon Doe;New York;11/01/2015;1/2015;150,00

我想要一个结果如下的selec：

Customer                 |1/2015|2/2015|3/2015|4/2015| .. |12/2015|
0812 Will Smith New York |300,00|..    |..    |..    | .. |..     | 
1234 Jon Doe New York    |400,00|..    |..    |..    | .. |..     |  
4711 Max Muster New York |..    |..    |900,00|..

Select 
  cust_nr, cust_name, cust_ans, month_year, sum(value_goods)
from sales_pos
group by 
  cust_nr, cust_name, cust_ans, month_year

该选择语句包含我需要的所有信息，但我不知道如何将该结果转换为上面的矩阵。

我也尝试过：

select
  cust_nr, cust_name, cust_ans, month_year, sum(value_goods)
from sales_pos
where
  month_year = '1/2015'
group by cust_nr, cust_name, cust_ans
UNION ALL
select
  cust_nr, cust_name, cust_ans, month_year, sum(value_goods)
from sales_pos
where
  month_year = '2/2015'
group by cust_nr, cust_name, cust_ans
UNION ALL
select
  cust_nr, cust_name, cust_ans, month_year, sum(value_goods)
from sales_pos
where
  month_year = '3/2015'
group by cust_nr, cust_name, cust_ans
UNION ALL
...

但这也不起作用。希望有人能帮忙。谢谢

亚历山大·巴尔塔萨（Alexander Baltasar）

我终于找到了一个适用于INFORMATIONIX 7.5的解决方案

select DISTINCT 
   CUST.cust_nr, CUST.cust_name, CUST.cust_ans, 
   (select sum(M01.value_goods) from $table as M01 where M01.month_year = '1/2015' and M01.cust_nr = CUST.cust_nr),
   (select sum(M02.value_goods) from $table as M02 where M02.month_year = '2/2015' and M02.cust_nr = CUST.cust_nr),
   ...
   (select sum(M12.value_goods) from $table as M12 where M12.month_year = '12/2015' and M12.cust_nr = CUST.cust_nr)
from sales_pos as CUST order by CUST.cust_nr;

我不喜欢这种解决方案，但至少它能用:-(

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。