时间戳

首页 > 解决方案 > 获得超过 1 个不同值的结果

sql - 获得超过 1 个不同值的结果

问题描述

我有一个这样的查询：

SELECT [DesignGroupId], [dgc].[CustomerKey]
FROM [DesignGroupCustomer] AS [DGC]
INNER JOIN [Customer] AS [C] ON [DGC].[CustomerKey] = [C].[CustomerKey]
WHERE [C].[CustTypeKey] = 7
GROUP BY [DesignGroupId], [DGC].[CustomerKey]
ORDER BY [DesignGroupId]

它返回值如下：

+--------------------------------------+------+
| A0E6E679-1CE3-4804-AF90-00022296CFA1 | 4644 |
| 0FFCFC8F-51EF-43FB-B2AE-000603ECC1EA | 4644 |
| 6CDC64F7-722C-4BA6-AE4E-0010F1C95F58 |  283 |
| 6CDC64F7-722C-4BA6-AE4E-0010F1C95F58 | 3832 |
+--------------------------------------+------+

我想做的是知道 DesignGroups 有什么不同的值CustomerKey，所以我想要的结果是只得到一行

6CDC64F7-722C-4BA6-AE4E-0010F1C95F58

因为这个 ID 有超过 1 个不同CustomerKey

我尝试使用 HAVING COUNT 像：

SELECT [DesignGroupId], [dgc].[CustomerKey]
FROM [DesignGroupCustomer] AS [DGC]
INNER JOIN [Customer] AS [C] ON [DGC].[CustomerKey] = [C].[CustomerKey]
WHERE [C].[CustTypeKey] = 7
GROUP BY [DesignGroupId], [DGC].[CustomerKey]
ORDER BY [DesignGroupId]
HAVING COUNT(DGC.CustomerKey) > 1

但它不起作用，它返回我不想要的其他值。我怎样才能做到这一点？问候

标签： sqlsql-servertsql

解决方案

只需从非聚合列中删除​​客户键：

SELECT [DesignGroupId]
FROM [DesignGroupCustomer] AS [DGC]
INNER JOIN [Customer] AS [C] ON [DGC].[CustomerKey] = [C].[CustomerKey]
WHERE [C].[CustTypeKey] = 7
GROUP BY [DesignGroupId]
HAVING COUNT(DISTINCT [DGC].[CustomerKey]) > 1

我DISTNCT在计数中使用，因为不清楚您的样本数据是否可能在DesignGroupId/上包含重复项CustomerKey。如果不是这种情况，您可以删除DISTINCT.

推荐阅读