同样的sql 语句在不同机器上得出的记录行数不一样，大家有没有碰到！！！

qianguob 2006-12-28 09:43:44
同样的sql 语句在不同机器上得出的记录行数不一样，大家有没有碰到！！！
我写了一个union all的sql 语句，在同一台服务器上，用我自己的机器能得到3万多条记录，但是用服务器自己的查询分析器只能得到一半的记录，结果是union all 后面的记录没有显示，这是为什么？但是我的机器上执行是正确的，请大家帮忙看看问题到底出在哪里了？
select a=1,h1=1,'' as Wbs_fullcode,'' as wbs_name,D.reserve_dtl_str11 as FPH ,M.CheckedDate as DateUse,
D.Mat_Sid,isnull(D.unitprice,0) as unitprice,D.Title,D.spec+D.model as GGXH,D.unitname,isnull(D.ActualNum,0) as ActualNum,0 as OutNum ,'' LLR, '' LLDW,
reserve_dtl_str2 as CLLB,year(M.CheckedDate) as nian, month(M.CheckedDate) as yue ,day(M.CheckedDate) as ri ,
isnull(D.unitprice*D.ActualNum,0) as DHJE,0 as LYJE ,reserve_dtl_str3 as GYS ,'' as LLDBH,'' AS QGDBH
,traffUnitprice as YFDJ
from view_Mat_DeliveryDetail D,view_Mat_Delivery M
where D.Delivery_Sid=M.Delivery_Sid and M.status='完结' and M.CheckedDate >='2005-12-01' and M.CheckedDate <='2006-12-22'
--- 过滤掉手工批准的
and D.delivery_sid in (select rec_id from pln_act where rec_type='delr' )
--and mat_sid=18884
union
select a=2, h1=1,Wbs_fullcode,D1.wbs_name,'' as FPH,M1.DrawDate as DateUse,D1.Mat_Sid,
isnull(D1.unitprice,0) as unitprice ,D1.Title,D1.spec+D1.model as GGXH,D1.unitname,0 as ActualNum,isnull(D1.ActualGetNum,0) as OutNum ,
M1.drawhuman_name as LLR ,M1.DrawDept_name AS LLDW,
reserve_dtl_str2 as CLLB,year(M1.DrawDate) as nian, month(M1.DrawDate) as yue ,day(M1.DrawDate) as ri ,
0,isnull(D1.unitprice*D1.ActualGetNum,0) as LYJE,'' ,M1.fetchcode, ask.ask_code
,0 as YFDJ
from view_Mat_Fetch M1 ,view_Mat_FetchDetail D1 left join mat_purchaseask ask on D1.sourceid=ask.ask_sid
where D1.Fetch_Sid=M1.Fetch_Sid and M1.status='批准' and D1.HaveFinished='Y' and D1.CheckedDate >='2005-12-01'
and D1.CheckedDate <='2006-12-22'
--- 过滤掉手工批准的
and D1.fetch_sid in (select rec_id from pln_act where rec_type='fetc' )
----保证领料和到货是对应的材料
and D1.mat_sid in (select D2.mat_sid from Mat_DeliveryDetail D2,Mat_Delivery M2
where D2.Delivery_Sid=M2.Delivery_Sid and M2.status='完结' )
--and mat_sid=18884

order by Mat_Sid ,a

...全文
349 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
zheninchangjiang 2006-12-31
  • 打赏
  • 举报
 回复
不要奇观，保证补丁一致
RicCC 2006-12-31
  • 打赏
  • 举报
 回复
是不是有两个table重名，而用户角色不一样?
Hahahahahaha 2006-12-30
  • 打赏
  • 举报
 回复
检查下视图的定义是否相同，特别是view_Mat_Delivery视图，最好贴出代码大家参详以下

moqiyayan 2006-12-30
  • 打赏
  • 举报
 回复
如果可以的话，顺便请高手也帮我看看我的问题
http://community.csdn.net/Expert/topic/5264/5264544.xml?temp=.8988001
多谢了！
moqiyayan 2006-12-30
  • 打赏
  • 举报
 回复
