同一个库，同样的语句，不同的表，一个报错，一个正常-CSDN论坛

⋅为什么结果是一样的呢？ ⋅同一个库，同样的语句，不同的表，一个报错，一个正常 ⋅关于teeChart X轴为DateTime类型的问题 更多帖子 关注空间博客 ycagri 本版专家分：253 结帖率 90%



Select 

     STCD,

     TM 

from 

     ST_RIVER_R a 

where 

     not exists(select 1 from ST_RIVER_R where stcd = a.stcd and TM > a.TM) 

     and STCD in ('40609500','40609502','40609504','40609506','40609508','40609510','40609512','40609514','40610101','40610102','40610104','40610106','40610108','40610110','40610112','40610400','40610402','40610404','40610406','40610408','40610410','40610412','40610414','40610450','40610452','40610454','40610456','40610458','40610460','40610462','40610464','40610466','40610468','40610470','40610472','40610500','40610502','40610504','40610506','40804000','40804002','40804004','40804006','40804008','40804010','40804100','40804102','40804104','40804106','40804108','40804110','40804112','40804200','40804202','40804204','40804206','40804208','40804210','40804212','40804214','40804216','40902840','40902842','41110700','41110702','41110704','41110706','41110750','41110752','41110754','41111050','41111075','41111077','41111079','41111081','41111083','41111085','41111087','41111089','41111091','41111150','41111152','41111154','41111156','41111158','41111160','41111162','41111164','41111166','41111168','41111170','41111172','41111250','41111252','41111254','41111256','41111351','41111352','41111354','41111356','41111358','41111360','41111362','41111364','41111380','41111382','41111384','41111386','41111520','41111522','41111524','41111526','41111528','41111530','41111532','41111534','41111536','41111538','41111540','41111542','41111550','41111552','41111554','41111556','41209100','41209102','41209104','41209106','41209108','41209110','41209112','41209114','41209116','41209118','41209120','41209122','41209124','41209126','41209300','41209350','41209352','41209450','41312000','41312002','41312004','41312006','41312008','41312010','41312012','41312014','41313500','41313502','41313504','41313506','41313508','41313510','41313512','41610000','41610002','41610004','60719100','60719102','60719104','60719106','60719108','60719110','60719112','60719114','60719116','60719118','60719120','60719122','60719131','60719132','60719134','61814200','61814202','61814204','61814206','61814208','61815800','61815802','61815804','61815806','61815808','61815810','61815850','61815852','61815854','61815856','61815858','61815860','61815862','61815864','61816000','61816002','61816004','61816006','61816008','61816010','61816012','61817000','61817002','61817004','61817006','61817008','61817010','61817012','61817014','61817020','61817022','61919801','61919802','61919804','61919806','61919808')  and TM between '2013-03-09 08:00:00' and '2013-03-25 08:00:00'  

ORDER BY STCD,TM

都是按照STCD、TM创建的主键
ST_PPTN_T表雨量表330万，ST_RIVER_R 水情表150万

数据库是SQL2000

现在的情况是，如果是ST_PPTN_T表，就很快出结果，如果是ST_RIVER_R表，就非常的慢，而且还报错
[Microsoft] [ODBC SQL Server Driver] [DBNETLIB] connection Write (Wrapper Write())

是什么原因？

而且就目前的这一条语句，有什么更好的解决办法

0 2013-03-26 11:50:03

回复数 9 只看楼主引用举报楼主

⋅问个问题，建表时，外键约束对性能有多大损耗 ⋅手机绑定没有获取50个下载积分，请鹳狸猿帮忙解决以下 ⋅vs2010F10调试问题 更多帖子 关注空间博客 chuanzhang5687 本版专家分：16115: 是链接到服务器查询的吗
语句写的太垃圾

0 2013-03-26 12:05:07

只看TA 引用举报 #1 得分 0

⋅眼镜一只。。。。。。。。。。。。。。。。。。。。 ⋅中国好声音广告词，pk华少一下 ⋅第一次拍静物，散个分，哈哈。 更多帖子 关注空间博客 fcuandy 本版专家分：68909 金牌 2007年11月总版技术专家分月排行榜第一 蓝花 2008年11月 MS-SQL Server大版内专家分月排行榜第三 2008年10月 MS-SQL Server大版内专家分月排行榜第三 2006年7月 MS-SQL Server大版内专家分月排行榜第三: 将not exists改为 max(tm)聚合试下看看。

