求个sql语句,具体如下

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

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

10个回答

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

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

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

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

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

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

重新回复一下:
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

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

这种也可以
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
Baymax0532 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
接近 2 年之前 回复

SELECT
*
FROM
price wp
WHERE
3 > (
SELECT
COUNT(*)
FROM
price
WHERE
productid = wp.productid
AND
pricedate > wp.pricedate
)
ORDER BY
wp.productid ASC,
wp.pricedate DESC

通过查资料和同事讨论,商量出了这个sql,后来又查到了一个,不知道有没有大神可以一块讨论讨论

SELECT
a.productid, a.pricedate

FROM
w_price a
LEFT JOIN
w_price b
ON
a.productid = b.productid
AND
a.pricedate <= b.pricedate
GROUP BY
a.productid, a.pricedate
HAVING
COUNT(b.pricedate) <= 7

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!