Oracle -如何在不使用组的情况下过滤重复行

Question

我有以下查询：

select
   C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, C.MODIFIEDDATE, V.NAME
from TPM_PROJECTCHANGES C
inner join TPM_PROJECTVERSION V ON C.PROJECTID = V.PROJECTID AND C.VERSIONID = V.VERSIONID
where C.MODIFIEDDATE BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY')

我想在特定日期之间显示项目中的所有更改。这个很好用。但是，我有很多重复的属性(比如有人在同一个项目中更改了50次相同的属性)。我想过滤掉这些内容，并且只显示在项目中更改某个属性的最近一次。我可以这样做：

select
   C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, MAX(C.MODIFIEDDATE)
from TPM_PROJECTCHANGES C
having MAX(C.MODIFIEDDATE) BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY')
group by C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID

然而，现在我不能JOIN任何东西。例如，如果我尝试：

select
   C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, MAX(C.MODIFIEDDATE), V.NAME
from TPM_PROJECTCHANGES C
inner join TPM_PROJECTVERSION V ON C.PROJECTID = V.PROJECTID AND C.VERSIONID = V.VERSIONID
having MAX(C.MODIFIEDDATE) BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY')
group by C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID

我会得到错误：

ORA-00979:不是按表达式分组

是否有更好的方法筛选出对同一项目的重复更改？

Answer 1

您还可以使用分析函数：

select PROJECTID, VERSIONID, MODIFIEDATTRIBUTEID, MODIFIEDDATE, Name
from (select C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, C.MODIFIEDDATE, V.NAME,
             row_number() over (partition by C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, V.NAME
                                order by C.MODIFIEDDATE desc) as seqnum
      from TPM_PROJECTCHANGES C inner join
           TPM_PROJECTVERSION V ON C.PROJECTID = V.PROJECTID AND C.VERSIONID = V.VERSIONID
      where C.MODIFIEDDATE BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and
                                   TO_DATE('07/25/12', 'MM/DD/YY')
     ) t
where seqnum = 1

Answer 2

HAVING应该是查询的最后一行：

select
   C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, MAX(C.MODIFIEDDATE), V.NAME
from TPM_PROJECTCHANGES C
inner join TPM_PROJECTVERSION V 
ON C.PROJECTID = V.PROJECTID AND C.VERSIONID = V.VERSIONID
group by C.PROJECTID, C.VERSIONID, C.MODIFIEDATTRIBUTEID, V.NAME
having MAX(C.MODIFIEDDATE) BETWEEN TO_DATE('07/18/12', 'MM/DD/YY') and TO_DATE('07/25/12', 'MM/DD/YY')

Answer 3

SQL查询组合的基本规则如下。将工作的SQl查询封装在括号中，并将其内联为视图：

select
   V.NAME, IC.* 
from (/*working query against TPM_PROJECTCHANGES here*/ ) IC
inner join TPM_PROJECTVERSION V ...

关于上一个查询的语法，预计逐组列表将容纳比select子句更多的列，而不是更少(当然不包括聚合)。