none
怎么查看数据 RRS feed

  • 问题

  • 运行结果是这样的:

    另一个结果是这样的:

    这个结果和上面的结果对不上那,

    第二个结果的sql查询语句运行的结果是:

    第一个是一个存储过过程,运行结果是:

    语句是:

    USE [Property]
    GO
    /****** Object:  StoredProcedure [dbo].[PM_QuantitativeStatisticsForEmployee]    Script Date: 2016/9/24 7:46:05 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		马小明
    -- Create date: 2013-05-16 17:01
    -- Description:	人员量化统计
    -- Edited date: 2013-05-16 17:01
    -- Description:	区分出租和出售
    -- Edited date: 2014-09-09 09:43
    -- Description:	更新大部门下人员异动后跨月查询统计错误问题
    -- =============================================
    ALTER PROCEDURE [dbo].[PM_QuantitativeStatisticsForEmployee]
    	@Category int,--查询类型:出租(172)或出售(171)
    	@LastDateTime nvarchar(50),--最后一天
    	@Where nvarchar(max),--条件
    	@OrderBy nvarchar(500),
    	@pageSize		bigint = 10,		-- 页尺寸
    	@pageIndex		bigint = 1,	
    	@TotalRecordCount bigint Output,	--返回记录总数		
    	@LastUpdateTime datetime Output		--更新时间
    AS
    Create Table #PM_QuantitativeStatisticsForEmployee
    (
    	DepartmentID uniqueidentifier
    	,DepartmentName nvarchar(50)
    	,AuxiliaryID uniqueidentifier
    	,StationID uniqueidentifier
    	,TotalFollowNum	int
    	,SurveyNum	int
    	,PictrueNum	int
    	,KeyNum	int
    	,EntrustNum	int
    	,FollowNum	int
    	,LookAroundNum	int
    	,LookingNum	int
    	,TalksNum	int
    	,SincereMoney	int
    	,NewHousingResourcesNum	int
    	,MothLoseHousingResourcesNum	int
    	,StockHousingResourcesNum	int
    	,NewClientInfoNum	int
    	,MothLoseClientInfoNum	int
    	,StockClientInfoNum	int
    	,SendoutCount	int
    	,TheWholeCount	int
    	,RefreshCount	int
    	,SecondViewNum int
    	,ResuViewNum int
    )
    
    BEGIN Try
    		Declare @UpdateTime datetime
    		Set @UpdateTime='1900-01-01'
    		
    		declare @SQL_Query nvarchar(max)		
    		Declare @Counts int
    		--判断是否有数据
    		Declare @SQLCounts nvarchar(Max)
    		Declare @LastTime2 Nvarchar(20)
    		Set @LastTime2 = CONVERT(Nvarchar(4), year(@LastDateTime)) + '-' + CONVERT(Nvarchar(4), month(@LastDateTime)) + '-' + CONVERT(Nvarchar(4), day(@LastDateTime))
    		set @SQLCounts=N'select @Counts=COUNT(0) from [dbo].[PM_QuantitativeStatisticsForBusinessTripResource] where Category='+CONVERT(nvarchar(20),@Category)+' And IsUse=1 '+@Where
    		print @SQLCounts
    		EXEC sp_executesql @SQLCounts,N'@Counts int output',@Counts output
    		
    		If @Counts>0
    		Begin
    			--提取最后更新时间
    			declare @SQL_UpdateTime nvarchar(Max)
    			set @SQL_UpdateTime='select top 1 @UpdateTime=UpdateTime from [dbo].[PM_QuantitativeStatisticsForBusinessTripResource] where Category='+CONVERT(nvarchar(20),@Category)+' And IsUse=1 '+@Where+' and (UpdateTime between '+@LastDateTime+' and '''+ @LastTime2 +' 23:59:59'') order by UpdateTime desc'
    			EXEC sp_executesql @SQL_UpdateTime,N'@UpdateTime datetime OUTPUT',@UpdateTime OUTPUT
    		
    			set @SQL_Query='insert into #PM_QuantitativeStatisticsForEmployee(DepartmentID,DepartmentName,AuxiliaryID,StationID,TotalFollowNum,SurveyNum,PictrueNum,KeyNum,EntrustNum,FollowNum,LookAroundNum,LookingNum,TalksNum,SincereMoney,NewHousingResourcesNum,MothLoseHousingResourcesNum,NewClientInfoNum,MothLoseClientInfoNum,StockHousingResourcesNum,StockClientInfoNum,SendoutCount,TheWholeCount,RefreshCount,SecondViewNum,ResuViewNum)
    							SELECT DepartmentID,DepartmentName,AuxiliaryID,StationID,SUM(TotalFollowNum) as TotalFollowNum,SUM(SurveyNum) as SurveyNum,SUM(PictrueNum) as PictrueNum,SUM(KeyNum) as KeyNum,SUM(EntrustNum) as EntrustNum,SUM(FollowNum) as FollowNum,SUM(LookAroundNum) as LookAroundNum,SUM(LookingNum) as LookingNum,SUM(TalksNum) as TalksNum,SUM(SincereMoney) as SincereMoney,SUM(NewHousingResourcesNum) as NewHousingResourcesNum,SUM(MothLoseHousingResourcesNum) as MothLoseHousingResourcesNum,SUM(NewClientInfoNum) as NewClientInfoNum,SUM(MothLoseClientInfoNum) as MothLoseClientInfoNum
    							,(select (case when count(0)=0 then 0 else (select StockHousingResourcesNum from [dbo].[PM_QuantitativeStatisticsForBusinessTripResource] As DDD where DDD.IsUse=1 and DDD.Category='+CONVERT(nvarchar(20),@Category)+' And DDD.AuxiliaryID=AAA.AuxiliaryID And DDD.UpdateTime between '''+@LastDateTime+' 00:00:00'' and '''+@LastDateTime+' 23:59:59'') end) from [dbo].[PM_QuantitativeStatisticsForBusinessTripResource] As XXX where XXX.IsUse=1 and XXX.Category='+CONVERT(nvarchar(20),@Category)+' And XXX.AuxiliaryID=AAA.AuxiliaryID And XXX.UpdateTime between '''+@LastDateTime+' 00:00:00'' and '''+@LastDateTime+' 23:59:59'') As StockHousingResourcesNum
    							,(select (case when COUNT(0)=0 then 0 else (select StockClientInfoNum from [dbo].[PM_QuantitativeStatisticsForBusinessTripResource] As EEE where EEE.IsUse=1 and EEE.Category='+CONVERT(nvarchar(20),@Category)+' And EEE.AuxiliaryID=AAA.AuxiliaryID And EEE.UpdateTime between '''+@LastDateTime+' 00:00:00'' and '''+@LastDateTime+' 23:59:59'') end) from [dbo].[PM_QuantitativeStatisticsForBusinessTripResource] As YYY where YYY.IsUse=1 and YYY.Category='+CONVERT(nvarchar(20),@Category)+' And YYY.AuxiliaryID=AAA.AuxiliaryID And YYY.UpdateTime between '''+@LastDateTime+' 00:00:00'' and '''+@LastDateTime+' 23:59:59'') As StockClientInfoNum
    							,SUM(SendoutCount) as SendoutCount,SUM(TheWholeCount) as TheWholeCount,SUM(RefreshCount) as RefreshCount,SUM(SecondViewNum) as SecondViewNum,SUM(ResuViewNum) as ResuViewNum
    							FROM [dbo].[PM_QuantitativeStatisticsForBusinessTripResource] AAA where AAA.Category='''+CONVERT(nvarchar(20),@Category)+''' And AuxiliaryID<>''00000000-0000-0000-0000-000000000000'' And AAA.IsUse=1 '+@Where+' group by AAA.DepartmentID,AAA.DepartmentName,AAA.AuxiliaryID,AAA.StationID'
    							--Order By '+Replace(Replace(Replace(Replace(@OrderBy,'E.',''),'OD.',''),'OS.',''),'Q.','')							
    			print @SQL_Query
    			exec sp_executesql @SQL_Query
    		End
    		
    		Set @LastUpdateTime=@UpdateTime
    				
    		declare @SQL_TotalRecordCount nvarchar(max)
    		Set @SQL_TotalRecordCount = 'Select @TotalRecordCount = Count(0) From #PM_QuantitativeStatisticsForEmployee'	
    		EXEC sp_executesql @SQL_TotalRecordCount,N'@TotalRecordCount int OUTPUT',@TotalRecordCount OUTPUT--计算总记录数
    				
    		--执行分页送现
    		Declare @SQL_Result	Nvarchar(4000)
    		Declare @Fields Nvarchar(1000)
    		Declare @RowFields Nvarchar(1000)
    
    		--处理开始点和结束点
    		Declare @StartRecord int
    		Declare @EndRecord int
    		Declare @SqlOrderBy varchar(50)
    	    
    		--设定开始与结束行
    		Set @StartRecord = @PageIndex
    		Set @EndRecord = @StartRecord + @PageSize - 1
    		Set @SqlOrderBy=Case When @OrderBy='' Then '' Else (Case When CHARINDEX('.',@OrderBy)>0 then @OrderBy else 'Q.'+@OrderBy end) End	
    		
    		Set @Fields = 'CompanyID, CompanyName, DepartmentID, DepartmentName, EmployeeID, TrueName,UserStatus, StationLogID,StationName,AuxiliaryID,TotalFollowNum,SurveyNum,PictrueNum,KeyNum,EntrustNum,FollowNum,LookAroundNum,LookingNum,TalksNum,SincereMoney,NewHousingResourcesNum,MothLoseHousingResourcesNum,StockHousingResourcesNum,NewClientInfoNum,MothLoseClientInfoNum,StockClientInfoNum,SendoutCount,TheWholeCount,RefreshCount,SecondViewNum,ResuViewNum'
    		Set @RowFields='A.CompanyID, OC.CompanyName, Q.DepartmentID, Q.DepartmentName, A.EmployeeID, E.TrueName,E.UserStatus, A.StationLogID,OS.StationName,Q.AuxiliaryID,Q.TotalFollowNum,Q.SurveyNum,Q.PictrueNum,Q.KeyNum,Q.EntrustNum,Q.FollowNum,Q.LookAroundNum,Q.LookingNum,Q.TalksNum,Q.SincereMoney,Q.NewHousingResourcesNum,Q.MothLoseHousingResourcesNum,Q.StockHousingResourcesNum,Q.NewClientInfoNum,Q.MothLoseClientInfoNum,Q.StockClientInfoNum,Q.SendoutCount,Q.TheWholeCount,Q.RefreshCount,Q.SecondViewNum,Q.ResuViewNum'
    		Set @SQL_Result = 'Select ' + @Fields + ' From 
    		(Select ROW_NUMBER() Over(Order By '+@SqlOrderBy+') as RowId, ' + @RowFields + ' From  
    			#PM_QuantitativeStatisticsForEmployee As Q
    			Left JOIN 
    			Link_22_3.GANGYUNOA.dbo.HR_EmployeeAuxiliaryStation AS A ON A.AuxiliaryID = Q.AuxiliaryID
    			Left JOIN
    			Link_22_3.GANGYUNOA.dbo.ORG_Company AS OC ON A.CompanyID = OC.CompanyID 
    			Left JOIN
    			Link_22_3.GANGYUNOA.dbo.HR_Employee AS E ON A.EmployeeID = E.EmployeeID 
    			Left JOIN
    			Link_22_3.GANGYUNOA.dbo.ORG_Station AS OS ON Q.StationID = OS.StationID'
    			+ ') As X where RowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
    		Print @SQL_Result
    		EXEC (@SQL_Result)		
    		Drop Table #PM_QuantitativeStatisticsForEmployee
    END Try
    Begin Catch
    		--SELECT 
    		--ERROR_NUMBER() as ErrorNumber,
    		--ERROR_MESSAGE() as ErrorMessage;
            
    		--去重
    		Declare @AuxiliaryID uniqueidentifier
    		Declare @DCategory int
    		Declare @RecordTime datetime
    		Declare @IsUse int
    		Declare QuantitativeCursor Cursor local static read_only forward_only For 
    		Select AuxiliaryID,Category,RecordTime,IsUse from PM_QuantitativeStatisticsForBusinessTripResource
    		Group By AuxiliaryID,Category,RecordTime,IsUse
    		Having(COUNT(0)>1)
    		Open QuantitativeCursor
    		FETCH NEXT FROM QuantitativeCursor Into @AuxiliaryID,@DCategory,@RecordTime,@IsUse
    		WHILE @@FETCH_STATUS = 0
    			BEGIN
    					Declare @BusinessTripResourceID uniqueidentifier
    					--取得首条
    					Select top 1 @BusinessTripResourceID=BusinessTripResourceID from PM_QuantitativeStatisticsForBusinessTripResource
    					where AuxiliaryID=@AuxiliaryID and Category=@DCategory and RecordTime=@RecordTime and IsUse=@IsUse
    					--删除多余
    					Delete From PM_QuantitativeStatisticsForBusinessTripResource
    					Where BusinessTripResourceID<>@BusinessTripResourceID
    					and AuxiliaryID=@AuxiliaryID and Category=@DCategory and RecordTime=@RecordTime and IsUse=@IsUse
    
    					FETCH NEXT FROM QuantitativeCursor Into @AuxiliaryID,@DCategory,@RecordTime,@IsUse
    			END
    		CLOSE QuantitativeCursor
    		DEALLOCATE QuantitativeCursor
    		
    End Catch

    它这个存储过程是求和,我想把所有的都查出来,看看那条数据没有对上,这个的怎么看一下啊?

    字段是ResuViewNum


    please verify my account


    • 已编辑 lctk 2016年9月24日 0:02
    2016年9月23日 23:56

答案

全部回复

  • 如果只是对比结果,建议分别将计算结果保存到一个临时表,然后分析这个临时表。

    没有你数据库环境,直接给我们sql也很难帮你看。


    family as water

    • 已标记为答案 lctk 2016年9月29日 3:04
    2016年9月24日 0:18
  • 怎么保存到临时表啊,第二个他是一个存储过程,他求的是和,我不想求和,想看每条数据。


    please verify my account

    2016年9月24日 0:22
  • 我数据库是192.168.8.246

    登录名sa


    你能连上吗?看看啊


    please verify my account


    • 已编辑 lctk 2016年9月24日 3:13
    2016年9月24日 0:24
  • 存储过程:

    exec PM_QuantitativeStatisticsForEmployee 171,'2016-07-28',' and (UpdateTime between ''2016-07-28 00:00:00'' and ''2016-07-28 23:59:59'') ','Q.StockHousingResourcesNum desc',20,1,null,null



    please verify my account

    2016年9月24日 0:26
  • 第一个查询:

    select * from [dbo].[HC_ClientSeeHouse]  where IsDelete=0 and State=3 and TransactionType=171 and  IsLiangHua=1
    and(IsSeeClient<>1 or SeeHouseResult is  null or SeeHouseResult is not null and IsSeeClient<>1 or SeeHouseResult is  null and IsSeeClient=1 or IsSeeClient is null)
    and  ( SeeHouseResult is null or FirstTime<>SeeHouseTime or  SeeHouseResult is not null and  FirstTime<>SeeHouseTime or FirstTime=SeeHouseTime and SeeHouseResult is null or FirstTime is null or SeeHouseTime is null) and
    DepartmentID in ( SELECT ID FROM [dbo].[GetChildDepartment] ( 'c741f993-8855-4754-8055-e54fd779927f')) and RecordTime>='2016-07-28' and RecordTime<='2016-07-28 23:59'


    please verify my account

    2016年9月24日 0:27
  • Are you kidding? Post your server's sa pwd in public?
    • 已标记为答案 lctk 2016年9月29日 3:04
    2016年9月24日 2:53
  • 测试库,应该没事吧?

    please verify my account

    2016年9月24日 3:14
  • 你好, lctk

    >>怎么保存到临时表啊,第二个他是一个存储过程,他求的是和,我不想求和,想看每条数据。

    如下的相关帖子是将存储过程的结果保存到临时表,你可以看下。

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f456960b-dbd9-4fae-ab07-c6c09af17fb3/select-into-table-from-a-stored-procedure-possible?forum=transactsql

    上面你的存储过程中,你可以先把#PM_QuantitativeStatisticsForEmployee @SQL_Query 的代码拿出来,新建一个查询窗口,把里面的参数去掉换成你需要的值,然后看看效果。根据你的描述,你可以把GROUP BY和相关聚合函数给去掉再做查询。建议你先分析下存储过程中的查询语句,分离出你想要的结果,然后自己动手尝试下。

    Best Regards,

    Albert Zhang

    • 已标记为答案 lctk 2016年9月29日 3:04
    • 取消答案标记 lctk 2016年9月29日 3:06
    • 已标记为答案 lctk 2016年9月29日 3:06
    2016年9月26日 5:27
  • hi

    select a.* into #t 
    
    from openrowset('SQLNCLI', 'Server=192.168.8.246;Trusted_Connection=yes;',
    
    'SET NOCOUNT ON;SET FMTONLY OFF ; 
    
    EXECUTE [PM_GetTakeLookStatistical]   10000,1,''2016-07-28 00:00:00'',''2016-07-28 23:59:59'',null,null,null,null') AS a;
    
    select * from #t;
    

    消息 7416,级别 16,状态 2,第 1 行
    对远程服务器的访问遭拒绝,因为不存在登录映射。

    不存在登录映射那


    please verify my account

    2016年9月26日 6:40
  • INSERT INTO MyTable  
    (  
        Col1,  
        Col2,  
    	 Col3,  
        Col4,
    	 Col5,  
        Col6,
    	 Col7,  
        Col8,
    	 Col9,  
        Col10,
    	 Col11,  
        Col12,
    	 Col3,  
        Col14,
    	Col15
    )  
    exec [PM_GetTakeLookStatistical] 10000,1,'2016-07-28 00:00:00','2016-07-28 23:59:59',null,null,null,null 
    这个是需要自己创建个MyTable表吗?是这个意思吗?

    please verify my account

    2016年9月26日 6:57
  • Create Table #PM_QuantitativeStatisticsForEmployee
    (
    rowId int,
    	DepartmentID uniqueidentifier
    	,DepartmentName nvarchar(50)
    
    	,StationName  nvarchar(50)
    	,Employeeid uniqueidentifier
    	,EmployeeName nvarchar(50)
    	,LevelID uniqueidentifier
    		,AuxiliaryID uniqueidentifier
    		,Num int
    		,NullNum int
    		,LookNum int
    		,TakeNum int
    		,SinNum int
    		,AbountNum int
    		,SecondNum int
    		,FirstNum int
    )
    
    
    INSERT INTO #PM_QuantitativeStatisticsForEmployee  
    (  
       DepartmentID 
    	,DepartmentName 
    
    	,StationName  
    	,Employeeid 
    	,EmployeeName 
    	,LevelID 
    		,AuxiliaryID 
    		,Num 
    		,NullNum 
    		,LookNum 
    		,TakeNum 
    		,SinNum 
    		,AbountNum 
    		,SecondNum 
    		,FirstNum 
    )  
    exec [PM_GetTakeLookStatistical] 10000,1,'2016-07-28 00:00:00','2016-07-28 23:59:59',null,null,null,null 

    消息 206,级别 16,状态 2,过程 PM_GetTakeLookStatistical,第 19 行
    操作数类型冲突: bigint 与 uniqueidentifier 不兼容

    这个我需要一个一个对照类型,有没有简单点的办法阿?


    please verify my account

    2016年9月26日 7:10
  • 你好, lctk

    是的,如果按你上面的方式需要你首先定义好表结构。

    >>对远程服务器的访问遭拒绝,因为不存在登录映射。

    另外,针对这个错误,你可以尝试传入用户名和密码的方式。详细情况如下链接。

    https://msdn.microsoft.com/en-us/library/ms190312.aspx

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b05d1471-e55a-4c5c-8845-d91629d1bb39/using-openrowset-to-retrieve-data-from-a-server-under-windows-authentication?forum=transactsql

    >>操作数类型冲突: bigint uniqueidentifier 不兼容

    建议你首先查看下存储过程的返回结果中各个字段的类型,然后对照你的表再一一修改。另外,你可以尝试把你表中引起错误的那一列改成 varchar(100)的试试看。

    Best Regards,

    Albert Zhang

    • 已标记为答案 lctk 2016年9月29日 3:04
    2016年9月26日 7:17
  • select a.* into #t111 
    
    from openrowset('SQLOLEDB', 'Data Source=192.168.8.246;User ID=sa;Pwd=0.1d2c3b4aO*Tfmfdu_l;Trusted_Connection=yes;database=[Property];
    
    Persist Security Info=False',
    
    '
    
    EXECUTE [PM_GetTakeLookStatistical]   10000,1,''2016-07-28 00:00:00'',''2016-07-28 23:59:59'',null,null,null,null') AS a;
    
    select * from #t111;

    消息 7416,级别 16,状态 2,第 1 行
    对远程服务器的访问遭拒绝,因为不存在登录映射。

    hi,Albert_ Zhang

    连接不上那,你连接看看啊


    please verify my account


    • 已编辑 lctk 2016年9月26日 7:37
    2016年9月26日 7:37
  • 1.你给的数据库地址明显是内网的

    2.建议先好好学习下sql语句,至少能看明白那个存储过程

    • 已标记为答案 lctk 2016年9月29日 3:04
    2016年9月27日 9:41