SQL查询：交易历史中所有客户所有商品最后一次交易价格

Question

select a.Cust_id,a.Item_code,a.trx_price,a.post_date from trxdtl a where
not exists(select * from trxdtl b where a.Cust_id =b.cust_id and a.Item_code =b.Item_code and b.post_date >a.post_date )

自己搞定了

Answer 1

最后一次交易价格
找日期最大的，如下
select * from wc where date=(select max(date) from wc)

Answer 2

类似这样的语句，按照组来排序
select site_id,account,lev,rank from (
select site_id,account,lev,@rownum:=@rownum+1,if(@pdept=b.site_id,@rank:=@rank+1,@rank:=1) as rank,@pdept:=b.site_id
from (select site_id,account,lev from general
where career='111'
order by site_id,lev desc) b ,
(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result where rank<=1;本回答被提问者采纳