本文介绍了sql server日期范围之间的价格差异的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
price volume mId Dated
574.07 16106 1 2012-12-18
572.94 15044 1 2012-12-18
576.78 14197 1 2012-12-18
999.28 39669 2 2012-12-19
999.67 37937 2 2012-12-19
1006.89 37274 2 2012-12-19
870.91 56737 3 2012-12-20
865.31 49047 3 2012-12-20
865.00 43507 3 2012-12-20
实际上我需要从2012-12-18到2012-12-20的最佳mID。意味着我需要价格差异的东西,以便我得到哪个类别做得最好的百分比。
请帮助我这个感谢表名称是.. Marketstock
Actually i need the best mID from dated 2012-12-18 to 2012-12-20. Means i need to something with price difference so that i get percentage of which category doing best.
please help me on this thanks table name is .. Marketstock
推荐答案
select max(price) - min(price)
from table
where Dated between '18 Dec 2012' and '19 Dec 2012'
With Cte as
(Select mid,MAX(price)-MIN(price) as diff from test group BY mid,dated)
SELECT mid from cte where DIFF=(select MAX(diff) from cte)
我的表和样本插页
My table and sample insert
CREATE table test
(Price decimal(18,2),
volume int,
mid int,
dated datetime)
INSERT INTO test values
(574.07, 16106, 1, '' 2012-12-18''),
(572.94, 15044 , 1, ''2012-12-18''),
(576.78 , 14197, 1 , ''2012-12-18''),
(999.28 , 39669, 2, ''2012-12-19''),
(999.67 , 37937 , 2, ''2012-12-19''),
(1006.89 , 37274 , 2, ''2012-12-19''),
(870.91 , 56737, 3 , ''2012-12-20''),
(865.31 , 49047, 3 , ''2012-12-20''),
(865.00 , 43507, 3 , ''2012-12-20'')
这篇关于sql server日期范围之间的价格差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!