同一个库，同样的语句，不同的表，一个报错，一个正常

ycagri 2013-03-26 11:50:03 


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())



是什么原因？

而且就目前的这一条语句，有什么更好的解决办法
...全文
300 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
KevinLiu 2013-03-27
  • 打赏
  • 举报
 回复
引用 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……
临时表性能应该好点
fcuandy 2013-03-26
  • 打赏
  • 举报
 回复
将not exists改为 max(tm)聚合试下看看。
chuanzhang5687 2013-03-26
  • 打赏
  • 举报
 回复
是链接到服务器查询的吗 语句写的太垃圾
Leon_He2014 2013-03-26
  • 打赏
  • 举报
 回复 

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
试试这样
ycagri 2013-03-26
  • 打赏
  • 举报
 回复
谢谢，不能row_number，SQL2000的库
szm341 2013-03-26
  • 打赏
  • 举报
 回复
会不会in列表太多了，尝试把参数组合成一个临时表，然后in临时表试试
最后一只恐龙 2013-03-26
  • 打赏
  • 举报
 回复
表名后面有个“架构”或者是“Schama“的信息，看这两个表的所有者是不是相同。
seusoftware 2013-03-26
  • 打赏
  • 举报
 回复
试试 

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
fcuandy 2013-03-26
  • 打赏
  • 举报
 回复
select a, max(b) from tb where a in (...) group by a
同一条SQL在不同的数据库中执行,有一个会报错.
今天碰到一个很蛋疼的问题,同一条SQL测试环境OK, 线上环境总报错, 找了半天, 也请教了好几个同事都没找出来. 很是费解.两个环境唯一不同的就是 版本,线上是5.6,测试环境是5.1 报错如下: () 后来一位大神给解决了,说是起的表别名没加``. 但是我记得起别名不需要必须加引号,具体原因大神也不知道,说是加上最好~~(皮皮虾,我们回来) 下面是加上引号之后的结果:
MySQL不同数据库不同表连表查询（跨库连表查询）
前提：文章为解决同一个连接不同数据库的表进行关联查询，本文以left join做示例其他的按照同样方法自己测试！ 首先拿到这个表，简称表A吧， 表A在A数据库 然后这个是表B 表B在B数据库 红线框的看不懂的去重新学一下数据库知识谢谢！ 两张表在数据库中的关系 表B 表A 同一个连接不同数据库 现在来关联两张表，其实语法很简单，只需要在表名前面加上数据库名字 例 数据库.表 数据库.表....
同样的查询语句,切换数据库时发生的问题
具体报错如下: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY 同样的代码连接不同的数据库出现的错误 一、问题原因：   由于MySQL启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下) 二、查看mysql是否启用ONLY_FULL_GROUP_BY select @@global.sql_mode 三、查看数据库版本 show variables lik
分区表与普通表之间切换的报错
将分区数据切换至普通表 1.报错：‘ALTER TABLE SWITCH’ 语句失败。表’db_test.dbo.test_parti’ 已分区，但 索引’PK_test_parti’ 未分区。 答：源表或分区以及目标表或分区必须在同一个文件组中。 对应的索引或索引分区也必须在同一个文件组中。 2.报错：ALTER TABLE SWITCH 语句失败。索引’db_test.dbo.steel_...
SQL注入之报错注入
然而，当XPath表达式构造不当，例如包含了非法字符或者尝试执行一个无法解析为有效XPath表达式的字符串时，函数会抛出错误，并且错误信息可能包含有关查询的信息，这便为报错注入提供了机会。floor()报错注入的原因是group by在向临时表插入数据时，由于rand()多次计算导致插入临时表时主键重复，从而报错，又因为报错前concat()中的SQL语句或函数被执行，所以该语句报错且被抛出的主键是SQL语句或函数执行后的结果。这个表名可能不存在，服务器会抛出一个错误消息，可能揭示表是否存在的信息。
MS-SQL Server

34,838

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章