sql server: left join 重覆數據

---塗聚文 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