sql server: left join 重复数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93

---涂聚文 2017-9-28 SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord  WHERE     VipExamMailProjectId = 1 GROUP BY VipExamMailStaffID,VipExamMailProjectId  HAVING COUNT(1)>1 ORDER BY VipExamMailStaffID GO   --192 SELECT * FROM (SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff) a LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId = 1 GROUP BY VipExamMailStaffID,VipExamMailProjectId  HAVING COUNT(1)>1) b ON a.BranchStaffID = b.VipExamMailStaffID  WHERE a.BranchStaffCountryID=1 GO   --153 香港 SELECT * FROM VipBranchStaff WHERE BranchStaffCountryID=1 AND BranchStaffIs=1 GO   --View_VipExamMailRecordProject   SELECT a.*,b.VipExamMailProjectId AS 'ExamProjectId',[dbo].getVipExamBranchSimpleName(a.BranchStaffCode) as 'BranchAreaName',[dbo].getAreaName(a.BranchStaffArea) as 'AreaName',dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) AS 'ServerName',  dbo.getVipExamBrandServiceNo(a.BranchStaffCode) AS 'BrandService',[dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) as 'IsSucces',dbo.getStaffProjectTimes(b.VipExamMailProjectId,a.BranchStaffID) AS 'ExamTimes',dbo.getVipExamMailDate(b.VipExamMailProjectId,a.BranchStaffID) AS 'VipExamMailDate'  FROM (SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff WHERE BranchStaffCountryID=1 AND BranchStaffIs=1) a LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId = 1 GROUP BY VipExamMailStaffID,VipExamMailProjectId  HAVING COUNT(1)>1) b ON a.BranchStaffID = b.VipExamMailStaffID  --WHERE a.BranchStaffCountryID=1  GO     --国内 --2425 SELECT * FROM VipBranchStaff WHERE BranchStaffCountryID=2 AND BranchStaffIs=1 GO   SELECT a.*,b.VipExamMailProjectId AS 'ExamProjectId',[dbo].getVipExamBranchSimpleName(a.BranchStaffCode) as 'BranchAreaName',[dbo].getAreaName(a.BranchStaffArea) as 'AreaName',dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) AS 'ServerName',  dbo.getVipExamBrandServiceNo(a.BranchStaffCode) AS 'BrandService',[dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) as 'IsSucces',dbo.getStaffProjectTimes(b.VipExamMailProjectId,a.BranchStaffID) AS 'ExamTimes' FROM (SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff WHERE BranchStaffCountryID=2 AND BranchStaffIs=1) a LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId =2 GROUP BY VipExamMailStaffID,VipExamMailProjectId  HAVING COUNT(1)>1) b ON a.BranchStaffID = b.VipExamMailStaffID GO   SELECT a.*,b.VipExamMailProjectId AS 'ExamProjectId',[dbo].getVipExamBranchSimpleName(a.BranchStaffCode) as 'BranchAreaName',[dbo].getAreaName(a.BranchStaffArea) as 'AreaName',dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) AS 'ServerName',  dbo.getVipExamBrandServiceNo(a.BranchStaffCode) AS 'BrandService',[dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) as 'IsSucces',dbo.getStaffProjectTimes(b.VipExamMailProjectId,a.BranchStaffID) AS 'ExamTimes',dbo.getVipExamMailDate(b.VipExamMailProjectId,a.BranchStaffID) AS 'VipExamMailDate' FROM (SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff WHERE BranchStaffCountryID=2 AND BranchStaffIs=1) a LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId =2 GROUP BY VipExamMailStaffID,VipExamMailProjectId  HAVING COUNT(1)>1) b ON a.BranchStaffID = b.VipExamMailStaffID WHERE dbo.getVipExamBrandServiceNo(a.BranchStaffCode)='013180'  ORDER BY a.BranchStaffCode GO   --2017-09-27修改 合格详情列表 IF EXISTS (select * from sysobjects where [name] = '[proc_Select_HKQualifiedNoDetails]') DROP PROCEDURE proc_Select_HKQualifiedNoDetails GO CREATE PROCEDURE [dbo].proc_Select_HKQualifiedNoDetails (     @ProjectID int,     @CountryID int,     @BrandService VARCHAR(50),     @search nvarchar(100) ) as declare @sql nvarchar(4000),@where nvarchar(4000) set @sql='SELECT a.*,b.VipExamMailProjectId AS ''ExamProjectId'',[dbo].getVipExamBranchSimpleName(a.BranchStaffCode) as ''BranchAreaName'',[dbo].getAreaName(a.BranchStaffArea) as ''AreaName'',dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) AS ''ServerName'',  dbo.getVipExamBrandServiceNo(a.BranchStaffCode) AS ''BrandService'',[dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) as ''IsSucces'',dbo.getStaffProjectTimes(b.VipExamMailProjectId,a.BranchStaffID) AS ''ExamTimes'',dbo.getVipExamMailDate(b.VipExamMailProjectId,a.BranchStaffID) AS ''VipExamMailDate'' FROM (SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff WHERE BranchStaffIs=1 and BranchStaffCountryID='+ cast(@CountryID as varchar)+') a' +' LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId ='+ cast(@ProjectID as varchar)+' GROUP BY VipExamMailStaffID,VipExamMailProjectId  HAVING COUNT(1)>1) b ON a.BranchStaffID = b.VipExamMailStaffID' set @where='' IF @BrandService<>'' AND @Search=''     SET  @where=@where+' where dbo.getVipExamBrandServiceNo(a.BranchStaffCode) like ''%'+@BrandService +'%''' if @Search<>'' AND @BrandService='' begin     set @where=@where+' where (a.BranchStaffCode like ''%'+@search +'%'' or [dbo].getVipExamBranchSimpleName(a.BranchStaffCode) like ''%'+@search +'%'' or [dbo].getAreaName(a.BranchStaffArea) like ''%'+@search +'%''  or dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode))  like ''%'+@search +'%'' or [dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) like ''%'+@search +'%''  or dbo.getVipExamBrandServiceNo(a.BranchStaffCode) like ''%'+@search +'%'')' END IF @BrandService<>'' AND @Search<>'' BEGIN    SET  @where=@where+' where dbo.getVipExamBrandServiceNo(a.BranchStaffCode) like ''%'+@BrandService +'%'''    set @where=@where+' and (a.BranchStaffCode like ''%'+@search +'%'' or [dbo].getVipExamBranchSimpleName(a.BranchStaffCode) like ''%'+@search +'%'' or [dbo].getAreaName(a.BranchStaffArea) like ''%'+@search +'%''  or dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode))  like ''%'+@search +'%'' or [dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) like ''%'+@search +'%''  or dbo.getVipExamBrandServiceNo(a.BranchStaffCode) like ''%'+@search +'%'')' END set @sql=@sql+@where+' order by ExamTimes' print @sql exec(@sql) GO   EXEC proc_Select_HKQualifiedNoDetails 1,1,'','' GO