SQL查询从表中选择并在另一列上分组 [英] SQL query select from table and group on other column

问题描述

我对问题标题的措辞很差，因为我不确定该怎么称呼我想做的事情，但这确实很简单.

I'm phrasing the question title poorly as I'm not sure what to call what I'm trying to do but it really should be simple.

我有一个带有两个ID列的链接/联接表.我想先进行检查，然后再将新行保存到表中.

I've a link / join table with two ID columns. I want to run a check before saving new rows to the table.

用户可以通过网页保存属性，但是在保存之前，我需要检查是否存在相同的组合.有了一条记录，很容易，很明显，您只需检查该属性标识是否已在表中，如果不允许，则不允许他们再次保存.

The user can save attributes through a webpage but I need to check that the same combination doesn't exist before saving it. With one record it's easy as obviously you just check if that attributeId is already in the table, if it is don't allow them to save it again.

但是，如果用户选择了该属性和另一个属性的组合，则应该允许他们保存该属性.

However, if the user chooses a combination of that attribute and another one then they should be allowed to save it.

这是我的意思的图片:

因此，如果用户现在尝试保存ID为1的属性，它将停止它们，但是，如果他们尝试ID为1、10的情况，只要1和10具有相同的productAttributeId，我也需要它来停止它们.

So if a user now tried to save an attribute with ID of 1 it will stop them, but I need it to also stop them if they tried ID's of 1, 10 so long as both 1 and 10 had the same productAttributeId.

我在解释中感到困惑，但我希望图像能阐明我需要做的事情.

I'm confusing this in my explanation but I'm hoping the image will clarify what I need to do.

这应该很简单，所以我想我缺少了一些东西.

This should be simple so I presume I'm missing something.

推荐答案

如果我对问题的理解正确，则希望防止重复使用AttributeId和ProductAttributeId的组合.如果是这种情况，只需将它们组合为一个主键，这自然就是UNIQUE.

If I understand the question properly, you want to prevent the combination of AttributeId and ProductAttributeId from being reused. If that's the case, simply make them a combined primary key, which is by nature UNIQUE.

如果这不可行，则创建一个存储过程，该存储过程针对AttributeId实例的联接运行查询.如果查询返回0个实例，请插入该行.

If that's not feasible, create a stored procedure that runs a query against the join for instances of the AttributeId. If the query returns 0 instances, insert the row.

这里有一些简单的代码可以介绍这个想法(可能需要修改才能与您的数据库一起使用):

Here's some light code to present the idea (may need to be modified to work with your database):

SELECT COUNT(1) FROM MyJoinTable WHERE AttributeId = @RequestedID
IF @@ROWCOUNT = 0
BEGIN
   INSERT INTO MyJoinTable ...
END

这篇关于SQL查询从表中选择并在另一列上分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！