IIF(COUNT)表达式和GROUP BY没有返回预期的结果

Question

我在一个select语句中使用SSMS，其中我有许多表要连接。其中一个字段需要标记为'M'或'N'，这取决于我使用的IIF(COUNT(*))的结果。

select语句如下:

SELECT
,   AcctChar.Chr1Nme             AS [Submitted For Party]   
,   P.LOCAL_CURR_CDE             AS [Notional Currency 1]
,   T.PortfolioCode              AS [Acct_ID]
,   T.IAMID                      AS [IAMID]
,   iif(count(*) > 1, 'M', 'N')  AS [ActionType]
,   T.Quantity                   AS [Quantity]
,   p.FLD1_AMT                   AS [Notional]
,   I.ISS_TMS                    AS [EffectiveDte]
from [dbo].[POSITION] AS p
INNER JOIN [dbo].[Trades] AS T
ON    T.PortfolioCode = P.ACCT_ID
INNER JOIN InfoPortal.dbo.ISSUE_DG AS i         
ON   i.instr_id = p.instr_id
LEFT OUTER JOIN infoportal.dbo.DW_AcctCharDG AS AcctChar
ON AcctChar.AcctId = p.acct_ID
GROUP BY
AcctChar.Chr1Nme
,   P.LOCAL_CURR_CDE
,   T.PortfolioCode
,   T.IAMID 
,   T.Quantity
,   p.FLD1_AMT  
,   I.ISS_TMS

上述查询返回正确的结果数，但[ActionType]错误。它将所有记录显示为'N'，这是不正确的。我需要if(count(*)>1， 'M'， 'N')语句来识别T中是否有任何记录(使用T. portfoliocode和T. iamid的唯一组合)，以识别T中是否只有一条记录(在这种情况下ActionType = 'N'或是否有>1记录在T中具有相同的PortfolioCode和IAMID组合，然后将其标记为'M'。

如果我只按T.PortfolioCode和T.IAMID分组，我会得到以下错误:"AcctChar列。Chr1Nme'在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。如果我加上AcctChar。

如果在Select语句中选择下一个字段，就会出现同样的错误。我如何配置iif(COUNT)子句为[ActionType]或GROUP BY只看T.PortfolioCode和T.IAMID组合，而不是select语句中的所有字段，以确定唯一性在T.PortfolioCode和T.IAMID组合方面?

Answer 1

如果不能自己运行它，这是一个猜测，但我认为您需要在子查询中计数:

SELECT
,   AcctChar.Chr1Nme             AS [Submitted For Party]   
,   P.LOCAL_CURR_CDE             AS [Notional Currency 1]
,   T.PortfolioCode              AS [Acct_ID]
,   T.IAMID                      AS [IAMID]
,   T.ActionType                 AS [ActionType]
,   p.FLD1_AMT                   AS [Notional]
,   I.ISS_TMS                    AS [EffectiveDte]
from [dbo].[POSITION] AS p
INNER JOIN (select    PortfolioCode  
,   IAMID
,   iif(count(*) > 1, 'M', 'N')  AS [ActionType]
from Trades group by PortfolioCode, IAMID
) AS T
INNER JOIN InfoPortal.dbo.ISSUE_DG AS i         
ON   i.instr_id = p.instr_id
LEFT OUTER JOIN infoportal.dbo.DW_AcctCharDG AS AcctChar
ON AcctChar.AcctId = p.acct_ID