也就是说按照type 找最小的value 和最大的time 和小time 然后进行差值？

等等 我去新建个写写

这个，需要做的事是，其实很简单，就是标记，分组，取出time的最大值和最小值对应的value，然后相减

;with cte as 
(select type
    ,value
    ,time
    ,row_number() over(partition by type over by time desc) as id
from table
)
,cte_2 as 
(
select type
    ,max(id) as maxid
    ,min(id) as minid
from cte
group by type
)
select t.type, c2.value-c2.value
from cte_2 t
inner join cte c1
    on t.type=c1.type and t.id=c1.maxid
inner join cte t2
    on t.type=c2.type and t.id=c2.minid

CREATE TABLE #Tab
(
ntype INT ,
nvalue INT ,
ntime DATETIME
)

INSERT INTO #Tab(ntype,nvalue,ntime) VALUES(2,5,'2016-01-01 12:42:00')
INSERT INTO #Tab(ntype,nvalue,ntime) VALUES(4,-42,'2016-01-01 13:42:00')
INSERT INTO #Tab(ntype,nvalue,ntime) VALUES(2,2,'2016-01-01 14:42:00')
INSERT INTO #Tab(ntype,nvalue,ntime) VALUES(2,7,'2016-01-01 12:52:00')
INSERT INTO #Tab(ntype,nvalue,ntime) VALUES(3,16,'2016-01-01 12:42:00')
INSERT INTO #Tab(ntype,nvalue,ntime) VALUES(3,20,'2016-01-01 13:42:00')

SELECT bb.ntype,
SUM(CASE BB.id WHEN 1 THEN BB.nvalue ELSE 0 END) -SUM(CASE BB.id WHEN 2 THEN BB.nvalue ELSE 0 END) num1
FROM
(
SELECT row_number() over(partition by ntype order by ntime desc) as id,ntype,nvalue,ntime
FROM #Tab a WHERE EXISTS(SELECT 1 FROM ( SELECT count(*) AS num,ntype FROM #Tab GROUP BY ntype) AS AA WHERE AA.ntype=a.ntype AND AA.num>=2)
) BB GROUP BY BB.ntype

不知道是要求在oracle下实现还是在MySQL下实现？如果是在oracle下，则可以使用分析函数实现。

因为你给的表的字段在oracle下都是oracle的关键字，所以我建表的时候把字段名都加了"_col"的后缀：

create table t(type_col char(2),  value_col number,  time_col date);

select type_col,
       sum(case when rn = 1 then value_col
                when rn = 2 then 0 - value_col
           end) as result_col
 from
(
select t.type_col,
       t.value_col,t.time_col,
       row_number() over(partition by t.type_col order by t.time_col desc) as rn
  from t
) where rn in (1,2)
group by type_col

时间虽然过去了很久了，但是我看上面的答案中，都是利用row_number() over() 这个函数来处理的，但是，Mysql数据中是没有这个函数的，这里有一个Mysql数据中的实现思路。

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`type` int(11) default NULL,
`value` int(11) default NULL,
`time` time default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('2', '5', '09:02:53');
INSERT INTO `test` VALUES ('4', '-42', '13:20:00');
INSERT INTO `test` VALUES ('2', '2', '14:48:00');
INSERT INTO `test` VALUES ('2', '7', '12:54:00');
INSERT INTO `test` VALUES ('3', '16', '13:19:00');
INSERT INTO `test` VALUES ('3', '20', '15:01:00');

解题思路在于，找到每个type的最近时间，和次近时间，数据如下。