公司前段時間碰到如下的報表需求：
查詢某個用戶購買金額最大的前三條記錄，如下：
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 | | |
+----------+--------+--------+--------+------+