我试图在表中 EntityId 相等的情况下使用COALESCE连接两行或三行，以便我的输出如下：
EmailAddress EntityId
12_abc@abc.com::::13_pqr@pqr.com 101
12_koi@koi.com::::13_poi@poi.com 102
现在我根据我的查询获得以下结果：
EmailAddress EmailTypeId EntityId
abc@abc.com 12 101
pqr@pqr.com 13 101
koi@koi.com 12 102
poi@poi.com 13 102
我正在使用的查询如下：
DECLARE @MyTableVariable TABLE
(
EmailAddress nvarchar(250),
EntityIDE int
);
INSERT @MyTableVariable(
EmailAddress,
EntityIDE
)
SELECT concat([EmailTypeID], '_____', [EmailAddress]))), [EntityID]
FROM [dbo].[Email_Addresses] where [EntityID] in
(select [EntityID] from [Entities] where [SourcePrimaryKey] in
(select [MerchantOwnerID] from [dbo].[Merchant_Owners] where [MerchantID] = 1 ) and
[EntityTypeID] = (select [EntityTypeID] from [Entity_Types] where [EntityType] = 'MerchantOwner'))
select * from @MyTableVariable
我已经定义了一个表值参数，并且在我的选择查询中我将两列连接成一个但我不知道如何使用 coalesce 来连接两个或多个 EntityId 相同的行 .
我在 where 之后的查询返回 EntityId 列的列表
(select [EntityID] from [Entities] where [SourcePrimaryKey] in
(select [MerchantOwnerID] from [dbo].[Merchant_Owners] where [MerchantID] = 1 ) and
[EntityTypeID] = (select [EntityTypeID] from [Entity_Types] where [EntityType] = 'MerchantOwner'))
以上查询返回以下结果集
EntityId
101
102
103
104
...
EDIT
; WITH CTE AS (
SELECT EA.[EmailTypeID], EA.[EmailAddress], EA.[EntityID]
FROM [dbo].[Email_Addresses] EA
JOIN [Entities] E ON E.[EntityID] = EA.[EntityID]
JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID])
,
ABC
As
(
SELECT EB.[PhoneNumber], EB.[EntityID], EB.[PhoneType]
FROM [dbo].[Phones] EB
JOIN [Entities] E ON E.[EntityID] = EB.[EntityID]
JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
)
SELECT STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress]
FROM CTE
WHERE [EntityID] = T1.[EntityID]
ORDER BY [EmailTypeID]
FOR XML PATH ('')),1,4,'') [EmailAddress],
T1.[EntityID],
STUFF((SELECT '::::' + CONVERT(NVARCHAR, [PhoneType]) + '_' + [PhoneNumber]
FROM ABC
WHERE [EntityID] = T2.[EntityID]
ORDER BY [PhoneType]
FOR XML PATH ('')),1,4,'') [PhoneNumber],
T2.[EntityID]
FROM CTE T1, ABC T2
GROUP BY T1.[EntityID],T2.[EntityID]
这个结果给了我56行，我的一个CTE返回8行，另一个CTE（ABC）为7行
我不知道它为什么会成倍增加
EDIT2
; WITH CTE AS (
SELECT EA.[EmailTypeID], EA.[EmailAddress], EA.[EntityID],
row_number() over(partition by EA.EntityID order by EA.EntityID desc) as rn
FROM [dbo].[Email_Addresses] EA
JOIN [Entities] E ON E.[EntityID] = EA.[EntityID]
JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
),
ABC
As
(
SELECT EB.[PhoneNumber], EB.[EntityID], EB.[PhoneType],
row_number() over(partition by EB.EntityID order by EB.EntityID desc) as rn
FROM [dbo].[Phones] EB
JOIN [Entities] E ON E.[EntityID] = EB.[EntityID]
JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
),
GHK
As
(
SELECT EB.[Address1], EB.[City], EB.[State],EB.[EntityID],
row_number() over(partition by EB.EntityID order by EB.EntityID desc) as rn
FROM [dbo].[Addresses] EB
JOIN [Entities] E ON E.[EntityID] = EB.[EntityID]
JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
),
PQR
As
(
SELECT EB.[MerchantOwnerID], EB.[FirstName], EB.[LastName],EB.[BusinessTitle],
EB.[OwnershipPercentage], EB.[DateOfBirth],E.[EntityID],
row_number() over(partition by E.EntityID order by E.EntityID desc) as rn
FROM [dbo].[Merchant_Owners] EB
JOIN [Entities] E ON E.[SourcePrimaryKey] = EB.[MerchantOwnerID] and [EntityTypeID]=2
JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
)
SELECT STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress]
FROM CTE
WHERE [EntityID] = c.[EntityID]
ORDER BY [EmailTypeID]
FOR XML PATH ('')),1,4,'') [EmailAddress],
c.[EntityID],
STUFF((SELECT '::::' + CONVERT(NVARCHAR, [PhoneType]) + '_' + [PhoneNumber]
FROM ABC
WHERE [EntityID] = c2.[EntityID]
ORDER BY [PhoneType]
FOR XML PATH ('')),1,4,'') [PhoneNumber],
c2.[EntityID] as pid,
c3.[City],c3.[State],c3.[EntityID], c4.[FirstName]
FROM CTE c FULL JOIN ABC c2 ON c.EntityID = c2.EntityID,
GHK c3, PQR c4
--FULL JOIN
--GHK c3 ON c.EntityID = c3.EntityID
WHERE c.rn = 1 OR c2.rn = 1 or c3.rn=1or c4.rn=1
group by c.[EntityID],c2.[EntityID],c3.EntityID,c4.EntityID
它抛出错误 Column 'GHK.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
如果我删除此字段然后它会在 GHK.State 处抛出错误，依此类推......
我的CTE（GHK）已经返回7行，所以我认为他们不需要聚合它 .
不知道如何到处走走 .
我的CTE（PQR）也返回了distict行 . 我也得到同样的错误 .
1 回答
根据您期望的输出，其中一个可以工作 .
如果您期望每个EmailTypeID的个别结果（例如12 ::: 13,12 ::: 14,13 ::: 14），那么以下内容将起作用 .
如果你只想要一个连接，那么这将有效：
作为旁注，我认为您的查询看起来更像是一系列连接而不是一系列IN语句 . 例如
编辑（一起显示整个查询）：
如果您更喜欢使用当前查询（或者由于某种原因我的查询不起作用），只需编辑CTE中的内容即可 .
EDIT2（添加了电话号码）：
EDIT3：
作为使用UNION如何使用当前查询的示例，这应该有效（并且避免需要GROUP BY，因为每个entityID只生成一行） . 编辑4：