查询某个用户购买金额最大的前三条记录,如下: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没关系,请帮忙一下,谢谢。
解决方案 »
- select a.userid,count(b.userid)
- 谁有叫号系统的标准声音文件
- win7的mysql ERROR #2013 - Lost connection试了很多天都不行,虚心求指点
- MySQL怎么写if语句?
- postgresql 中有没有类似sqlserver中 #table1 或declare @table1 table(id int,age int);的语法
- 请教一条sql语句
- 如何实现mysql里的数据定时更新
- 一个有关远程数据库连接的问题。
- 求一对多,查询最新一条信息SQL
- XAMPP安装sql的时候一直无法启动
- 研究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,不懂,能解释下吗?