mysql统计用户不同充值渠道的充值总数

0 0

mysql统计用户不同充值渠道的充值总数

数据库结构:

  create table tx(
  

  id int(11) primary key,
  

  c1 varchar(255) COMMENT '用户名',
  

  c2 varchar(255) COMMENT '充值渠道',
  

  c3 int(11) COMMENT '充值金额'
  

  );
  

  insert into tx values
  

  (1 ,'A1','B1',9),
  

  (2 ,'A2','B1',7),
  

  (3 ,'A3','B1',4),
  

  (4 ,'A4','B1',2),
  

  (5 ,'A1','B2',2),
  

  (6 ,'A2','B2',9),
  

  (7 ,'A3','B2',8),
  

  (8 ,'A4','B2',5),
  

  (9 ,'A1','B3',1),
  

  (10 ,'A2','B3',8),
  

  (11 ,'A3','B3',8),
  

  (12 ,'A4','B3',6),
  

  (13 ,'A1','B4',8),
  

  (14 ,'A2','B4',2),
  

  (15 ,'A3','B4',6),
  

  (16 ,'A4','B4',9),
  

  (17 ,'A1','B4',3),
  

  (18 ,'A2','B4',5),
  

  (19 ,'A3','B4',2),
  

  (20 ,'A4','B4',5);

期望得到的结果如下，每个用户使用每个充值渠道的充值总和：

  +------+-----+-----+-----+-----+------+
  

  |C1    |B1   |B2   |B3   |B4   |Total |
  

  +------+-----+-----+-----+-----+------+
  

  |A1    |9    |2    |1    |11   |23    |
  

  |A2    |7    |9    |8    |7    |31    |
  

  |A3    |4    |8    |8    |8    |28    |
  

  |A4    |2    |5    |6    |14   |27    |
  

  |Total |22   |24   |23   |40   |109   |
  

  +------+-----+-----+-----+-----+------+

求sql语句如何实现。

sql mysql

12 years, 9 months ago

腐化D世界

腐化D世界 12 years, 9 months ago

sql语句如下：

  SELECT IFNULL(c1,'total') AS C1,
  

  SUM(if(c2='B1',c3,0)) AS B1,
  

  SUM(if(c2='B2',c3,0)) AS B2,
  

  SUM(if(c2='B3',c3,0)) AS B3,
  

  SUM(if(c2='B4',c3,0)) AS B4,
  

  SUM(c3) AS TOTAL
  

  FROM tx
  

  GROUP BY c1 WITH ROLLUP ;

answered 12 years, 9 months ago

山田大魔王

山田大魔王 answered 12 years, 9 months ago

mysql统计用户不同充值渠道的充值总数

腐化D世界

Answers

山田大魔王

Your Answer