当列值重复时返回唯一值的查询 [英] Query to return unique values when one of column value is repeating

问题描述

我有一个sql查询，如下所示

I have a sql query like the following

SELECT Name.Nameguid,Name.Name,relation.ancestorguid,relation.productguid,relation.pathLength
     FROM Name Name 
     JOIN ProductRelationship relation 
       ON Name.productGuid = relation.ancestorGuid 
      AND relation.productGuid = '6075D04A-E74A-464B-94E7-25374F0B9833' 
     ORDER BY relation.pathLength DESC

哪个返回以下

NameGuid                                Name                AncestorGuid                            ProductGuid                             PathLength
6D39CB04-88D9-4125-A052-8DF608AAD29C    NameParentOnly  8E07F824-763C-434F-926C-80FCC8690243    6075D04A-E74A-464B-94E7-25374F0B9833    2
186E1DF3-4D1A-4020-B845-1280CF1092EA    NameParentChild 8E07F824-763C-434F-926C-80FCC8690243    6075D04A-E74A-464B-94E7-25374F0B9833    2
CA60E542-4047-4B4D-AA22-0744A1E5F2E0    childlevelName  D9833FCA-93A7-42F5-AFC3-5544F7A4425D    6075D04A-E74A-464B-94E7-25374F0B9833    1
A09D01FC-D69D-4AFA-B4D0-C804F030A281    NameParentChild D9833FCA-93A7-42F5-AFC3-5544F7A4425D    6075D04A-E74A-464B-94E7-25374F0B9833    1
63148C89-59FD-4C96-883F-60E8446A6BC4    NameParentChild 6075D04A-E74A-464B-94E7-25374F0B9833    6075D04A-E74A-464B-94E7-25374F0B9833    0

我的情况是这样的，当有名称重复时，我希望查询只返回最少的行路径长度的值。

My scenario is such that when ever name gets repeated , I want the query to return only the row with least value for Path Length .

在上面的例子中，只有 NameParentChild 重复三次，我希望它返回最小路径长度在这种情况下为零）

In the above example only NameParentChild gets repeated three times i want it to return the one with least path length(In this case Zero)

以下是我期望的结果

NameGuid                                Name                AncestorGuid                            ProductGuid                             PathLength
6D39CB04-88D9-4125-A052-8DF608AAD29C    NameParentOnly  8E07F824-763C-434F-926C-80FCC8690243    6075D04A-E74A-464B-94E7-25374F0B9833    2
CA60E542-4047-4B4D-AA22-0744A1E5F2E0    childlevelName  D9833FCA-93A7-42F5-AFC3-5544F7A4425D    6075D04A-E74A-464B-94E7-25374F0B9833    1
63148C89-59FD-4C96-883F-60E8446A6BC4    NameParentChild 6075D04A-E74A-464B-94E7-25374F0B9833    6075D04A-E74A-464B-94E7-25374F0B9833    0

我如何调整查询来做到这一点？我希望查询也可以在Oracle，DB2和SQL服务器上工作。

How can i tweak the query to do this ? I want the query to work on Oracle , DB2 and SQL server as well.

谢谢

推荐答案

您应该可以通过分析（或窗口）功能。分析功能是否在RDBMS中可用，以及您必须使用哪种语法是另一回事。在Oracle中，假设NameGuid对于您的原始查询是唯一的，您可以执行以下操作：

You should be able to do this with analytic (or window) functions. Whether analytic functions are available in your RDBMS or not, and which syntax you have to use is another matter. In Oracle, assumming that NameGuid is unique for your original query, you could do:

select NameGuid, Name, AncestorGuid, ProductGuid, PathLength
from (
    select 
    NameGuid, 
    Name, 
    AncestorGuid, 
    ProductGuid, 
    PathLength, 
    -- take every row from original query with the same Name as this,
    -- order those rows by PathLength (and NameGuid to disambiguate)
    -- and return the NameGuid of the first row in that "partition"
    first_value(NameGuid) over (partition by Name order by PathLength asc, NameGuid asc) MinNameGuid
    from ( 
        ... your original query ...
    )
)
where 
-- return rows whose NameGuid is the same as the NameGuid calculated by first_value(...)
NameGuid = MinNameGuid

这篇关于当列值重复时返回唯一值的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！