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

24小时热门版块排行榜    

小木虫论坛-学术科研互动平台 » 计算模拟区 » 程序语言 » 数据库 » 感谢各位朋友帮忙优化下，运行时间实在太长了
北京石油化工学院2026年研究生招生接收调剂公告
71/1返回列表
查看: 1004  |  回复: 6
只看楼主 @他人 存档 新回复提醒 (忽略) 收藏    在APP中查看

xumiao1985

银虫 (小有名气)

[求助] 感谢各位朋友帮忙优化下，运行时间实在太长了 已有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
回复此楼

» 猜你喜欢
1楼 2015-08-05 00:11:26
已阅   回复此楼   关注TA 给TA发消息 送TA红花 TA的回帖

jianliu67

木虫 (小有名气)

【答案】应助回帖

感谢参与，应助指数 +1
感觉你是想把相同的stcdt, 每24小时内相隔1小时的dtrn取和. 你最好能提供一些原始数据的样本, 就我的感觉而言, 不同的数据有不同的查询方法.
赞一下 回复此楼
2楼2015-08-05 09:32:26
已阅   回复此楼   关注TA 给TA发消息 送TA红花 TA的回帖

xumiao1985

银虫 (小有名气)
引用回帖:
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

木虫 (小有名气)

【答案】应助回帖

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

看你的语法, 你的数据库应该是MS SQL Server吧? 哪个版本?
赞一下 回复此楼
4楼2015-08-05 11:12:47
已阅   回复此楼   关注TA 给TA发消息 送TA红花 TA的回帖

xumiao1985

银虫 (小有名气)
引用回帖:
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

木虫 (小有名气)

【答案】应助回帖

★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ★ ...
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的回帖

jianliu67

木虫 (小有名气)

【答案】应助回帖

先取短一点的时间段, 验证一下结果对不对. 如果对的话, 再算完整的时间段.
赞一下(1人) 回复此楼
7楼2015-08-05 11:31:05
已阅   回复此楼   关注TA 给TA发消息 送TA红花 TA的回帖
相关版块跳转 我要订阅楼主 xumiao1985 的主题更新
71/1返回列表
普通表情 龙 兔 虎 猫
最具人气热帖推荐 [查看全部] 作者 回/看 最后发表
[考研] 求调剂，一志愿郑州大学材料与化工专硕，英二数二342分，求老师收留 +17 v12abo 2026-04-02 19/950 2026-04-04 09:16 by 来看流星雨10
[考研] 282求调剂 +20 ycy1201 2026-04-01 22/1100 2026-04-04 00:42 by userper
[考研] 化学调剂求助 +6 LULONG1 2026-04-03 6/300 2026-04-03 23:13 by qzxyhcsy
[考研] 考研调剂 +5 小sun要好运 2026-04-03 5/250 2026-04-03 21:43 by 啵啵啵0119
[考研] 五邑大学土木工程招调剂生2026 +3 wyutj 2026-03-31 4/200 2026-04-03 18:21 by zengxj_7201
[考研] 317分 一志愿南理工材料工程 本科湖工大 求调剂 +14 芋泥小铃铛 2026-03-28 14/700 2026-04-03 15:36 by 晴空210210
[考研] 324求调剂 +4 想上学求调 2026-04-03 4/200 2026-04-03 14:41 by rongligao
[考研] 一志愿北京交通大学材料工程总分358 +4 cs0106 2026-04-03 4/200 2026-04-03 13:41 by 百灵童888
[考研] 315分 085602 求调剂 +15 26考研上岸版26 2026-04-02 15/750 2026-04-03 12:45 by xingguangj
[考研] 282求调剂 +5 呼吸都是减肥 2026-03-31 5/250 2026-04-03 12:03 by 1753564080
[考研] 081200-11408-276学硕求调剂 +6 崔wj 2026-04-02 6/300 2026-04-03 10:19 by 蓝云思雨
[考研] 325分化学调剂 +5 15771691647 2026-04-02 5/250 2026-04-03 09:58 by ChemPharm
[考研] 312求调剂 +6 小小墨123 2026-04-02 7/350 2026-04-03 07:32 by jsw79
[考研] 279求调剂 +5 傅文秋 2026-04-02 5/250 2026-04-02 18:10 by 笔落锦州
[考研] 322求调剂 +5 熹僖XX 2026-03-31 6/300 2026-04-02 10:08 by 求调剂zz
[考研] 一志愿9初试366 本双非求调剂 +4 运气来得若有似� 2026-04-02 4/200 2026-04-02 09:56 by guanxin1001
[考研] 一志愿安徽大学计算机科学与技术学硕，331分求调剂 +5 蒋昌鹏qtj 2026-04-01 5/250 2026-04-02 08:10 by fxue1114
[考研] 考研调剂求助 +7 13287130938 2026-03-31 7/350 2026-03-31 16:39 by 690616278
[考研] 276求调剂 +3 赵久华 2026-03-29 3/150 2026-03-31 10:06 by cal0306
[考研] 305求调剂 +8 RuiFairyrui 2026-03-28 8/400 2026-03-29 08:22 by fmesaito
信息提示
关闭
请填处理意见
关闭