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

24小时热门版块排行榜    

小木虫论坛-学术科研互动平台 » 计算模拟区 » 程序语言 » 数据库 » 感谢各位朋友帮忙优化下，运行时间实在太长了
71/1返回列表
查看: 960  |  回复: 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返回列表
普通表情 龙 兔 虎 猫
信息提示
关闭
请填处理意见
关闭