sql查询读取多个表数据。 [英] sql query for read multiple tables data.

问题描述

表1

区中心s_No 
 Chittoor VenkatagiriKota  20004  
 Hyderabad CivilSupply  20005  
 Ananthapur NGOHome  20006  
 Ananthapur Ananthapur  20007  
 Chittoor Chittoor  20008  
 Ananthapur NGOHome  20010  
 Ananthapur Ananthapur  20020  

Tble2

 Id S_No 
  1080200062412120131215133536   20004  
  1080200062412220130509170801   20005  
  1080200062412320130509171628   20006  
  1080200062412420130509172121   20007  
  1080200062412520130509172512   20008  

table3

 id s_No名称
  1080200062412120131215133536  < span class =code-digit> 20004  aaa 
  1080200062412420130509172121   20007  bbb 
  1080200062413220130509181948   20010  xxx 
  1080200062413320130509182836   20020  yyy 

O / P;

区中心S_No Id状态
 Chittoor VenkatagiriKota  20004   1080200062412120131215133536  匹配 
 Hyderabad CivilSupply  20005   1080200062412220130509170801 无与伦比的
 Ananthapur NGOHome  20006   1080200062412320130509171628 无与伦比的
 Ananthapur Ananthapur  20007   1080200062412420130509172121  匹配 
 Chittoor Chittoor  20008   1080200062412520130509172512 无与伦比的
 Ananthapur NGOHome  20010   1080200062413220130509181948 新增
 Ananthapur An anthapur  20020   1080200062413320130509182836 新增

配对是指：如果Table2和Table3记录匹配状态匹配

不匹配意味着：如果Table2和Table3记录在不匹配状态下是不匹配的状态

新添加：如果在table3状态中添加了新记录是新增的。

我正在使用下面的查询。但它会显示一个错误

   cte  as （选择 CenterDetails.District，CenterDetails.CenterName，NewDataParsing.EA_STATION_ID， case  isnull（OldDataParsing。 ENROLMENT_PACKET， 0 ）   0  然后 ' 新添加的'  else  ' 匹配' 结束  as 状态
 来自 OldDataParsing 正确 外部  join  NewDataParsing  on  OldDataParsi ng.EA_STATION_ID = NewDataParsing.EA_STATION_ID 
  inner   join  CenterDetails  on  NewDataParsing.EA_STATION_ID = CenterDetails.EnrolmentStation）
 选择 CenterDetails.District，CenterDetails.CenterName，CenterDetails.EnrolmentStation，isnull（cte。 status，'  Un_matched'） as 状态来自 CenterDetails  left  外部  join  cte  on  CenterDetails.EnrolmentStation = cte.EA_STATION_ID 

error是

消息 248 ，等级 16  ，状态 1 ，行 2  
转换   nvarchar 值' < span class =code-string> 1080200073724520130524170256'溢出 int  列。

解决方案

错误显示OverFlow正在发生...所以尝试将整数转换为Nvarchar数据类型..而不是将Nvarchar转换为整数..

Table1

District      	Center		        s_No
Chittoor	        VenkatagiriKota         20004
Hyderabad   	CivilSupply	                20005 
Ananthapur	NGOHome 	        20006	
Ananthapur	Ananthapur                20007
Chittoor	        Chittoor                     20008
Ananthapur	NGOHome                20010	
Ananthapur	Ananthapur               20020

Tble2

Id                                                    S_No	
1080200062412120131215133536 	   20004
1080200062412220130509170801          20005
1080200062412320130509171628          20006 
1080200062412420130509172121          20007
1080200062412520130509172512          20008

table3

id	                                                s_No  	Name
1080200062412120131215133536	20004	aaa
1080200062412420130509172121	20007	bbb
1080200062413220130509181948 	20010       xxx
1080200062413320130509182836 	20020	yyy

O/P;

District        Center               S_No            Id                                       Status                       
Chittoor      VenkatagiriKota  20004  1080200062412120131215133536   Matched
Hyderabad  CivilSupply	 20005  1080200062412220130509170801 Unmatched
Ananthapur  NGOHome       20006  1080200062412320130509171628 Unmatched 
Ananthapur  Ananthapur      20007  1080200062412420130509172121 Matched
Chittoor        Chittoor 	         20008  1080200062412520130509172512 Unmatched
Ananthapur   NGOHome      20010 1080200062413220130509181948 Newlyadded
Ananthapur   Ananthapur     20020 1080200062413320130509182836 Newlyadded

Matched means:if Table2 and Table3 record is matched status in matched
Unmatched means:if Table2 and Table3 record is unmatched status in unmatched
Newly added:if new record is added in table3 status is newly added.
i am using bellow query. but it will shows one error

with cte as (select CenterDetails.District,CenterDetails.CenterName,NewDataParsing.EA_STATION_ID,case isnull(OldDataParsing.ENROLMENT_PACKET,0) when 0 then 'Newly added' else 'Matched' end as status
from  OldDataParsing right outer join  NewDataParsing on OldDataParsing.EA_STATION_ID = NewDataParsing.EA_STATION_ID
inner join  CenterDetails on NewDataParsing.EA_STATION_ID = CenterDetails.EnrolmentStation)
select CenterDetails.District,CenterDetails.CenterName,CenterDetails.EnrolmentStation,isnull(cte.status,'Un_matched') as Status from  CenterDetails left outer join cte on CenterDetails.EnrolmentStation = cte.EA_STATION_ID

error is

Msg 248, Level 16, State 1, Line 2
The conversion of the nvarchar value '1080200073724520130524170256 ' overflowed an int column.

解决方案

Error Shows that OverFlow is Occuring... So try Converting that Integer to Nvarchar Datatype.. not Nvarchar to Integer..

这篇关于sql查询读取多个表数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！