关注！
qianguob 2006-12-28
  • 打赏
  • 举报
 回复
这点是可以肯定的，同样的sql 语句，同样的服务器，只是查询分析器客户端不一样。
中国风 2006-12-28
  • 打赏
  • 举报
 回复
首先要确定是不是同一个语句
同一个数据库
qianguob 2006-12-28
  • 打赏
  • 举报
 回复
邹建 大哥在吗？能否给看看出了什么问题吗？
qianguob 2006-12-28
  • 打赏
  • 举报
 回复
用楼上的脚本在本地执行结果一样，但是在那边服务器上执行会报错，报错如下：
服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: '2006-12-22' 附近有语法错误。

难道是服务器设置什么问题吗？为什么远程连接执行是正确的呢？而服务器本身执行就会报错呢？
Hahahahahaha 2006-12-28
  • 打赏
  • 举报
 回复
还有个可能，你测试下一下语句：

select * from (
select a=1,h1=1,'' as Wbs_fullcode,'' as wbs_name,D.reserve_dtl_str11 as FPH ,M.CheckedDate as DateUse,
D.Mat_Sid,isnull(D.unitprice,0) as unitprice,D.Title,D.spec+D.model as GGXH,D.unitname,isnull(D.ActualNum,0) as ActualNum,0 as OutNum ,'' LLR, '' LLDW,
reserve_dtl_str2 as CLLB,year(M.CheckedDate) as nian, month(M.CheckedDate) as yue ,day(M.CheckedDate) as ri ,
isnull(D.unitprice*D.ActualNum,0) as DHJE,0 as LYJE ,reserve_dtl_str3 as GYS ,'' as LLDBH,'' AS QGDBH
,traffUnitprice as YFDJ
from view_Mat_DeliveryDetail D,view_Mat_Delivery M
where D.Delivery_Sid=M.Delivery_Sid and M.status='完结' and M.CheckedDate >='2005-12-01' and M.CheckedDate <='2006-12-22'
--- 过滤掉手工批准的
and D.delivery_sid in (select rec_id from pln_act where rec_type='delr' )
--and mat_sid=18884
union all
select a=2, h1=1,Wbs_fullcode,D1.wbs_name,'' as FPH,M1.DrawDate as DateUse,D1.Mat_Sid,
isnull(D1.unitprice,0) as unitprice ,D1.Title,D1.spec+D1.model as GGXH,D1.unitname,0 as ActualNum,isnull(D1.ActualGetNum,0) as OutNum ,
M1.drawhuman_name as LLR ,M1.DrawDept_name AS LLDW,
reserve_dtl_str2 as CLLB,year(M1.DrawDate) as nian, month(M1.DrawDate) as yue ,day(M1.DrawDate) as ri ,
0,isnull(D1.unitprice*D1.ActualGetNum,0) as LYJE,'' ,M1.fetchcode, ask.ask_code
,0 as YFDJ
from view_Mat_Fetch M1 ,view_Mat_FetchDetail D1 left join mat_purchaseask ask on D1.sourceid=ask.ask_sid
where D1.Fetch_Sid=M1.Fetch_Sid and M1.status='批准' and D1.HaveFinished='Y' and D1.CheckedDate >='2005-12-01'
and D1.CheckedDate <='2006-12-22'
--- 过滤掉手工批准的
and D1.fetch_sid in (select rec_id from pln_act where rec_type='fetc' )
----保证领料和到货是对应的材料
and D1.mat_sid in (select D2.mat_sid from Mat_DeliveryDetail D2,Mat_Delivery M2
where D2.Delivery_Sid=M2.Delivery_Sid and M2.status='完结' )
--and mat_sid=18884
) as t
order by Mat_Sid ,a
Hahahahahaha 2006-12-28
  • 打赏
  • 举报
 回复
union all后面的语句

