mysql数据库
产品价格表 price，
字段：id, productid, price, pricedate
表里存的是多个产品在不同时间的价格，
比如产品p1,
1 p1 203.00 2017-08-21
2 p1 251.00 2017-08-30
希望写出一个sql，可以按照报价日期降序排列，每个产品取7条数据
也就是每个产品取最新的七条价格
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 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
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
*
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