如何比较同一表上最后插入的两个指定行的列值?

I have a data set that is being updated on each operation maden by customers. For example, I am getting a customer's last two operations by

我有一个数据集，客户对每个操作进行更新。例如，我得到一个客户的最后两个操作

select id, 
       referance 
from (select id, 
             referance, 
             row_number() 
             over (order by time desc) as seqnum 
      from mytable where id=':id') 
al where seqnum <= 2

where id is getting from a feature file. But now I need to compare the referance values of these two operations.

从特征文件中获取id。但是现在我需要比较这两个操作的参考值。

mytable:

id  | name  | referance  |   time   |
-------------------------------------
11  |  abc  |   4589     |   09:05  |
11  |  abc  |   1234     |   09:04  |
10  |  xyz  |   0185     |   09:02  |
15  |  qpr  |   9564     |   08:54  |

so on...

等等……

Again, I can get the last two rows with id = 11; and, as far as all columns are not (null), it is returning "true" which is what I want literally. But also I'd like to compare if their referances are the same or not; and, when I call the query, it has to return "true" or "false".

同样，我可以得到id = 11的最后两行;而且，在所有列都不是(null)的情况下，它返回的是“true”，这正是我想要的。但我也想比较一下他们的参照是否相同;当我调用查询时，它必须返回“true”或“false”。

Thanks in advance

谢谢提前

P.S. I actually just need a useful function or idea. I've already try to use inner join but couldnt manage it:

我只是需要一个有用的函数或想法。我已经尝试使用内部连接，但无法管理它:

select table1.id, 
       table1.referance, 
       table2.id, 
       table2.referance 
from (select id, 
             referance, 
             row_number() 
             over (order by time desc) as seqnum 
      from mytable where id=':id') table1 
      inner join (select id, 
                         referance, 
                         row_number() 
                         over (order by time desc) as seqnum 
                  from mytable where id=':id') table2 
      on table1.referance != table2.referance 
al where seqnum <= 2 order by seqnum

2 个解决方案

#1

Aggregate your current query over the id and check if the two reference values be the same or not.

在id上聚合当前查询并检查两个引用值是否相同。

select
    id,
    case when count(distinct reference) = 1
         then 'true' else 'false' end as result
from
(
    select id, reference,
        row_number() over (order by time desc) as seqnum
    from table
    where id=':id'
) al
where seqnum <= 2
group by id;

If the distinct count of reference over the two records be 1 then it implies that they have the same value. Otherwise, we can assume that the values are different.

如果两个记录的不同引用计数为1，则意味着它们具有相同的值。否则，我们可以假设值是不同的。

#2

Why are you using row_nubmer()? You can get the last two rows as:

为什么要使用row_nubmer()?您可以将最后两行设置为:

select top 2 id, referance 
from mytable
where id=':id'
order by time desc;

You can then determine if these are the same using aggregation:

然后您可以使用聚合来确定它们是否相同:

select (case when min(reference) <> max(reference) then 'false'
             else 'true'
        end) as is_same
from (select top 2 id, referance 
      from mytable
      where id=':id'
      order by time desc
     ) t;

Note: This doesn't take NULL values for reference into account, but that is easily incorporated into the logic.

注意:这里不考虑空值作为引用，但是很容易将其合并到逻辑中。

#1