from view_Mat_Fetch M1 ,view_Mat_FetchDetail D1 left join mat_purchaseask ask on D1.sourceid=ask.ask_sid
where D1.Fetch_Sid=M1.Fetch_Sid and M1.status='批准' and D1.HaveFinished='Y' and D1.CheckedDate >='2005-12-01'
and D1.CheckedDate <='2006-12-22'
--- 过滤掉手工批准的
and D1.fetch_sid in (select rec_id from pln_act where rec_type='fetc' )
----保证领料和到货是对应的材料
and D1.mat_sid in (select D2.mat_sid from Mat_DeliveryDetail D2,Mat_Delivery M2
where D2.Delivery_Sid=M2.Delivery_Sid and M2.status='完结' )
--and mat_sid=18884



这么写显得不规范，虽然测试结果好像没问题，该规范来试试

from view_Mat_Fetch M1 inner join view_Mat_FetchDetail D1
on D1.Fetch_Sid=M1.Fetch_Sid
left join mat_purchaseask ask on D1.sourceid=ask.ask_sid
where M1.status='批准' and D1.HaveFinished='Y' and D1.CheckedDate >='2005-12-01'
and D1.CheckedDate <='2006-12-22'
--- 过滤掉手工批准的
and D1.fetch_sid in (select rec_id from pln_act where rec_type='fetc' )
----保证领料和到货是对应的材料
and D1.mat_sid in (select D2.mat_sid from Mat_DeliveryDetail D2,Mat_Delivery M2
where D2.Delivery_Sid=M2.Delivery_Sid and M2.status='完结' )
--and mat_sid=18884

qianguob 2006-12-28
  • 打赏
  • 举报
 回复
即使我把数据库恢复到本地，得出的结果也是对的，但是客户在那边两台机器上执行结果都只有1.5万条记录，所以就很奇怪了，并且少的是union all 后面的记录。
qianguob 2006-12-28
  • 打赏
  • 举报
 回复
连接的是同一台服务器，这个是确定的，因为我给客户发的脚本，他执行的结果就是我的一半记录，后来我自己远程连接了一下，得到结果是3万条，而客户在自己服务器上执行却只有1.5万条，不知道问题出在哪里了？
jacobsan 2006-12-28
  • 打赏
  • 举报
 回复
冒牌的试过没？两台机器的数据一样？你确定一样？
dawugui 2006-12-28
  • 打赏
  • 举报
 回复
如果数据是相同的,不可能出现这种错误.

或许你的数据不同?
qianguob 2006-12-28
  • 打赏
  • 举报
 回复
关键是在两个不同机器上查询分析器得到结果不一样，一台机器上显示union all 后面的记录，一台机器没有显示，服务器都连的同一个，这是问题的关键。
qianguob 2006-12-28
  • 打赏
  • 举报
 回复
目前脚本是这个：

