首页 新闻 赞助 找找看

紧急求助SQL 面试题 1小时在线等

0
悬赏园豆:20 [已解决问题] 解决于 2018-06-01 06:28

有张表 

type  value  time

2       5        12:42

4       -42     13:19

2        2        14:48

2        7       12:54

3        16     13:19

3        20     15:01

 

需要按照type排序,返回同一个type的,最近时间和次近时间的两个value的差值

比如 type为2的,最近时间是 14:48,value为2  ,接下来时间是 12:54,value为7

所以差值为 2-7 = -5

 

所以结果是 

type  value

2      -5

3       4

 

求高手帮忙写SQL,小弟怎么都写不出来。。。。

新西兰程序员的主页 新西兰程序员 | 初学一级 | 园豆:3
提问于:2017-03-31 14:08
< >
分享
最佳答案
0


create table test5
(
[type] int not null,
[value] int not null,
[time] time
)

go

INSERT INTO test5 VALUES(2,5,'2:42:00')
INSERT INTO test5 VALUES(4,-42,'3:42:00')
INSERT INTO test5 VALUES(2,2,'4:42:00')
INSERT INTO test5 VALUES(2,7,'2:52:00')
INSERT INTO test5 VALUES(3,16,'2:42:00')
INSERT INTO test5 VALUES(3,20,'3:42:00')

go


with v1 as (
select * from (
select top 100 *, row_number() over ( partition by [type] order by time) as [rank] from test5
) V1 where V1.rank=1)
,v2 as (
select * from (
select top 100 *, row_number() over ( partition by [type] order by time) as [rank] from test5
) V1 where V1.rank=2)
select v1.[type],v1.[value]-v2.[value] from v1 left join v2 on v1.[type]=v2.[type]

收获园豆:10
pengbg | 初学一级 |园豆:13 | 2017-06-08 10:58
其他回答(5)
0

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

等等 我去新建个写写

Sen7 | 园豆:250 (菜鸟二级) | 2017-03-31 14:44

兄弟,写好了吗

 

时间快到了,还有15分钟

支持(0) 反对(0) 新西兰程序员 | 园豆:3 (初学一级) | 2017-03-31 15:11

SELECT A.[event_type] as [event_type],A.[value] as [value],A.[time] as [time]
FROM [MaxMindDB].[dbo].[Test] A
where (select count(*) from [MaxMindDB].[dbo].[Test] B
WHERE A.[event_type] = B.[event_type] and A.[time] < B.[time]

) < 2

 

结果是 

 

event_type    value       time
4                  -42          2015-05-09 13:19:57.000
2                   2             2015-05-09 14:48:57.000
2                   7              2015-05-09 12:54:57.000
3                 16               2015-05-09 13:19:57.000
3                 20                2015-05-09 15:01:57.000

 

兄弟,这是我写的,关键是如何算event_type相同的两条的差值

请务必帮忙啊,还有15分钟。。。。

支持(0) 反对(0) 新西兰程序员 | 园豆:3 (初学一级) | 2017-03-31 15:13

@新西兰程序员: 

SELECT A.type,(CAST(A.`value` as SIGNED)-CAST(C.`value` as SIGNED)) FROM
(SELECT MIN(`value`) as `value`,type FROM memory GROUP BY type) as A LEFT JOIN
(SELECT * FROM
(SELECT MAX(`value`) as `value`,type FROM memory GROUP BY type) as B) AS C ON A.type=C.type

老大叫干事情了,没仔细测试,你的time 是datatime 我以为是varchar的 你在加一个取max最大时间的字段连接用这个的value去减去C就好了

支持(0) 反对(0) Sen7 | 园豆:250 (菜鸟二级) | 2017-03-31 15:20

@新西兰程序员: 

2 2 -5
3 16 -4
4 -42 0

这是结果

支持(0) 反对(0) Sen7 | 园豆:250 (菜鸟二级) | 2017-03-31 15:20

@新西兰程序员: 首先分组  教你找次近时间方法,以time降序排序,用over()取下一行就是次近时间的数据。

example:over(order by time rows next 1 following)

type重复>2 count(*)>2

支持(0) 反对(0) ~扎克伯格 | 园豆:1923 (小虾三级) | 2017-03-31 15:22
0

这个,需要做的事是,其实很简单,就是标记,分组,取出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

 

 

收获园豆:5
悦光阴 | 园豆:2251 (老鸟四级) | 2017-03-31 16:40
0

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

 

收获园豆:5
咖啡不会醉 | 园豆:209 (菜鸟二级) | 2017-05-05 17:48

虽然来不及了,不过求采纳

支持(0) 反对(0) 咖啡不会醉 | 园豆:209 (菜鸟二级) | 2017-05-05 17:49
0

不知道是要求在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

铁皮人 | 园豆:202 (菜鸟二级) | 2018-06-01 09:33
0

时间虽然过去了很久了,但是我看上面的答案中,都是利用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的最近时间,和次近时间,数据如下。

type       last_time        second_time
2            14:48:00         12:54:00
3            15:01:00         13:19:00
4            13:20:00         13:20:00
利用Mysql中的group_concat()函数可以实现:
create view t_view as 
select type,SUBSTRING_INDEX(cast(group_concat(time order by time desc) as char),',',1) last_time,
SUBSTRING_INDEX(SUBSTRING_INDEX(cast(group_concat(time order by time desc) as char),',',2),',',-1) second_time
from test
group by type
有了 t_view这个视图,就很容易处理了。利用原表与视图做两次表关联查询,分别获取每个type中最近时间的value和次近时间的value,再取差值,最终如下:

select t1.type,t1.`value` - t2.`value` value_diff from
(select test.type,`value` from test,t_view where test.type = t_view.type and test.time = test_view.last_time) t1,
(select test.type,`value` from test,t_view where test.type = t_view.type and test.time = t_view.second_time) t2
where t1.type = t2.type

--------------------------------------------------------------------------------------------------------------
这种问题,实际应用中,可以看做,取各个市场,最近两天时间内的交易差额。
想和大蒜在一起的瘦肉 | 园豆:202 (菜鸟二级) | 2018-06-01 11:37

select t1.type,t1.`value` - t2.`value` value_diff from 
(select test.type,`value` from test,t_view where test.type = t_view.type and test.time = t_view.last_time) t1,
(select test.type,`value` from test,t_view where test.type = t_view.type and test.time = t_view.second_time) t2
where t1.type = t2.type

----------------------------------------------------------------------------------------------

最后的sql的视图名需要修改一下

清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册