ORACLE Select Distinct返回许多列，其中 [英] ORACLE Select Distinct return many columns and where

问题描述

我有一个看起来像这样的表:

I have a table which looks like this:

NAME    Col1     Col2    Col3
Tim     1        2       3
Tim     1        1       2
Tim     2        1       2
Dan     1        2       3
Dan     2        2       1
Dan     2        1       3

我正在尝试执行SELECT命令，结果是这样:

Im trying to make a SELECT command which results in this:

NAME    Col1     Col2    Col3
Tim     2        1       2
Dan     2        2       1

所以我要使用不同的名称，但要选择所有四列，其中Col1 = 2. 我在这里使用解决方案来使独特的部分起作用: SQL/mysql-选择不重复/唯一，但返回所有列?

So I want distinct Names but select all four columns and where Col1 = 2. I used the solution here to get the distinct part working: SQL/mysql - Select distinct/UNIQUE but return all columns?

但是，当我在select语句的WHERE部分添加add Col1 = '2'时，并不会返回Col1为2的所有名称，因为我认为它首先查找的是一个不同的数字，而不是1，所以需要该结果.

But when I add add Col1 = '2' to the WHERE part of the select statement is does not return all names where Col1 is 2 because i think it looks at a different one first which is not 1 so takes that result.

希望我有道理，有人可以提供帮助.很难解释，并想出一个好标题.谢谢！

Hope I made sense and someone can help. Was hard to explain and come up with a good title. Thanks!

推荐答案

据我了解，该查询应该可以解决您的问题:

As I understand this query should solve your problem:

select distinct a.* from temp_ids a join

(select name, max(col1 || ' ' || col2  || ' ' || col3) id
from temp_ids
where col1 = 2
group by name
) b

on (a.name = b.name and (col1 || ' ' || col2  || ' ' || col3) = b.id)
;

当然，最好使用唯一记录ID代替串联，但是可以生成ID.确保id的函数为每个列组合返回唯一值(在这种情况下使用(col1 ||'''|| col2 ||''|| col3))

Of cause, better to use unique record id instead concatenation, but generated id is possible. Be sure that function for id returns unique value for each combination of columns (in this case used (col1 || ' ' || col2 || ' ' || col3) )

这篇关于ORACLE Select Distinct返回许多列，其中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！