编写SQL转动表

Question

我想这样

输入数据表

customer_id fee_name amount 
1 a 1 
1 b 3.25 
1 c 1.75 
1 d 2 
1 e 0 
2 a 1 
2 b 3.25 
2 c 1.75 
2 d 2 
2 e 0 
3 a 1 
3 b 3.25 
3 c 1.75 
3 d 3.5 
3 e 0 
4 a 1 
4 b 3.25 
4 c 1.75 
4 d 3.5 
4 f 1 
5 a 1 
5 b 3.25 
5 c 1.75 
5 d 3.5 
5 f 1 

见创造的东西，你必须为所有FEE_NAME和数量组。所以这里会有三组。 1类似的时间表顾客1和2，第二组类似的客户3和类似客户4和5名这个基团也时间表第三组

输出1应该像

group a b  c  d e f 
    1 1 3.25 1.75 2 0 0 
    2 1 3.25 1.75 3.5 0 0 
    3 1 3.25 1.75 3.5 0 1 

第二输出表应该将这些组映射到客户ID。 所以第二个输出应该看起来像

customer id group 
1 1 
2 1 
3 2 
4 3 
5 3 

任何帮助，高度赞赏。 谢谢

Answer 1

SELECT x.* 
    , y.customer_id 
    , CASE WHEN @prev = x.fee_schedule THEN @i:[email protected] ELSE @i:[email protected]+1 END i 
    , @prev:=x.fee_schedule 
    FROM 
    (SELECT DISTINCT GROUP_CONCAT(amount ORDER BY fee_name) fee_schedule FROM my_table GROUP BY customer_id) x 
    JOIN 
    (SELECT customer_id, GROUP_CONCAT(amount ORDER BY fee_name) fee_schedule FROM my_table GROUP BY customer_id) y 
    ON y.fee_schedule = x.fee_schedule 
    JOIN 
    (SELECT @prev:=null, @i:=0) vars 
ORDER 
    BY y.fee_schedule 
    , y.customer_id;