27,582
社区成员
发帖
与我相关
我的任务
分享求查询连续五天大于某数值的语句
sql数据库里有两个字段名，一个是日期，一个是温度，每天一个数据记录当天的温度
现在想要得到“连续五天大于某个温度（比如大于10）的起始日期”
能否用一句sql语句实现呢
现在想要得到“连续五天大于某个温度（比如大于10）的起始日期”
能否用一句sql语句实现呢
...全文
请发表友善的回复…
发表回复
snail_t 2014-04-24
- 打赏
- 举报
感谢tsp3ng的帮助，表里没有索引，先给建立了临时表，然后按照代码就可以得到想要的结果了，谢谢
guguda2008 2014-04-18
- 打赏
- 举报
这语句执行起来超时了，是不是效率不够高[/quote]
你这个需求，没有高效率的执行方式，除非加索引或者冗余列
snail_t 2014-04-18
- 打赏
- 举报
求指导，远程协助也可以的，我的QQ是81850685
snail_t 2014-04-18
- 打赏
- 举报
从53年到现在，一天一条记录，一共两万多条记录了
tsp3ng 2014-04-09
- 打赏
- 举报
有多少条记录，日期，温度有没有索引？
snail_t 2014-04-09
- 打赏
- 举报
这语句执行起来超时了，是不是效率不够高
tsp3ng 2014-04-07
- 打赏
- 举报
这条语句没有问题，而且效率还可以的。
snail_t 2014-04-07
- 打赏
- 举报
这条语句好像不行
QQ83707594 2014-04-03
- 打赏
- 举报
学习了tsp3ng 2014-04-03
- 打赏
- 举报
drop table #tmp
create table #tmp(日期 datetime,日平均温度 decimal(20,2))
DELETE #tmp
insert into #tmp
select '1953-1-1',6 union all
select '1953-1-2',16 union all
select '1953-1-3',15.4 union all
select '1953-1-4',15.8 union all
select '1953-1-5',15.2 union all
select '1953-1-6',15 union all
select '1953-1-7',16.2 union all
select '1953-1-8',5.1 union all
select '1953-1-9',18.7 union all
select '1953-1-10',12.1 union all
select '1953-1-11',12.2 union all
select '1953-1-12',14.7 union all
select '1953-1-13',11.7
select min([日期]) as bgdate, max([日期]) as eddate, count(1) as maxdays
from
(select [日期], datediff(dd, '1900-01-01', a.[日期]) - (select count(1) from #tmp b where [日平均温度] >= 10 and datediff(dd, b.[日期], a.[日期]) >= 0) as maxdays
from #tmp a
where [日平均温度] >= 10) a
group by maxdays
order by maxdays
snail_t 2014-04-01
- 打赏
- 举报
好像可以诶，如果是按年呢？就是要查每年中连续五天大于某个温度（比如大于10）的起始日期，这个该怎么改进呢
开心美颜 2014-03-27
- 打赏
- 举报
HeavenInShanghai 2014-03-18
- 打赏
- 举报
最后你的查询就会变成
ＳELECT 日期
WHERE 温度> 10 and 明日温度>10 and 后日温度>10 and 3天后温度>10 and 4天后温度>10 and 5天后温度>10
HeavenInShanghai 2014-03-18
- 打赏
- 举报
简单的行转列例子,只是个思路模板，不好意思没有细写，但是希望你能理解这个思路，可以应用到很多场景：
SELECT cur.date, cur.temp，prv.date,prv.temp, 2d.date,2d.temp,3d.date,3d.temp,4d.date,4d.temp,5d.date,5d.temp
FROM table cur
INNER JOIN table nxt ON cur.date = prv.date -1
INNER JOIN table 2d ON cur.date = 2d.date -2
INNER JOIN table 2d ON cur.date = 3d.date -3
INNER JOIN table 2d ON cur.date = 4d.date -4
INNER JOIN table 2d ON cur.date = 5d.date -5
HeavenInShanghai 2014-03-18
- 打赏
- 举报
抛砖引玉：可以转换成数据仓库的fact 表，通过行专列，将你的原表结构变成如下（缓存到temp table或者 WITH TABLE)：
日期，温度，明日，明日温度，后日，后日温度，3天后时间，3天后温度，4天后时间，4天后温度，5天后时间，5天后温度
这样你的需求在报表查询层面上会变得异常的简单。
xxfvba 2014-03-18
- 打赏
- 举报
with T(Dat,tem) as
(select '1953-1-1',6 union all
select '1953-1-2',16 union all
select '1953-1-3',15.4 union all
select '1953-1-4',15.8 union all
select '1953-1-5',15.2 union all
select '1953-1-6',15 union all
select '1953-1-7',16.2 union all
select '1953-1-8',5.1 union all
select '1953-1-9',18.7 union all
select '1953-1-10',12.1 union all
select '1953-1-11',12.2 union all
select '1953-1-12',14.7 union all
select '1953-1-13',11.7)
select * from
(select min(convert(datetime,dat)) a,max(convert(datetime,dat)) b from
(select Dat,datediff(dd,dat,getdate())+row_number() over (order by convert(datetime,dat)) as grp from T where tem>10) a
group by grp) a where datediff(d,a,b)>=5
lzw_0736 2014-03-18
- 打赏
- 举报
6樓第二行是多余的，應該去掉.
lzw_0736 2014-03-18
- 打赏
- 举报
create table #tmp(日期 varchar(20),日平均温度 decimal(20,2));
DELETE #tmp
insert into #tmp
select '1953-1-1',6 union all
select '1953-1-2',16 union all
select '1953-1-3',15.4 union all
select '1953-1-4',15.8 union all
select '1953-1-5',15.2 union all
select '1953-1-6',15 union all
select '1953-1-7',16.2 union all
select '1953-1-8',5.1 union all
select '1953-1-9',18.7 union all
select '1953-1-10',12.1 union all
select '1953-1-11',12.2 union all
select '1953-1-12',14.7 union all
select '1953-1-13',11.7
--取出连续n天大于指定温度C的时间段
declare @n int; set @n=5;
declare @C decimal(20,2); set @C=10;
WITH a1 AS
(
SELECT *,case when 日平均温度>@C then 1 else 0 end as IsOver,
ROW_NUMBER() OVER(ORDER BY CAST(日期 AS DATETIME)) re
FROM #tmp
)
,a2 AS
(
SELECT *,re-ROW_NUMBER() OVER(PARTITION BY IsOver ORDER BY re) re2
FROM a1
)
SELECT MIN(日期) StartDate,max(日期) as EndDate
FROM a2
GROUP BY re2
HAVING COUNT(*)>=@n
frogley 2014-03-17
- 打赏
- 举报
字段类型忘了说了，日期是按年月日的形式记录的，字符串类型
年月日 日平均温度
1953-1-1 6
1953-1-2 6
1953-1-3 5.4
1953-1-4 5.8
1953-1-5 5.2
1953-1-6 5
1953-1-7 6.2
1953-1-8 5.1
1953-1-9 18.7
1953-1-10 12.1
1953-1-11 12.2
1953-1-12 14.7
1953-1-13 1.7
诸如此类[/quote]
--测试数据
create table #tmp(日期 varchar(20),日平均温度 decimal(20,2));
insert into #tmp
select '1953-1-1',6 union all
select '1953-1-2',6 union all
select '1953-1-3',5.4 union all
select '1953-1-4',5.8 union all
select '1953-1-5',5.2 union all
select '1953-1-6',5 union all
select '1953-1-7',6.2 union all
select '1953-1-8',5.1 union all
select '1953-1-9',18.7 union all
select '1953-1-10',12.1 union all
select '1953-1-11',12.2 union all
select '1953-1-12',14.7 union all
select '1953-1-13',1.7
--取出连续n天大于指定温度C的时间段
declare @n int; set @n=4;
declare @C decimal(20,2); set @C=10;
select min("date") as StartDate,max("date") as EndDate from (
select "date",IsOver
,row_number() over (order by "date")
-row_number() over (partition by IsOver order by "date") as grp
from (
select cast(日期 as datetime) as "date"
,case when 日平均温度>@C then 1 else 0 end as IsOver
from #tmp
) as t1
) as t2
where IsOver=1
group by grp
having COUNT(*)>=@n
唐诗三百首 2014-02-28
- 打赏
- 举报
try this,
select a.日期 '起始日期'
from [表名] a
where not exists
(select 1 from [表名] b
where datediff(d,a.日期,b.日期)<=5 and b.温度<10)
加载更多回复（3）
