在一列中选择具有相同值但在第二列中具有不同值的行

I am using PL-SQL. I do have a table named objectlist. In this table, I do have serial numbers,named as changeno, and each has production dates.

我正在使用PL-SQL。我有一个名为objectlist的表。在此表中,我有序列号,名为changeno,每个都有生产日期。

I have seen that some of the serial numbers have different production dates. I want to list the serial numbers that have different production dates.

我看到一些序列号有不同的生产日期。我想列出具有不同生产日期的序列号。

Lets say that the serial 1234 has two production dates in the table. So, my sql should show like

假设序列1234在表中有两个生产日期。所以,我的sql应该显示为

1234   12.01.2015   
1234   01.12.2015

I do not which serials have more than one production date. Kindly show me how I can find this.

我不知道哪个系列有多个生产日期。请告诉我如何找到这个。

1 个解决方案

#1

One of possibilities is using analytical function count() here:

其中一种可能性是使用分析函数count():

select changeno, pdate 
  from (
    select changeno, pdate, count(1) over (partition by changeno) cnt 
      from objectlist )
  where cnt > 1 order by changeno, pdate

SQLFiddle demo

If you need this in PLSQL than put this query in procedure, function or block:

如果你在PLSQL中需要这个,那么把这个查询放在过程,函数或块中:

begin 
  for c in (
    select changeno, pdate 
      from (
        select changeno, pdate, count(1) over (partition by changeno) cnt 
          from objectlist )
      where cnt > 1 order by changeno, pdate )
  loop
    dbms_output.put_line(lpad(c.changeno, 10)||' '||c.pdate);
  end loop;
end;

#1