如何从唯一性由 2 列定义的 SQL 中获取唯一的一组行? [英] How to get unique set of rows from SQL where uniqueness is defined by 2 columns?

问题描述

如果我有一个表格如下:

If I have a table as follows:

身份证 |姓名 |分类 |家长 |网址 |上次修改

ID | Name | Category | Parent | URL | LastModified

如果两行具有相同的名称和父级，则它们不是唯一的.在这种情况下，我如何获得唯一的一组行(但返回的不仅仅是使它们唯一的列)?

If two rows have the same Name and parent, then they are not unique. How do I get the unique set of rows in this case (but return more than the columns that make them unique)?

所以，欲知更多详情:这是一个企业关键词表，其中关键词是按类别组织的.每个关键字只能有一个类别.每个关键字都可以有子关键字，因此如果 parent=0 或 NULL，则它是根关键字.如果关键字在类别中具有相同的名称和父项，则它们不是唯一的(无论其他列如何).如果两个关键字具有相同的名称和类别，并且 parent=0 或 NULL 则它们不是唯一的.如果有重复，那么我只想要第一个.原因是我将这些放入一个系统中，该系统不允许一个关键字有两个同名的孩子.

So, for more details: This is a corporate keywords table, where keywords are organized by category. Each keyword can only have one category. Each keyword can have child keywords, so if parent=0 or NULL, it is a root keyword. If keywords have the same name and parent in a category, then they are not unique (regardless of the other columns). If two keywords have the same name and category, and parent=0 or NULL then they are not unique. If there are duplicates, then I only want the 1st one. The reason is I am putting these into a system that will not allow a keyword to have two children with the same name.

我还想看看哪些行是重复的，看看是什么给我带来了麻烦！

I would also like to see what rows ARE duplicates to see what is causing me the trouble!

到目前为止，感谢一百万个出色的回复.我显然不是 SQL 人... :(

Thanks a million so far for excellent responses. I am obviously not a SQL guy... :(

推荐答案

此查询查找没有其他行具有相同名称和父行的所有行.如果两行的 parent 设置为 NULL，则这些行不被视为具有相同的 parent.

This query finds all rows where no other row has the same name and parent. If two rows have parent set to NULL, these rows are not considered to have the same parent.

SELECT T1.*
FROM Table1 T1
LEFT JOIN Table1 T2
ON T1.ID != T2.ID AND T1.Name = T2.Name AND T1.Parent = T2.Parent
WHERE T2.ID IS NULL

这篇关于如何从唯一性由 2 列定义的 SQL 中获取唯一的一组行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！