2 cool0532 cool0532 于 2017.09.19 10:16 提问

求个sql语句,具体如下

mysql数据库
产品价格表 price,
字段:id, productid, price, pricedate
表里存的是多个产品在不同时间的价格,
比如产品p1,
1 p1 203.00 2017-08-21
2 p1 251.00 2017-08-30

希望写出一个sql,可以按照报价日期降序排列,每个产品取7条数据
也就是每个产品取最新的七条价格

11个回答

jtrsreumf
jtrsreumf   2017.09.19 10:43

select * from price order by pricedate desc limit 0,7;

jtrsreumf
jtrsreumf select * from price a where instr( (select GROUP_CONCAT(b.ids) from (select SUBSTRING_INDEX(GROUP_CONCAT(p.id order by p.pricedate desc),',',7) as ids from price p group by p.productid) b) , a.id)>0
2 个月之前 回复
jtrsreumf
jtrsreumf 前面没看清。
2 个月之前 回复
kingslave1
kingslave1 他是要分产品的呀,你这样只是查出最新的7条数据吧
2 个月之前 回复
u011924665
u011924665   2017.09.19 10:37

select productid,price from (select productid,price,row_number() over(partition by productid order by price) rownumb from price) whre rownumb<=7

Oh_my_godness
Oh_my_godness   2017.09.19 10:41

mysql数据库 进行操作!!!直接先查询全部的在排序,最后进行分页查询
SELECT * FROM tableName ORDER BY 排序的字段 LIMIT 分页起始页码,每页显示多少条
比如SELECT * FROM test ORDER BY pricedate LIMIT 0,7

Oh_my_godness
Oh_my_godness   2017.09.19 10:43

显示什么字段在最前面写什么字段就可以

m0_37462976
m0_37462976   2017.09.19 10:55

考虑两点:
1.前七条
2.每个产品---->说明产品不能重复的
select distanct(r.id ,r.productid,r.price,r.pricedate) from price r desc limit 0,6;

weixin_38177163
weixin_38177163   2017.09.19 11:23

select * from (select productid,pricedate,row_number() over (partition by productid order by pricedate desc) num from price)t where t.num<=7

qq_28546593
qq_28546593   2017.09.19 11:25

SELECT
productid,
price
FROM
(
SELECT
productid,
price,
row_number () over (
PARTITION BY productid
ORDER BY
price
) rownumb
FROM
price
) whre rownumb <= 7

jtrsreumf
jtrsreumf   2017.09.19 16:17

重新回复一下:
select * from price a where instr(
(select GROUP_CONCAT(b.ids) from
(select SUBSTRING_INDEX(GROUP_CONCAT(p.id order by p.pricedate desc),',',7) as ids from price p group by p.productid) b)
, a.id)>0

guanfengliang1988
guanfengliang1988   2017.09.19 16:26

SELECT * FROM price a

WHERE (SELECT count(*) FROM price b WHERE b.productid=a.productid AND b.id>a.id )<7

ORDER BY pricedate desc

jtrsreumf
jtrsreumf   2017.09.19 16:26

这种也可以
select a.* from price a,
(select s.productid,SUBSTRING_INDEX(GROUP_CONCAT(p.id order by p.pricedate desc),',',7) as ids from price p group by p.productid) b
where a.productid = b.productid and instr(b.ids, a.id)>0

cool0532
cool0532 SELECT * FROM w_price wp WHERE 3 > ( SELECT COUNT(*) FROM w_price WHERE productid = wp.productid AND pricedate > wp.pricedate ) ORDER BY wp.productid ASC, wp.pricedate DESC
大约一个月之前 回复
共11条数据 1 尾页
Csdn user default icon
上传中...
上传图片
插入图片