select a=1,h1=1,'' as Wbs_fullcode,'' as wbs_name,D.reserve_dtl_str11 as FPH ,M.CheckedDate as DateUse,
D.Mat_Sid,isnull(D.unitprice,0) as unitprice,D.Title,D.spec+D.model as GGXH,D.unitname,isnull(D.ActualNum,0) as ActualNum,0 as OutNum ,'' LLR, '' LLDW,
reserve_dtl_str2 as CLLB,year(M.CheckedDate) as nian, month(M.CheckedDate) as yue ,day(M.CheckedDate) as ri ,
isnull(D.unitprice*D.ActualNum,0) as DHJE,0 as LYJE ,reserve_dtl_str3 as GYS ,'' as LLDBH,'' AS QGDBH
,traffUnitprice as YFDJ
from view_Mat_DeliveryDetail D,view_Mat_Delivery M
where D.Delivery_Sid=M.Delivery_Sid and M.status='完结' and M.CheckedDate >='2005-12-01' and M.CheckedDate <='2006-12-22'
--- 过滤掉手工批准的
and D.delivery_sid in (select rec_id from pln_act where rec_type='delr' )
--and mat_sid=18884
union all
select a=2, h1=1,Wbs_fullcode,D1.wbs_name,'' as FPH,M1.DrawDate as DateUse,D1.Mat_Sid,
isnull(D1.unitprice,0) as unitprice ,D1.Title,D1.spec+D1.model as GGXH,D1.unitname,0 as ActualNum,isnull(D1.ActualGetNum,0) as OutNum ,
M1.drawhuman_name as LLR ,M1.DrawDept_name AS LLDW,
reserve_dtl_str2 as CLLB,year(M1.DrawDate) as nian, month(M1.DrawDate) as yue ,day(M1.DrawDate) as ri ,
0,isnull(D1.unitprice*D1.ActualGetNum,0) as LYJE,'' ,M1.fetchcode, ask.ask_code
,0 as YFDJ
from view_Mat_Fetch M1 ,view_Mat_FetchDetail D1 left join mat_purchaseask ask on D1.sourceid=ask.ask_sid
where D1.Fetch_Sid=M1.Fetch_Sid and M1.status='批准' and D1.HaveFinished='Y' and D1.CheckedDate >='2005-12-01'
and D1.CheckedDate <='2006-12-22'
--- 过滤掉手工批准的
and D1.fetch_sid in (select rec_id from pln_act where rec_type='fetc' )
----保证领料和到货是对应的材料
and D1.mat_sid in (select D2.mat_sid from Mat_DeliveryDetail D2,Mat_Delivery M2
where D2.Delivery_Sid=M2.Delivery_Sid and M2.status='完结' )
--and mat_sid=18884

order by Mat_Sid ,a
Hahahahahaha 2006-12-28
  • 打赏
  • 举报
 回复
呵，说了半天union all
实际语句却用union
qianguob 2006-12-28
  • 打赏
  • 举报
 回复
我用union all 试了，也不行的，刚开始就用union all 的，即使会忽略重复的，那两个机器应该都会忽略的啊，所以想不同。
Hahahahahaha 2006-12-28
  • 打赏
  • 举报
 回复
在执行语句前执行下
set rowcount 0
go
再测试下

----先排除低级错误
加载更多回复（1）
数据库--sql语句性能（mysql45讲 为什么这些SQL语句逻辑相同，性能却差异巨大？ 为什么我只查一行的语句，也执行这么慢？）
一条SQL语句执行很慢的原因的哪些 目录 一条SQL语句执行很慢的原因的哪些 一 、针对偶尔很慢的情况 1、数据库在刷新脏页 2、拿不到锁 二、一直都这么慢的情况 例子 1没用到索引 （1）、字段没有索引 ...
MySQL之Sql语句的执行原理
我们在数据库层执行SQL语句时，应用程序会连接到相应的数据库服务器，把SQL语句发送给服务器处理。那么sql语句执行时，有哪些步骤呢 1、查询缓存 服务器在解析一个查询语句之前，如果查询缓存是打开的（MySQL默认...
取得前一次MySQL操作所影响的记录行数
mysql_affected_rows() 返回最近一次与 连接句柄 关联的 INSERT，UPDATE 或 DELETE 查询所影响的记录行数.FOUND_ROWS() : select ROW_COUNT() : update delete insert.   FOUND_ROWS() : select ...
SQL语句用法大全
提取：SQL（Structured Query Language）是一个对数据库进行增删改查的语言。不过这玩意儿还是挺难理解的。所以我就写了这篇文章，希望能帮助到您。提取：提取出结果：那么，如果我们想要提取出票房一亿以上的优质...
python执行sql语句慢很多_Python学习教程：面试问：一条SQL语句执行得很慢的原因有哪些？...
说实话，这个问题可以涉及到 MySQL 的很多核心知识，可以扯出一大堆，就像要考你计算机网络的知识时，问你“输入URL回车之后，究竟发生了什么”一样，看...在这里插入图片描述一、开始装逼：分类讨论一条 SQL 语句执...
疑难问题

22,210

社区成员

121,727

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章