ORACLE SQL:只显示特定的值，如果出现不止一次。

I have a table that has two columns...

我有一个有两列的表格。

LN    |   SRCREF
LN-1  |  LN/123456
LN-1  |  LN/789012
LN-2  |  LN/123456
LN-2  |  LN/098765
LN-3  |  LN/123456
LN-3  |  LN/789012
LN-3  |  LN/432109

And I want to return something like this...

我想要返回像这样的东西…

LN    |    SRCREF
LN-1  |  LN/123456
LN-1  |  LN/789012
LN-2  |  LN/098765
LN-3  |  LN/432109

So in other words I only want to return the SRCREF once but in a priority order so I see all those that have LN-1s then those that don't have LN-1s but LN-2s then finally those that just have LN-3s.

换句话说，我只需要返回SRCREF，但在优先顺序中，所以我看到所有那些有ln -1的，然后那些没有ln -1，但有LN-2s的，最后那些只有ln -3的。

I hope that makes sense, thanks.

我希望这能说得通，谢谢。

1 个解决方案

#1

You can group by SRCREF. This means that you only have each value SRCREF once in your results.

你可以用SRCREF来分组。这意味着在结果中只有每个值SRCREF。

Then, you can use an aggregate function to get one specific value from LN to go with that SRCREF value. In this case, I'm using min which returns the lowest value. So you get the lowest LN value that goes with each SRCREF value.

然后，可以使用聚合函数从LN中获得一个特定值，以与SRCREF值一起执行。在这种情况下，我使用最小值返回最小值。所以你得到的是每个SRCREF值的最低的LN值。

Finally, I'm using order by to sort by that lowest value.

最后，我用排序的最小值排序。

select
  min(LN) as first,
  SRCREF 
from
  YourTable t
group by
  SRCREF 
order by
  min(LN)

Note that in this case you might get odd results, because the first column is a character field. That means that its contents are compared alphabetically. This means that L-10 will come before L-2. There are other ways to solve that, but I don't know if it's going to be an issue for you.

注意，在这种情况下，您可能会得到奇怪的结果，因为第一列是一个字符字段。这意味着它的内容是按字母顺序比较的。这意味着L-10在L-2之前。还有其他方法可以解决这个问题，但我不知道这是否会成为你的问题。

#1