英文:
Delete duplicates within a group of a group
问题
我有一个MSSQL数据库中的名为t_objectproperties的表，如下所示：
| PropertyID | ObjectID | Property |
|---|---|---|
| ID1 | Object1 | Property1 |
| ID2 | Object1 | Property1 |
| ID3 | Object1 | Property1 |
| ID4 | Object2 | Property1 |
我想要删除同一对象内的所有属性的重复项。因此，行(ID2，Object1，Property1和ID3，Object1，Property1)应该被删除，而行(ID1，Object1，Property1和ID4，Object2，Property1)不应被删除。
我总是希望保留同一对象内多个属性中最低的PropertyID。
到目前为止，我尝试了以下代码，但没有得到满意的结果（我尝试使用SELECT而不是DELETE，以首先确认我的语句是否正确）。
WITH CTE AS(
SELECT t_objectproperties.Property, t_objectproperties.ObjectID, t_objectproperties.PropertyID,
RN = ROW_NUMBER()OVER(PARTITION BY t_objectproperties.Property ORDER BY t_objectproperties.PropertyID ASC)
FROM t_objectproperties
)
SELECT * FROM CTE WHERE RN > 1
如果我按ObjectID进行分区，结果会有所不同，但仍然不是我所期望的。
此外，我尝试使用以下语句提取我的数据的正确格式。
SELECT t_objectproperties.ObjectID, t_objectproperties.Property, count(*)
From t_objectproperties
Group by t_objectproperties.ObjectID, t_objectproperties.Property
虽然GROUP BY正确提取了数据，但我无法使用该语句删除我的行。我需要以某种方式将这两个语句组合起来。
我在这里和谷歌上找到了很多答案，但它们从未想过要按三列进行分组/分区，然后删除它们。
如何提取/删除相应的数据？
英文:
I have a table t_objectproperties in a MSSQL database like follows
| PropertyID | ObjectID | Property |
|---|---|---|
| ID1 | Object1 | Property1 |
| ID2 | Object1 | Property1 |
| ID3 | Object1 | Property1 |
| ID4 | Object2 | Property1 |
I would like to delete all duplicates of a property within the same object. So the rows (ID2, Object1, Property1 AND ID3, Object1, Property1) should be deleted while the rows (ID1, Object1, Property1 AND ID4, Object2, Property1) must not be deleted.
I always want to keep the lowest PropertyID of the multiple Properties within the same object.
Until now I tried the following code but didn't get a satisfying result. (I tried with SELECT instead of DELETE, in order to first confirm that my statement is correct)
WITH CTE AS(
SELECT t_objectproperties.Property, t_objectproperties.ObjectID, t_objectproperties.PropertyID,
RN = ROW_NUMBER()OVER(PARTITION BY t_objectproperties.Property ORDER BY t_objectproperties.PropertyID ASC)
FROM t_objectproperties
)
SELECT * FROM CTE WHERE RN > 1
If I partition by ObjectID the result is different but still not what I'm looking for.
Further I tried extracting the correct format of my data with the following statement.
SELECT t_objectproperties.ObjectID, t_objectproperties.Property, count(*)
From t_objectproperties
Group by t_objectproperties.ObjectID, t_objectproperties.Property
While the groupy by extracts the data correctly, I can't use that statement to delete my rows. Somehow I need to combine the two statements.
I've found a lot of answers here and on google, but they never wanted to group/partition by 3 columns and then delete it.
How can I extract/delete the respective data?
答案1
得分: 2
那比预期的要容易得多。非常感谢Thom的评论。我必须根据ObjectID和Property进行分区。以下代码完美运行。
WITH CTE AS(
SELECT t_objectproperties.Property, t_objectproperties.ObjectID, t_objectproperties.PropertyID,
RN = ROW_NUMBER() OVER(PARTITION BY t_objectproperties.ObjectID, t_objectproperties.Property ORDER BY t_objectproperties.PropertyID ASC)
FROM t_objectproperties
)
删除 FROM CTE WHERE RN > 1
谢谢。
英文:
Well that was way easier than expected. Thanks a lot Thom for your comment. I had to partition by ObjectID and Property. The following code worked as a charm.
WITH CTE AS(
SELECT t_objectproperties.Property, t_objectproperties.ObjectID, t_objectproperties.PropertyID,
RN = ROW_NUMBER()OVER(PARTITION BY t_objectproperties.ObjectID, t_objectproperties.Property ORDER BY t_objectproperties.PropertyID ASC)
FROM t_objectproperties
)
Delete FROM CTE WHERE RN > 1
Thanks.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论