SQL分组聚合为不同的值

I have a database table that looks like:

我有一个数据库表,看起来像:


ForeignId: int
Key: varchar
Value: varchar

Where ForeignId and Key constitute a unique primary key

其中ForeignId和Key构成唯一的主键

I can easily determine the total set of keys defined for a given set of document with

我可以轻松确定为给定文档集定义的密钥总集


SELECT DISTINCT [Key] FROM [Table] WHERE [ForeignId] IN (...)

What I would like to do however is to further distinguish the values of each values of each property in the case where it is the same for every ForeignId (and NULL or some other sentinal value in the case where there the values differ).

然而,我想要做的是进一步区分每个属性的每个值的值,对于每个ForeignId它们是相同的(并且在值不同的情况下为NULL或一些其他的sentinal值)。

If I do something like:

如果我这样做:

ForeignId  Key  Value
1            1      A
1            2      B
1            3      C
2            1      A
2            2      Z
3            1      A
3            2      Z

I want output like:

我想输出像:

Key   Value
1     A      -- All 3 are the same
2     NULL   -- More than one distinct value (B, and Z)
3     NULL   -- Defined for only one ForeignId

The best I have been able to come up with is

我能想到的最好的是


SELECT [Key], MAX([Value]), MIN([Value]) FROM [Table]
WHERE [ForeignId] IN (...)
GROUP BY [Key]

and looking for any instances where the max and min values returned differ. If they are the same I assume all of the values match, if they differ, I know there are more than one distinct values.

并查找返回的最大值和最小值不同的任何实例。如果它们是相同的,我假设所有的值都匹配,如果它们不同,我知道有不止一个不同的值。

What's missing here is the 3rd part where I need to mark values as differing if any of the individual items doesn't have a definition at all. In my example above, my current code would return the value C for key 3 even though it is not defined for some of the ForeignIds.

这里缺少的是第3部分,如果任何单个项目根本没有定义,我需要将值标记为不同。在上面的示例中,我的当前代码将返回键3的值C,即使它没有为某些ForeignId定义。

3 个解决方案

#1

Ok - so if I understand your question you need to generate a list containing two columns, key and value.

好的 - 所以,如果我理解你的问题,你需要生成一个包含两列,键和值的列表。

The list will have a single row for each and every key, the value column can be defined as:

每个键的列表都有一行,value列可以定义为:

NULL if it is not defined for all ForeignID' NULL if it is defined for all keys but contains multiple values The value if it is defined for all keys, with that value being consistently the same?

如果没有为所有ForeignID定义NULL,则为NULL如果为所有键定义但是包含多个值,则为NULL如果为所有键定义了值,该值始终相同?

So, I think we want to do something like this:

所以,我想我们想做这样的事情:

SELECT
    TABLE.KEY,
    data.VAL
FROM
    TABLE
LEFT JOIN
(
   SELECT KEY, VALUE FROM TABLE GROUP BY KEY, VALUE HAVING COUNT(*) = x
) data

where x is the number of distinct keys (which you may want to throw into a subquery).

其中x是不同键的数量(您可能希望将其放入子查询中)。

The left join will ensure you list all keys, the subquery will only return values for the case when the count of the grouping is equal to the number of keys.

左连接将确保列出所有键,子查询将仅在分组计数等于键数时返回大小写的值。

I don't have access to sql (am at home, new PC) to test my syntax, but I think the idea should get you on the right path.

我无法访问sql(我在家,新PC)来测试我的语法,但我认为这个想法应该让你走上正确的道路。

If I have misunderstood your question please let me know

如果我误解了您的问题,请告诉我

#2

I don't think you can do that without using some kind of code (a stored procedure would work) without it being REALLY ugly (and quite possibly slow).

如果不使用某种代码(存储过程可以工作),我认为你不能做到这一点,而不是它真的很丑(很可能很慢)。

Here is my best attempt. It's rather ugly. I wouldn't want to use this in production. I question how well it performs (time wise). This is MySQL speak, since that's what I'm familiar with

这是我最好的尝试。这很难看。我不想在生产中使用它。我怀疑它的表现如何(时间明智)。这是MySQL的说法,因为这就是我所熟悉的

SELECT key, IF(EXISTS (SELECT 1 FROM table t
                        WHERE t.key = key AND t.value != value),
               null, value)
FROM table
GROUP BY key
ORDER BY key

Here are the explanations of the parts:

以下是各部分的说明:

SELECT 1 FROM table t WHERE t.key = key AND t.value != value

Returns a row if there is another row in the table with the same key but a different value.

如果表中的另一行具有相同的键但具有不同的值,则返回一行。

The IF checks if the query above returned a row. If it did (the EXISTS works) then return the null (since there is a row with a different value). If it didn't return a row, then they all match and we return the value.

IF检查上面的查询是否返回了一行。如果它(EXISTS工作)然后返回null(因为有一个具有不同值的行)。如果它没有返回一行,那么它们都匹配,我们返回值。

The GROUP BY makes sure this is only executed once for each key.

GROUP BY确保每个键仅执行一次。

The ORDER BY makes it pretty.

ORDER BY让它漂亮。

#3

Chris pretty much had it. Add another group by and you're golden.

克里斯非常喜欢它。添加另一个组,你是金色的。

SELECT
    TABLE.KEY,
    data.VAL
FROM
    TABLE
LEFT JOIN
(
   SELECT KEY, VALUE FROM TABLE GROUP BY KEY, VALUE HAVING COUNT(*) = x
) data on Table.Key = data.Key
group by Table.Key, data.VAL

#1