查询某个用户购买金额最大的前三条记录,如下: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这个SQL应该怎么写啊,多少条SQL没关系,请帮忙一下,谢谢。
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这个SQL应该怎么写啊,多少条SQL没关系,请帮忙一下,谢谢。
解决方案 »
- 请教一个创建表的问题
- mysql 支持 两台服务器的链表查下吗
- mysql 集群 提问
- mysql正则表达式匹配标签内容
- 求一条统计用的sql
- mysql 中实现SET IDENTITY_INSERT tablename ON功能的语句怎么写?
- c语言中用C API连接mysql数据库的问题 急切期盼高手帮忙
- ~~~~~如何改变localhost~~~~~
- 大家知不知道,在Mysql 一个数据库能放多少表 ,200个表有问题吗?性能会怎样。??
- 我希望成为MySQL分栏的版主,为大家作点贡献,请支持我,过来UP吧!!
- 研究mysql源代码 能对数据库 架构深入了解吗?
- 类似MS-SQL的这种语句应该怎么写?
insert pt select
'A',1000 union all select
'A', 888 union all select
'A',333 union all select
'B', 12222 union all select
'B', 12000 union all select
'C', 3000 union all select
'C', 2000;select customer,
max(if(rn=1,trim(price),'')) as price1,
max(if(rn=2,trim(price),'')) as price2,
max(if(rn=3,trim(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
group by a.customer,a.price )k
group by customer;+----------+--------+--------+--------+
| customer | price1 | price2 | price3 |
+----------+--------+--------+--------+
| A | 1000 | 888 | 333 |
| B | 12222 | 12000 | |
| C | 3000 | 2000 | |
+----------+--------+--------+--------+
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
group by a.customer,a.price )k
group by customer;
Customer id1 price1 id2 price2 id3 price3
A 1 1000 2 888 3 333
B 4 12222 5 12000
C 6 3000 7 2000
select customer,
@n:=@n+1 as id1,
max(if(rn=1,price,'')) as price1,
@n:=@n+1 as id2,
max(if(rn=2,price,'')) as price2,
@n:=@n+1 as id3,
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
group by a.customer,a.price )k
group by customer;+----------+------+--------+------+--------+------+--------+
| customer | id1 | price1 | id2 | price2 | id3 | price3 |
+----------+------+--------+------+--------+------+--------+
| A | 1 | 1000 | 2 | 888 | 3 | 333 |
| B | 4 | 12222 | 5 | 12000 | 6 | |
| C | 7 | 3000 | 8 | 2000 | 9 | |
+----------+------+--------+------+--------+------+--------+
feixianxxx太牛了
但是重复数据呢,比如
id customer price
1 A 1000
2 A 1000这种情况好像有问题啊。
insert pt select
'A',1000 union all select
'A',1000 union all select ---加了这条
'A', 888 union all select
'A',333 union all select
'B', 12222 union all select
'B', 12000 union all select
'C', 3000 union all select
'C', 2000;set @n=0;
create table kos select *,@n:=@n+1 as id from pt; --建立个辅助表set @id=0;
select customer,
@id:=@id+1 as id1,
max(if(rn=1,price,'')) as price1,
@id:=@id+1 as id2,
max(if(rn=2,price,'')) as price2,
@id:=@id+1 as id3,
max(if(rn=3,price,'')) as price3,
@id:=@id+1 as id4,
max(if(rn=4,price,'')) as price4
from (
select a.customer,a.price,a.id,count(b.id) as rn
from kos a join kos b on a.customer=b.customer and a.id>=b.id
group by a.customer,a.price,a.id )k
group by customer;+----------+------+--------+------+--------+------+--------+------+--------+
| customer | id1 | price1 | id2 | price2 | id3 | price3 | id4 | price4 |
+----------+------+--------+------+--------+------+--------+------+--------+
| A | 1 | 1000 | 2 | 1000 | 3 | 888 | 4 | 333 |
| B | 5 | 12222 | 6 | 12000 | 7 | | 8 | |
| C | 9 | 3000 | 10 | 2000 | 11 | | 12 | |
+----------+------+--------+------+--------+------+--------+------+--------+
这里使用max,不懂,能解释下吗?