对于 Oracle SQL，跨多个表和列获取不同的值

For Oracle SQL, getting a distinct value across multiple tables and columns

提问by Yas

I have the following sample query,

我有以下示例查询，

select o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE, l.COMPANY_ID 
from TABLE1 o, TABLE2 l 
where o.ENTRY_ID = l.ENTRY_ID 
and COMPANY_ID in (10, 11, 12, 13)

that would return a set of data similar to the following:

这将返回一组类似于以下内容的数据：

ENTRY_ID, DESCRIPTION, ENTRY_DATE, COMPANY_ID
1, Description 1, 2/12/2008, 10
2, Description 2, 2/12/2008, 10
3, Description 3, 2/10/2008, 10
4, Description 4, 2/11/2008, 10
4, Description 4, 2/11/2008, 11
4, Description 4, 2/11/2008, 12
4, Description 4, 2/11/2008, 13

If an entry is associated with multiple companies, this query will return the same entry 1 time for each company it is associated with.

如果一个条目与多个公司相关联，则此查询将为与其关联的每个公司返回相同的条目 1 次。

I would like the query to be distinct by COMPANY_ID, so that if an entry is associated with multiple companies, it is only returned once.

我希望通过 COMPANY_ID 来区分查询，以便如果一个条目与多个公司相关联，则只返回一次。

Here is the result set I want returned:

这是我想要返回的结果集：

ENTRY_ID, DESCRIPTION, ENTRY_DATE, COMPANY_ID
    1, Description 1, 2/12/2008, 10
    2, Description 2, 2/12/2008, 10
    3, Description 3, 2/10/2008, 10
    4, Description 4, 2/11/2008, 10

but, the following would be acceptable as well,

但是，以下内容也是可以接受的，

ENTRY_ID, DESCRIPTION, ENTRY_DATE, COMPANY_ID
    1, Description 1, 2/12/2008, 10
    2, Description 2, 2/12/2008, 10
    3, Description 3, 2/10/2008, 10
    4, Description 4, 2/11/2008, 11

I want distinct ENTRY_ID. In the result set I am getting now, I get four entries with ENTRY_ID because four different companies share that entry. I want only one entry returned when the entry is shared between multiple companies.

我想要不同的 ENTRY_ID。在我现在得到的结果集中，我得到四个带有 ENTRY_ID 的条目，因为四个不同的公司共享该条目。当条目在多个公司之间共享时，我只希望返回一个条目。

回答by Quassnoi

SELECT ENTRY_ID, DESCRIPTION, ENTRY_DATE, COMPANY_ID
FROM (
  SELECT o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE, l.COMPANY_ID,
         ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY entry_date DESC, entry_id DESC) AS rn
  FROM TABLE1 o, TABLE2 l 
  WHERE o.ENTRY_ID = l.ENTRY_ID 
    AND COMPANY_ID in (10, 11, 12, 13)
  )
WHERE rn = 1

This will return entry with the last ENTRY_DATEor a greater ENTRY_IDbe there more than one entry equal to the last ENTRY_DATE

如果有多个条目等于最后一个条目，这将返回最后一个ENTRY_DATE或更大的ENTRY_ID条目ENTRY_DATE

回答by WW.

If you are happy to have the largest COMPANY_IDreturned:

如果您很高兴获得最大的COMPANY_ID回报：

select o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE, MAX( l.COMPANY_ID ) AS COMPANY_ID
from TABLE1 o, TABLE2 l 
where o.ENTRY_ID = l.ENTRY_ID 
and COMPANY_ID in (10, 11, 12, 13)
group by o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE;

回答by Yas

I would like the query to be distinct by COMPANY_ID, so that if an entry is associated with multiple companies, it is only returned once.

我希望通过 COMPANY_ID 来区分查询，以便如果一个条目与多个公司相关联，则只返回一次。

The question is not clear, do you want distinct entries or distinct companies. Maybe you can also show the output you want so we can understand better.

问题不清楚，你想要不同的条目还是不同的公司。也许您还可以显示您想要的输出，以便我们更好地理解。

If you want distinct entries and the company_id to list with it does not matter here is the query:

如果您想要不同的条目并且 company_id 列出它并不重要，这里是查询：

select * from (
select o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE, l.COMPANY_ID, row_number() over (partition by o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE order by l.COMPANY_ID) rn
from TABLE1 o, TABLE2 l 
where o.ENTRY_ID = l.ENTRY_ID 
and COMPANY_ID in (10, 11, 12, 13)
)
where rn=1;