0 2013-03-26 12:07:53

只看TA 引用举报 #2 得分 0

⋅眼镜一只。。。。。。。。。。。。。。。。。。。。 ⋅中国好声音广告词，pk华少一下 ⋅第一次拍静物，散个分，哈哈。 更多帖子 关注空间博客 fcuandy 本版专家分：68909 金牌 2007年11月总版技术专家分月排行榜第一 蓝花 2008年11月 MS-SQL Server大版内专家分月排行榜第三 2008年10月 MS-SQL Server大版内专家分月排行榜第三 2006年7月 MS-SQL Server大版内专家分月排行榜第三: select a, max(b) from tb where a in (...) group by a

0 2013-03-26 13:40:48

只看TA 引用举报 #3 得分 0

⋅BCP和DTS的区别在哪里~~ ⋅同一台电脑上，先装SQL2005后装2000，用2000的企业管理器无法连接2005的server ⋅SQL server2000当前活动服务器与操作系统的关系 更多帖子 关注空间博客 seusoftware 本版专家分：2570

试试



with t

as

(

Select STCD,TM,ROW_NUMBER() OVER(PARTITION BY stcd ORDER BY TM DESC) as num from ST_RIVER_R

where STCD in ('40609500','40609502','40609504','40609506','40609508','40609510','40609512','40609514','40610101','40610102','40610104','40610106','40610108','40610110','40610112','40610400','40610402','40610404','40610406','40610408','40610410','40610412','40610414','40610450','40610452','40610454','40610456','40610458','40610460','40610462','40610464','40610466','40610468','40610470','40610472','40610500','40610502','40610504','40610506','40804000','40804002','40804004','40804006','40804008','40804010','40804100','40804102','40804104','40804106','40804108','40804110','40804112','40804200','40804202','40804204','40804206','40804208','40804210','40804212','40804214','40804216','40902840','40902842','41110700','41110702','41110704','41110706','41110750','41110752','41110754','41111050','41111075','41111077','41111079','41111081','41111083','41111085','41111087','41111089','41111091','41111150','41111152','41111154','41111156','41111158','41111160','41111162','41111164','41111166','41111168','41111170','41111172','41111250','41111252','41111254','41111256','41111351','41111352','41111354','41111356','41111358','41111360','41111362','41111364','41111380','41111382','41111384','41111386','41111520','41111522','41111524','41111526','41111528','41111530','41111532','41111534','41111536','41111538','41111540','41111542','41111550','41111552','41111554','41111556','41209100','41209102','41209104','41209106','41209108','41209110','41209112','41209114','41209116','41209118','41209120','41209122','41209124','41209126','41209300','41209350','41209352','41209450','41312000','41312002','41312004','41312006','41312008','41312010','41312012','41312014','41313500','41313502','41313504','41313506','41313508','41313510','41313512','41610000','41610002','41610004','60719100','60719102','60719104','60719106','60719108','60719110','60719112','60719114','60719116','60719118','60719120','60719122','60719131','60719132','60719134','61814200','61814202','61814204','61814206','61814208','61815800','61815802','61815804','61815806','61815808','61815810','61815850','61815852','61815854','61815856','61815858','61815860','61815862','61815864','61816000','61816002','61816004','61816006','61816008','61816010','61816012','61817000','61817002','61817004','61817006','61817008','61817010','61817012','61817014','61817020','61817022','61919801','61919802','61919804','61919806','61919808')  and TM between '2013-03-09 08:00:00' and '2013-03-25 08:00:00'

)

select * from t where num = 1

0 2013-03-26 14:15:28

只看TA 引用举报 #4 得分 0

最后一只恐龙 本版专家分：655: 表名后面有个“架构”或者是“Schama“的信息，看这两个表的所有者是不是相同。

0 2013-03-26 14:35:08

只看TA 引用举报 #5 得分 0

