如何使用BusinessId列的不同关键字？ [英] How to use Distinct Keyword for BusinessId Column?

问题描述

嗨



这里我有一个程序并使用这个程序我从几个列获取数据但businessId列有几个重复。如何摆脱重复。

如何在这里使用distinct关键字。有人帮助我。

Hi

Here I have a procedure and using this procedure iam getting tha data from several columns but businessId column has several duplicates.how to get rid of the duplicates.
how to use distinct keyword here. some one help me.

ALTER procedure CORP.USP_GET_BUSINESSDETAILS_SEARCH
 (     @BusinessID INT = NULL
	,@BusinessName VARCHAR(255) = NULL
	,@PageID INT = NULL
	,@PageCount INT = NULL
)

as begin
DECLARE @StartFrom INT = (@PageID -1) * @PageCount
IF @PageID = 1 OR @PageID IS NULL
	BEGIN
SELECT TOP(@PageCount) B.BusinessId,B.BusinessName,B.CreatedDate,BS.[Description] as Status,CONCAT(A.StreetAddress1,',',A.StreetAddress2,',',A.City,',',A.State,',',A.Zip5,',',Zip4) as PrincipalOfficeAddress, B.EmailAddress,B.DateOfIncorporation 
 from [CORP].[Business] as B
 inner join [CORP].[BusinessXAddress] as BA on B.BusinessId=BA.BusinessId
 inner join [CORP].[Address] as A on BA.AddressId=A.AddressId  
 Inner join [CORP].[BusinessStatus] as BS on BS.BusinessStatusId = B.BusinessStatusId
 WHERE   (@BusinessName IS NULL OR B.BusinessName LIKE @BusinessName + '%' ) 
 ORDER BY B.BusinessID
 SELECT COUNT(1) AS TotalRowCount
		FROM CORP.Business B WITH(NOLOCK)
		WHERE (@BusinessID IS NULL OR B.BusinessID = @BusinessID)
		AND (@BusinessName IS NULL OR BusinessName LIKE '%' + @BusinessName + '%')
END
	ELSE
	BEGIN
		SELECT Distinct B.BusinessID,B.BusinessName, BT.BusinessTypeDesc AS BusinessType,BS.[Description] AS [Status],CONCAT(A.StreetAddress1,',',A.StreetAddress2,',',A.City,',',A.[State],',',A.Zip5,',',Zip4) as PrincipalOfficeAddress, B.EmailAddress, B.DateOfIncorporation  
		FROM CORP.Business B WITH(NOLOCK)
		INNER JOIN CORP.BusinessType BT ON BT.BusinessTypeId = B.BusinessTypeId
		inner join [CORP].[BusinessXAddress] as BA on B.BusinessId=BA.BusinessId
		inner join [CORP].[Address] as A on BA.AddressId=A.AddressId  
		INNER JOIN CORP.BusinessStatus BS ON BS.BusinessStatusId= B.BusinessStatusId
		WHERE (@BusinessID IS NULL OR B.BusinessID = @BusinessID)
		AND (@BusinessName IS NULL OR BusinessName LIKE '%' + @BusinessName + '%')
		ORDER BY B.BusinessID
		OFFSET @StartFrom ROWS FETCH NEXT @PageCount ROWS ONLY
	END
END

先谢谢

Thanks in Advance

推荐答案

试试这个（查询会有不好的表现）：

Try this (query can have bad performance):

SELECT DISTINCT TOP(@PageCount) *
FROM (SELECT B.BusinessID,B.BusinessName, BT.BusinessTypeDesc AS BusinessType,BS.[Description] AS [Status],CONCAT(A.StreetAddress1,',',A.StreetAddress2,',',A.City,',',A.[State],',',A.Zip5,',',Zip4) as PrincipalOfficeAddress, B.EmailAddress,
    B.DateOfIncorporation
    FROM CORP.Business B WITH(NOLOCK)
    INNER JOIN CORP.BusinessType BT ON BT.BusinessTypeId = B.BusinessTypeId
    inner join [CORP].[BusinessXAddress] as BA on B.BusinessId=BA.BusinessId
    inner join [CORP].[Address] as A on BA.AddressId=A.AddressId
    INNER JOIN CORP.BusinessStatus BS ON BS.BusinessStatusId= B.BusinessStatusId
    WHERE (@BusinessID IS NULL OR B.BusinessID = @BusinessID)
    AND (@BusinessName IS NULL OR BusinessName LIKE '%' + @BusinessName + '%')
    ORDER BY B.BusinessID
    OFFSET @StartFrom ROWS)  as x

这篇关于如何使用BusinessId列的不同关键字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！