公司前段时间碰到如下的报表需求：
查询某个用户购买金额最大的前三条记录，如下：
customer price
A 1000
A 888
A 333
B 12222
B 12000
C 3000
C 2000
然后需要通过SQL将其变成四个字段
Customer price1 price2 price3
A 1000 888 333
B 12222 12000
C 3000 2000
这个问题牵涉到两个问题，一个取前N位的SQL，另一个将行记录怎么变成列字段。
第一个问题好解决：
1 select a.customer,a.price,count(b.price) as rn
2 from pt a join pt b on a.customer=b.customer and a.price<=b.price
3 groupby a.customer,a.price;
结果：
+----------+-------+----+
| customer | price | rn |
+----------+-------+----+
| A | 333 | 3 |
| A | 888 | 2 |
| A | 1000 | 1 |
| B | 12000 | 2 |
| B | 12222 | 1 |
| C | 2000 | 2 |
| C | 3000 | 1 |
+----------+-------+----+
然后就是将行记录变成列字段，这里采用if判断关键字，对上面的查询结果进行如下操作：
1 select
2
3 customer, (if(rn=1,price,'')) as price1, (if(rn=2,price,'')) as price2,
4
5 (if(rn=3,price,'')) as price3 from a；
执行结果如下所示：
+----------+--------+--------+--------+
| customer | price1 | price2 | price3 |
+----------+--------+--------+--------+
| A | 143.20 | | |
| A | | 99.99 | |
| A | | | 49.99 |
| B | 63.92 | | |
| B | | 33.99 | |
| C | 99.99 | | |
| C | | 24.10 | |
+----------+--------+--------+--------+
针对这个执行结果执行如下的sql既可以得到我们期望的结果了：
select customer,
max(if(rn=1,price,'')) as price1,
max(if(rn=2,price,'')) as price2,
max(if(rn=3,price,'')) as price3
from (
select a.customer,a.price,count(b.price) as rn
from pt a join pt b on a.customer=b.customer and a.price<=b.price
groupby a.customer,a.price )k
groupby customer;
+----------+--------+--------+--------+
| customer | price1 | price2 | price3 |
+----------+--------+--------+--------+
| A |1000 |888 |333 |
| B |12222 |12000 | |
| C |3000 |2000 | |
+----------+--------+--------+--------+
上面的记录中，价格没有重复的情况，如果有重复的，那么上面的结果方案是不行的，下面是一个办法：
1 createtable pt(customer varchar(19),price int);
2 insert pt select
3 'A',1000 union all select
4 'A',1000 union all select
5 'A', 888 union all select
6 'A',333 union all select
7 'B', 12222 union all select
8 'B', 12000 union all select
9 'C', 3000 union all select
10 'C', 2000;
11
12 set@n=0;
13 createtable kos select*,@n:=@n+1as id from pt; --建立个辅助表
14
15 select customer,
16 max(if(rn=1,price,'')) as price1,
17 max(if(rn=2,price,'')) as price2,
18 max(if(rn=3,price,'')) as price3,
19 from (
20 select a.customer,a.price,a.id,count(b.id) as rn
21 from kos a join kos b on a.customer=b.customer and a.id>=b.id
22 groupby a.customer,a.price,a.id )k
23 groupby customer;
执行结果如下：
+----------+--------+--------+--------+--------+
| customer | price1 | price2 | price3 | price4 |
+----------+--------+------+--------+------+
| A |1000 |1000 |888 |333 |
| B |12222 |12000 | | |
| C |3000 |2000 | | |
+----------+--------+--------+--------+------+