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

24小时热门版块排行榜    

小木虫论坛-学术科研互动平台 » 计算模拟区 » 程序语言 » 数据库 » 感谢各位朋友帮忙优化下，运行时间实在太长了
71/1返回列表
查看: 1120  |  回复: 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返回列表
普通表情 龙 兔 虎 猫
最具人气热帖推荐 [查看全部] 作者 回/看 最后发表
[文学芳草园] 厉娜 +3 张丹枫1991 2026-11-09 3/150 2026-11-09 14:58 by 天下第一剑
[考博] 售SCI一区T0P文章，我:8.O.55.1.O54,科目全,可伽急 +5 kvewwGtKktqB 2026-11-04 12/600 2026-11-09 13:26 by Equinoxhua
[考研] 售SCI-T0P文章，我:8O.5.5.1.O.54,科目齐全,可+急 +5 ttH3f1oA6ace 2026-11-04 15/750 2026-11-09 13:19 by Equinoxhua
[硕博家园] 售SCI一区T0P文章，我:8O.55.1.O.54,科目全,可伽急 +5 5sJFDKp2kwuI 2026-11-06 16/800 2026-11-09 11:47 by GwrQVVR6Oenx
[考博] 售SCI文章，我:8O.5.5.1O.54,科目全,可十急 +5 5sJFDKp2kwuI 2026-11-06 15/750 2026-11-09 11:41 by GwrQVVR6Oenx
[硕博家园] 售SCI一区文章，我:8O5.5.1.O5.4,科目全,可伽急 +4 5sJFDKp2kwuI 2026-11-06 10/500 2026-11-09 08:00 by IhnEQauGmT3K
[考研] 售SCI一区T0P文章，我:8O.55.1.O.54,科目全,可伽急 +3 5sJFDKp2kwuI 2026-11-06 12/600 2026-11-09 06:32 by l48hFd8Nwae2
[公派出国] 售SCI一区文章，我:8.O.55.1.O.54,科目齐全,可伽急 +4 5sJFDKp2kwuI 2026-11-06 8/400 2026-11-09 00:14 by l48hFd8Nwae2
[教师之家] 售SCI一区T0P文章，我:8.O.55.1.O.54,科目齐全,可+急 +4 5sJFDKp2kwuI 2026-11-06 8/400 2026-11-08 23:01 by l48hFd8Nwae2
[教师之家] 售SCI一区T0P文章，我:8.O.55.1.O.5.4,科目全,可+急 +5 5sJFDKp2kwuI 2026-11-06 9/450 2026-11-08 22:46 by l48hFd8Nwae2
[教师之家] 延安大学回应“副教授留绝笔信”：确已离世，正核实举报内容 +3 yexuqing 2026-11-04 5/250 2026-11-07 13:22 by 苏东坡二世
[公派出国] 售SCI一区T0P文章，我:8.O.55.1.O.54,科目齐全,可+急 +5 ttH3f1oA6ace 2026-11-04 10/500 2026-11-06 16:12 by 5sJFDKp2kwuI
[公派出国] 售SCI一区文章，我:8.O.551.O.5.4,科目全,可伽急 +6 ttH3f1oA6ace 2026-11-04 12/600 2026-11-06 16:10 by 5sJFDKp2kwuI
[硕博家园] 售SCI一区文章，我:8O5.5.1.O5.4,科目全,可伽急 +4 ttH3f1oA6ace 2026-11-04 11/550 2026-11-06 16:10 by 5sJFDKp2kwuI
[公派出国] 售SCI一区T0P文章，我:8.O.55.1.O54,科目全,可伽急 +5 kvewwGtKktqB 2026-11-04 14/700 2026-11-06 05:10 by qL1cU46YIdK0
[硕博家园] 售SCI一区T0P文章，我:8O.55.1.O.5.4,科目齐全,可+急 +6 kvewwGtKktqB 2026-11-04 16/800 2026-11-06 05:00 by qL1cU46YIdK0
[考博] 售一区SCI文章T0P，我:8O.551.O54,科目全,可十急 +4 ttH3f1oA6ace 2026-11-04 7/350 2026-11-06 02:13 by qL1cU46YIdK0
[考研] 售SCI一区T0P文章，我:8.O55.1.O.54,科目全,可十急 +4 ttH3f1oA6ace 2026-11-04 10/500 2026-11-06 02:04 by qL1cU46YIdK0
[硕博家园] 售SCI-T0P文章，我:8O.5.5.1.O.54,科目齐全,可+急 +3 ttH3f1oA6ace 2026-11-04 9/450 2026-11-06 02:02 by qL1cU46YIdK0
[硕博家园] 售SCI一区T0P文章，我:8.O55.1.O.54,科目全,可十急 +4 hNpp4Y2CGvSC 2026-11-04 12/600 2026-11-06 01:53 by qL1cU46YIdK0
信息提示
关闭
请填处理意见
关闭