SQL查询以提取显示的表和数据的速率和开始和结束日期 [英] SQL Query for extracting rate and date started and ended for the table and data shown
问题描述
我今天进行了一次采访,并且进行了一次动手测试,其中包含以下费率和日期"列.问题是编写一个查询,以输出该汇率的汇率,开始日期和结束日期.速率重复多次,在下面的示例中,在4.5或5行或更高行之后,3.5可以再次出现;
I had an interview today and there was a hands on test which had the following Rate and Date column. The question was to write a query that would output the rate, date started and date ended for that rate. Rates are repeated many times and in the example below, 3.5 can appear again after 4.5 or 5 or later rows;
Rate Date
3.5 20/01/2014
3.5 18/02/2014
.
.
3.5 20/03/2015
4.5 14/04/2015
4.5 19/05/2015
.
.
4.9 30/05/2015
3.5 28/06/2015
我不知道该怎么做,但是正在使用Window函数,但是访问者对我的方法并不满意:(请有人帮我理解对此的查询吗?
I didn't know how to, but was playing around Window functions, but the interviewers weren't looking satisfied with my approach :( Please someone help me understand a query for this?
我认为该问题的预期输出结果如下(如果我没记错的话)
I think that question had expected output results as follows (if i remember correctly)
Rate StartEndDate
3.5 20/01/2014
3.5 28/06/2015
4.5 14/04/2015
4.5 19/05/2015
推荐答案
对于您建议的输出,它不只是:
for the output you are suggesting is it not just:
select
rate
, min(date)
from
rnd
group by
rate
union
select
rate
, max(date)
from
rnd
group by
rate
例如: http://data.stackexchange.com/stackoverflow/query/750504/max-and-min-in-the-same-column
这篇关于SQL查询以提取显示的表和数据的速率和开始和结束日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!