这个SQL怎么写？
 |
wusuoshishi
2009-02-12
--第一大
select id, price,max(pdate) from goods group by id --第二大 select id,price,max(pdate) from goods g2 where pdate<(select id,max(pdate) from goods where id=g2.id) group by id --最终 select max1.id,(max1.price-max.price) from max1，max2 where max1.id=max2.id and row<10 --如果是ORCLE的话可以用RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 去取 |
|
sunsong
2009-02-13
一句话可以搞定。
select a.* from ( select goods_id,max(price)-min(price) as difference from table1 group by goods_id ) a order by a.difference desc limit 10 limit 10为mysql语法，其他数据库自己改写一下 |
|
sunsong
2009-02-13
更简洁的做法，用一层查询：
select goods_id,max(price)-min(price) as difference from table1 group by goods_id order by difference desc limit 10 |
 |
zhzhxh
2009-02-13
sunsong 写道 更简洁的做法，用一层查询：
select goods_id,max(price)-min(price) as difference from table1 group by goods_id order by difference desc limit 10 sorry, 可能是我没说清楚，差值不是最大和最小之间的，是最新的价格跟前一次价格的差。 |
 |
honda418
2009-03-05
sunsong 写道 一句话可以搞定。
select a.* from ( select goods_id,max(price)-min(price) as difference from table1 group by goods_id ) a order by a.difference desc limit 10 limit 10为mysql语法，其他数据库自己改写一下 一次的变化量啊，你这是一件商品的总的价格变化量 |
 |
MyDicta
2011-06-16
你试试是不是这个意思 SELECT *
FROM (SELECT GOODS_ID, MAX(P_DIFF)
FROM (SELECT T.*,
ABS(T.PRICE - LEAD(T.PRICE, 1)
OVER(PARTITION BY T.GOODS_ID ORDER BY T.DATES DESC
NULLS LAST)) AS P_DIFF
FROM GOODS T) T
GROUP BY T.GOODS_ID
ORDER BY MAX(P_DIFF) DESC NULLS LAST)
WHERE ROWNUM <= 10
|
|
XiaoFan012
2011-07-15
有个思路，大概分两步处理：
1.建立一个表GOOD_NEW，结构如下 goods_id | price | date | rn A | 12.0 | 01-12| 1 A | 14.0 | 01-15| 2 A | 18.0 | 01-20| 3 B | 10.0 | 01-11| 1 B | 8.0 | 01-20| 2 它是group by goods_id,order by date的 2.建立查询 select top 10 a.good_id,abs(a.price-b.price) as price from good_new a, good_new b where (a.goods_id=b.goods_id) and (a.rn= b.rn- 1) |
|
lmhcgd
2011-07-21
select tt.t1,tt.t4,tt.m2,tt.m1,(tt.m2-tt.m1) from (select t1,t4, min(t4) keep (dense_rank first order by t4 ) over(partition by t1) m1,
max(t4) keep (dense_rank first order by t4 desc ) over(partition by t1) m2 from test) tt order by (tt.m2-tt.m1) desc |