⋅又来请教2005内存方面的问题了~~ ⋅问个关于AWE中“锁定内存页”的小白问题 ⋅能否根据数据页信息查询到具体数据 更多帖子 关注空间博客 szm341 本版专家分：14466 黄花 2013年3月 MS-SQL Server大版内专家分月排行榜第二 2013年2月 MS-SQL Server大版内专家分月排行榜第二 2013年1月 MS-SQL Server大版内专家分月排行榜第二: 会不会in列表太多了，尝试把参数组合成一个临时表，然后in临时表试试

0 2013-03-26 14:42:22

只看TA 引用举报 #6 得分 0

ycagri 本版专家分：253: 谢谢，不能row_number，SQL2000的库

0 2013-03-26 16:48:39

只看TA 引用举报 #7 得分 0

⋅URL中openConnection的实现 ⋅varchar ,varchar2和nvarchar2的区别 ⋅求oracle入门好书 更多帖子 关注空间博客 Leon_He2014 本版专家分：4628



select stcd,max(TM) TM

into #temp

from ST_RIVER_R

group by stcd



Select 

     STCD,

     TM 

from 

     ST_RIVER_R a 

join #temp b

on a.stcd = b.stcd and a.TM=b.TM

and TM between '2013-03-09 08:00:00' and '2013-03-25 08:00:00'  

and STCD in ('40609500','40609502','40609504','40609506','40609508','40609510','40609512','40609514','40610101','40610102','40610104','40610106','40610108','40610110','40610112','40610400','40610402','40610404','40610406','40610408','40610410','40610412','40610414','40610450','40610452','40610454','40610456','40610458','40610460','40610462','40610464','40610466','40610468','40610470','40610472','40610500','40610502','40610504','40610506','40804000','40804002','40804004','40804006','40804008','40804010','40804100','40804102','40804104','40804106','40804108','40804110','40804112','40804200','40804202','40804204','40804206','40804208','40804210','40804212','40804214','40804216','40902840','40902842','41110700','41110702','41110704','41110706','41110750','41110752','41110754','41111050','41111075','41111077','41111079','41111081','41111083','41111085','41111087','41111089','41111091','41111150','41111152','41111154','41111156','41111158','41111160','41111162','41111164','41111166','41111168','41111170','41111172','41111250','41111252','41111254','41111256','41111351','41111352','41111354','41111356','41111358','41111360','41111362','41111364','41111380','41111382','41111384','41111386','41111520','41111522','41111524','41111526','41111528','41111530','41111532','41111534','41111536','41111538','41111540','41111542','41111550','41111552','41111554','41111556','41209100','41209102','41209104','41209106','41209108','41209110','41209112','41209114','41209116','41209118','41209120','41209122','41209124','41209126','41209300','41209350','41209352','41209450','41312000','41312002','41312004','41312006','41312008','41312010','41312012','41312014','41313500','41313502','41313504','41313506','41313508','41313510','41313512','41610000','41610002','41610004','60719100','60719102','60719104','60719106','60719108','60719110','60719112','60719114','60719116','60719118','60719120','60719122','60719131','60719132','60719134','61814200','61814202','61814204','61814206','61814208','61815800','61815802','61815804','61815806','61815808','61815810','61815850','61815852','61815854','61815856','61815858','61815860','61815862','61815864','61816000','61816002','61816004','61816006','61816008','61816010','61816012','61817000','61817002','61817004','61817006','61817008','61817010','61817012','61817014','61817020','61817022','61919801','61919802','61919804','61919806','61919808')  



ORDER BY STCD,TM

试试这样

0 2013-03-26 17:06:18

只看TA 引用举报 #8 得分 0

⋅新年继续散分啦散完为止啊

⋅继续散分

⋅博客无法显示图片

更多帖子

关注空间博客

KevinLiu

本版专家分：17649

微软MVP 2014年1月荣获微软MVP称号

红花 2013年6月 MS-SQL Server大版内专家分月排行榜第一

蓝花 2013年7月 MS-SQL Server大版内专家分月排行榜第三: 引用 8 楼 stublue 的回复:
SQL code?12345678910111213141516select stcd,max(TM) TMinto #tempfrom ST_RIVER_Rgroup by stcd Select STCD, TM from ST_RIVER_R a join #temp bon a.stcd = b.stcd and a.TM=b.TMan……

临时表性能应该好点

0 2013-03-27 07:23:04

只看TA 引用举报 #9 得分 0

同一个库，同样的语句，不同的表，一个报错，一个正常 [问题点数：20分]