版块导航: 正在加载中...

登录注册

应《网络安全法》要求，自2017年10月1日起，未进行实名认证将不得使用互联网跟帖服务。为保障您的帐号能够正常使用，请尽快对帐号进行手机号验证，感谢您的理解与支持！

24小时热门版块排行榜

返回列表

xumiao1985

银虫 (小有名气)

应助: 8 (幼儿园)
金币: 382.2
红花: 1
帖子: 88
在线: 106.5小时
虫号: 1831121
注册: 2012-05-23
专业: 拓扑学

[求助] 感谢各位朋友帮忙优化下，运行时间实在太长了已有1人参与

use zj_sw1
select a1.dtrn+a2.dtrn+a3.dtrn+a4.dtrn+a5.dtrn+a6.dtrn+a7.dtrn+a8.dtrn+a9.dtrn+a10.dtrn+a11.dtrn+a12.dtrn+a13.dtrn+a14.dtrn+a15.dtrn+a16.dtrn+a17.dtrn+a18.dtrn+a19.dtrn+a20.dtrn+a21.dtrn+a22.dtrn+a23.dtrn+a24.dtrn,a1.ymdhm,a1.stcdt from
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a1,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a2,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a3,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a4,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a5,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a6,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a7,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a8,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a9,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a10,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a11,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a12,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a13,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a14,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a15,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a16,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a17,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a18,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a19,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a20,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a21,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a22,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a23,
(select ymdhm,dtrn,stcdt from st_rnfl_r where ymdhm>='2015-4-15 8:00:00.000' and ymdhm<='2015-7-1 0:00:00.000' ) as a24
where a1.stcdt=a2.stcdt and a1.stcdt=a3.stcdt and a1.stcdt=a4.stcdt and a1.stcdt=a5.stcdt and a1.stcdt=a6.stcdt and a1.stcdt=a7.stcdt and a1.stcdt=a8.stcdt and a1.stcdt=a9.stcdt and a1.stcdt=a10.stcdt and a1.stcdt=a11.stcdt and a1.stcdt=a12.stcdt and a1.stcdt=a13.stcdt and a1.stcdt=a14.stcdt and a1.stcdt=a15.stcdt and a1.stcdt=a16.stcdt and a1.stcdt=a17.stcdt and a1.stcdt=a18.stcdt and a1.stcdt=a19.stcdt and a1.stcdt=a20.stcdt and a1.stcdt=a21.stcdt and a1.stcdt=a22.stcdt and a1.stcdt=a23.stcdt and a1.stcdt=a24.stcdt and datediff(hh,a1.ymdhm,a2.ymdhm)=1 and datediff(hh,a2.ymdhm,a3.ymdhm)=1 and datediff(hh,a3.ymdhm,a4.ymdhm)=1 and datediff(hh,a4.ymdhm,a5.ymdhm)=1 and datediff(hh,a5.ymdhm,a6.ymdhm)=1 and datediff(hh,a6.ymdhm,a7.ymdhm)=1 and datediff(hh,a7.ymdhm,a8.ymdhm)=1 and datediff(hh,a8.ymdhm,a9.ymdhm)=1 and datediff(hh,a9.ymdhm,a10.ymdhm)=1 and datediff(hh,a10.ymdhm,a11.ymdhm)=1 and datediff(hh,a11.ymdhm,a12.ymdhm)=1
and datediff(hh,a12.ymdhm,a13.ymdhm)=1 and datediff(hh,a13.ymdhm,a14.ymdhm)=1 and datediff(hh,a14.ymdhm,a15.ymdhm)=1 and datediff(hh,a15.ymdhm,a16.ymdhm)=1 and datediff(hh,a16.ymdhm,a17.ymdhm)=1 and datediff(hh,a17.ymdhm,a18.ymdhm)=1 and datediff(hh,a18.ymdhm,a19.ymdhm)=1 and datediff(hh,a19.ymdhm,a20.ymdhm)=1 and datediff(hh,a20.ymdhm,a21.ymdhm)=1 and datediff(hh,a21.ymdhm,a22.ymdhm)=1 and datediff(hh,a22.ymdhm,a23.ymdhm)=1 and datediff(hh,a23.ymdhm,a24.ymdhm)=1
order by a1.dtrn+a2.dtrn+a3.dtrn+a4.dtrn+a5.dtrn+a6.dtrn+a7.dtrn+a8.dtrn+a9.dtrn+a10.dtrn+a11.dtrn+a12.dtrn+a13.dtrn+a14.dtrn+a15.dtrn+a16.dtrn+a17.dtrn+a18.dtrn+a19.dtrn+a20.dtrn+a21.dtrn+a22.dtrn+a23.dtrn+a24.dtrn desc

回复此楼

» 猜你喜欢

最失望的一年已经有3人回复
拟解决的关键科学问题还要不要写已经有7人回复
存款400万可以在学校里躺平吗已经有20人回复
国自然申请面上模板最新2026版出了吗？已经有19人回复
请教限项目规定已经有3人回复
基金委咋了？2026年的指南还没有出来？已经有10人回复
基金申报已经有6人回复
推荐一本书已经有13人回复
疑惑？已经有5人回复
溴的反应液脱色已经有7人回复

