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

24小时热门版块排行榜    

小木虫论坛-学术科研互动平台 » 计算模拟区 » 程序语言 » 数据库 » 感谢各位朋友帮忙优化下，运行时间实在太长了
71/1返回列表
查看: 1081  |  回复: 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返回列表
普通表情 龙 兔 虎 猫
最具人气热帖推荐 [查看全部] 作者 回/看 最后发表
[基金申请] 分享一下我之前已中青C的计划书的filecode +3 布布和一二 2026-08-11 4/200 2026-08-11 15:20 by aasahr
[基金申请] 为什么网上很多人说本周 12号出结果 +6 瞬息宇宙 2026-08-10 6/300 2026-08-11 13:19 by JACS/Angew
[基金申请] 听说今天filecode变了 +26 布布和一二 2026-08-06 49/2450 2026-08-11 13:18 by WH3796
[基金申请] 帮忙看看fileCode +6 wwncly 2026-08-10 12/600 2026-08-11 13:01 by wwncly
[基金申请] FileCode能看出啥？ +7 要乐观耀哥 2026-08-10 17/850 2026-08-11 10:50 by 要乐观耀哥
[基金申请] filecode +9 documentary 2026-08-10 9/450 2026-08-11 10:08 by wk7465
[基金申请] 确定了，国自然21号放榜 +6 布布和一二 2026-08-10 7/350 2026-08-10 19:15 by 2000zf36392
[基金申请] 综述论文作为代表作会不会影响评审专家的印象分？ +10 yufeiwaner 2026-08-09 11/550 2026-08-10 18:47 by yufeiwaner
[基金申请] 关于Filecode分析方法 +3 majunge000 2026-08-10 3/150 2026-08-10 15:46 by lch2012
[基金申请] 国自然结果 +4 Vierhys 2026-08-10 8/400 2026-08-10 15:06 by Vierhys
[基金申请] 据悉今年马上要出结果了 +7 瞬息宇宙 2026-08-10 8/400 2026-08-10 12:42 by Vivilian
[基金申请] 2026国自然放榜时间 +9 布布和一二 2026-08-08 9/450 2026-08-10 11:22 by xxxx2020
[基金申请] 这样的filecode谁见过 +11 布布和一二 2026-08-08 22/1100 2026-08-10 11:10 by wmfsnow
[基金申请] 面上项目filecode邪修 +5 西山十月 2026-08-09 7/350 2026-08-10 07:32 by 仁砚薪传
[基金申请] 国基金的申报应该改成非等额制，评价高的钱多评价低的钱少，但是增加资助率 +7 a089 2026-08-07 7/350 2026-08-08 18:05 by gltch
[基金申请] 固定端突然变了，今天 +6 archvillain 2026-08-06 10/500 2026-08-07 16:03 by 医学老男孩
[基金申请] filecode变化情况 +6 布布和一二 2026-08-07 22/1100 2026-08-07 14:45 by 且听虎啸
[基金申请] 大家散了吧，后缀研究没有意义，别浪费时间了，过好目前的每一天，不要焦虑 +5 Tide man 2026-08-06 7/350 2026-08-07 13:11 by 医学老男孩
[基金申请] filecode +8 布布和一二 2026-08-06 11/550 2026-08-06 20:41 by tangpu318
[基金申请] 好消息？这个有何含义？？？ +8 Tide man 2026-08-05 10/500 2026-08-05 16:14 by xmuxiaoyu
信息提示
关闭
请填处理意见
关闭