求一个sql语句，mysql的，具体要求如下

有两个表，product表，id, productid, productname
price表，id, productid, price, pricedate
希望写一个sql，查出每个产品的productid,productname, 最新的价格lastprice，
以及最新价格比次新价格的涨幅difference

select a.productid,a.productname,b.price from product as a left join price as b where a.productid=b.productid
价格那个不知道是如何统计计算的

建price表的时候多一个lastprice会省多少事！

select productid,productname, lastprice from product,price where product.productid==price.product.productid
differce=lastprice-price

select a.productid,a.productname,b.price from product as a left join price as b where a.productid=b.productid

select a.productid,a.productname,b.price from product as a left join price as b where a.productid=b.productid;

select a.productid,a.productname,b.price from product as a left join price as b where a.productid=b.productid;
联表查询

select t.* from (select k.*, row_number() over(partition by k.productid order by k.pricedate desc) rw from price k) t where t.rw = 1 这句sql的意思是从price这个表里查出每个productid中日期最新的一条

关联查询可以这样写
select p.productid,p.productname,pr.price lastprice,pr.price-pr2.price cixinprice from product p
left join (select t.price,t.productid from (select k.*, row_number() over(partition by k.productid order by k.pricedate desc) rw from price k) t where t.rw = 1) pr on pr.productid = p.productid

left join (select t.price ,t.productid from (select k.*, row_number() over(partition by k.productid order by k.pricedate desc) rw from price k) t where t.rw = 2) pr2 on pr2.productid = p.productid

查出每个产品的productid,productname, 最新的价格lastprice
SELECT a.*
FROM (
SELECT pd.productid , pd.productname , pc.price AS 'lastprice'
FROM product pd LEFT JOIN price pc
ON pd.id = pc.productid
ORDER BY pc.pricedate DESC
) a
GROUP BY a.productid

再加上涨幅
SELECT b.* ,
CASE WHEN b.lastprice > pi.price THEN CONCAT(CONVERT((b.lastprice-pi.price)*100/pi.price,DECIMAL(10,2)), '%')
END AS 'difference'
FROM (
SELECT a.*
FROM (
SELECT pd.productid , pd.productname , pc.price AS 'lastprice'
FROM product pd LEFT JOIN price pc
ON pd.id = pc.productid
ORDER BY pc.pricedate DESC
) a
GROUP BY a.productid
) b LEFT JOIN price pi
ON b.productid = pi.productid
GROUP BY b.productid

可能方法笨了点，望不吝赐教

select p.productid,p.productname,aa.lastprice,(aa.lastprice - bb.price) difference from
(select i.price lastprice,i.productid
from price i,
(select max(c.pricedate) pricedate,c.productid
from price c
group by c.productid
) m //根据商品分组，查询出最近日期的商品id和日期
where i.pricedate = m.pricedate and i.productid = m.productid
) aa //查询出最近日期的商品id，价格
,
(select max(o.pricedate) pricedate,o.price,o.productid
from price o,
(select max(c.pricedate) pricedate,c.productid
from price c
group by c.productid
) k //根据商品分组，查询出最近日期的商品id和日期
where o.productid = k.productid and o.pricedate != k.pricedate
) bb //查询出排除最近日期的最大日期的商品id，价格
,
product p
where aa.productid = bb.productid
and aa.productid = p.productid

select
t.productid,
t.productname,
p.price as lastprice //最新的价格
(select max(id) from price) limit 1,1 - p.price as difference //涨幅
from product t
left join price p on p.productid = t.productid
where p.id = (select max(id) from price) //一般来说最新的id对应最新的价格
大概是应该这样的

考虑到id是自增id

SELECT b.*,c.price AS lastprice,c.price-d.price AS difference FROM 
(
    SELECT productid,MAX(id) AS maxid,MIN(id) AS minid FROM 
    (
        SELECT  a.`id`,  a.productid,  a.`price`,  a.`pricedate` FROM  `price` a 
        WHERE 2 >  (SELECT COUNT(*) FROM price WHERE productid = a.productid AND id > a.id)
        ORDER BY a.productid,  a.id DESC
    ) a GROUP BY productid
) a
LEFT OUTER JOIN product b ON a.productid=b.productid
LEFT OUTER JOIN price c ON (a.productid=c.productid AND a.maxid=c.`id`)
LEFT OUTER JOIN price d ON (a.productid=d.productid AND a.minid=d.`id`)

原始数据

 SELECT temp2.productid,temp2.productname,temp2.price,temp2.pricedate,GROUP_CONCAT(temp2.difference) 涨幅 FROM
  (SELECT temp1.productid,temp1.productname,temp1.price,temp1.pricedate,temp1.rn,temp1.difference FROM
    (SELECT
      p.productid,
      p.productname,
      m.price,
      m.pricedate,
      @rn := IF(@prev = m.productid, @rn + 1, 1) AS rn,
      IF(@prev = m.productid, CONVERT((@firstprice-m.price)*100/m.price,DECIMAL(10,2)), NULL) AS difference, -- 我在这里拼接了%导致排序错误
      @firstprice := IF(@prev = m.productid, 0, m.price) AS firstprice,
      @prev := m.productid
    FROM
      (SELECT * FROM price ORDER BY productid DESC, pricedate DESC) m, product p
      JOIN (SELECT @prev := NULL, @rn := 1) AS vars WHERE m.productid = p.productid) temp1
  WHERE rn <= 2 ORDER BY temp1.productid,temp1.rn DESC
  ) temp2 GROUP BY temp2.productid

执行结果

见一个视图就好了， 显示一部分， 之后在联立另外一张表