1楼 2015-08-05 00:11:26

已阅回复此楼关注TA 给TA发消息送TA红花 TA的回帖

jianliu67

木虫 (小有名气)

应助: 27 (小学生)
金币: 4053.5
红花: 4
帖子: 216
在线: 135.7小时
虫号: 3511817
注册: 2014-11-01
性别: GG
专业: 计算机科学的基础理论

【答案】应助回帖

感谢参与，应助指数 +1

感觉你是想把相同的stcdt, 每24小时内相隔1小时的dtrn取和. 你最好能提供一些原始数据的样本, 就我的感觉而言, 不同的数据有不同的查询方法.

赞一下

回复此楼

2楼2015-08-05 09:32:26

已阅回复此楼关注TA 给TA发消息送TA红花 TA的回帖

xumiao1985

银虫 (小有名气)

应助: 8 (幼儿园)
金币: 382.2
红花: 1
帖子: 88
在线: 106.5小时
虫号: 1831121
注册: 2012-05-23
专业: 拓扑学

引用回帖:

2楼: Originally posted by jianliu67 at 2015-08-05 09:32:26
感觉你是想把相同的stcdt, 每24小时内相隔1小时的dtrn取和. 你最好能提供一些原始数据的样本, 就我的感觉而言, 不同的数据有不同的查询方法.

大神啊，就是这个意思，表是st_rnfl_r表内资料如下
STCDT YMDHM DTRN
71311 2014-12-30 01:00:00.000 0
71903 2014-12-30 01:00:00.000 0
71904 2014-12-30 01:00:00.000 0
71907 2014-12-30 01:00:00.000 0
71911 2014-12-30 01:00:00.000 0
71912 2014-12-30 01:00:00.000 0
71916 2014-12-30 01:00:00.000 0
71917 2014-12-30 01:00:00.000 0

赞一下

回复此楼

3楼2015-08-05 10:55:09

已阅回复此楼关注TA 给TA发消息送TA红花 TA的回帖

jianliu67

木虫 (小有名气)

应助: 27 (小学生)
金币: 4053.5
红花: 4
帖子: 216
在线: 135.7小时
虫号: 3511817
注册: 2014-11-01
性别: GG
专业: 计算机科学的基础理论

【答案】应助回帖

就像我说的, 不同的数据有不同的查询方法.
再问几个问题:
1. YMDHM 总是取整小时吗?
2. 对每个STCDT, 每个小时整总是有数据吗?
3. 如果对某个STCDT, 在24小时内不总是有数据, 你也希望求和吗?

看你的语法, 你的数据库应该是MS SQL Server吧? 哪个版本?

赞一下

回复此楼

4楼2015-08-05 11:12:47

已阅回复此楼关注TA 给TA发消息送TA红花 TA的回帖

xumiao1985

银虫 (小有名气)

应助: 8 (幼儿园)
金币: 382.2
红花: 1
帖子: 88
在线: 106.5小时
虫号: 1831121
注册: 2012-05-23
专业: 拓扑学

引用回帖:

4楼: Originally posted by jianliu67 at 2015-08-05 11:12:47
就像我说的, 不同的数据有不同的查询方法.
再问几个问题:
1. YMDHM 总是取整小时吗?
2. 对每个STCDT, 每个小时整总是有数据吗?
3. 如果对某个STCDT, 在24小时内不总是有数据, 你也希望求和吗?

看你的语法, 你 ...

2008，ymdhm取的是整点的，每个STCDT都是有数据的

赞一下

回复此楼

5楼2015-08-05 11:22:37

已阅回复此楼关注TA 给TA发消息送TA红花 TA的回帖

jianliu67

木虫 (小有名气)

应助: 27 (小学生)
金币: 4053.5
红花: 4
帖子: 216
在线: 135.7小时
虫号: 3511817
注册: 2014-11-01
性别: GG
专业: 计算机科学的基础理论

【答案】应助回帖

★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ...
xumiao1985: 金币+100, ★★★★★最佳答案, 我自己编的要2个小时多点，你的20分钟 2015-08-05 12:30:25

试试这个:

SELECT t1.stcdt, t1.ymdhm, SUM(t2.dtrn)
FROM st_rnfl_r t1 JOIN st_rnfl_r t2 ON t1.stcdt = t2.stcdt AND t2.ymdhm BETWEEN t1.ymdhm AND DATEADD(hh, 23, t1.ymdhm)
WHERE t1.ymdhm BETWEEN '2015-4-15 8:00:00.000' AND '2015-7-1 0:00:00.000'
GROUP BY t1.stcdt, t1.ymdhm
ORDER BY t1.stcdt, t1.ymdhm

赞一下

回复此楼

6楼2015-08-05 11:28:15

已阅回复此楼关注TA 给TA发消息送TA红花 TA的回帖