Compare different orders of the same table

I have this following scenario, a table with these columns:

table_id|user_id|os_number|inclusion_date

In the system, the os_number is sequential for the users, but due to a system bug some users inserted OSs in wrong order. Something like this:

table_id | user_id | os_number | inclusion_date
-----------------------------------------------
1        | 1       | 1         | 2015-11-01
2        | 1       | 2         | 2015-11-02
3        | 1       | 3         | 2015-11-01

Note the os number 3 inserted before the os number 2

What I need:

Recover the table_id of the rows 2 and 3, which is out of order.

I have these two select that show me the table_id in two different orders:

select table_id from table order by user_id, os_number

select table_id from table order by user_id, inclusion_date

I can't figure out how can I compare these two selects and see which users are affected by this system bug.

---

Your question is a bit difficult because there is no correct ordering (as presented) -- because dates can have ties. So, use the rank() or dense_rank() function to compare the two values and return the ones that are not in the correct order:

select t.*
from (select t.*,
             rank() over (partition by user_id order by inclusion_date) as seqnum_d,
             rank() over (partition by user_id order by os_number) as seqnum_o
      from t
     ) t
where seqnum_d <> seqnum_o;

---

对两个订单使用row_number() ：

select *
from (
    select *, 
        row_number() over (order by os_number) rnn,
        row_number() over (order by inclusion_date) rnd
    from a_table
    ) s
where rnn <> rnd;

 table_id | user_id | os_number | inclusion_date | rnn | rnd 
----------+---------+-----------+----------------+-----+-----
        3 |       1 |         3 | 2015-11-01     |   3 |   2
        2 |       1 |         2 | 2015-11-02     |   2 |   3
(2 rows)

---

Not entirely sure about the performance on this but you could use a cross apply on the same table to get the results in one query. This will bring up the pairs of table_ids which are incorrect.

select 
    a.table_id as InsertedAfterTableId,
    c.table_id as InsertedBeforeTableId
from table a
cross apply 
(
    select b.table_id
    from table b
    where b.inclusion_date < a.inclusion_date and b.os_number > a.os_number
) c

链接地址: http://www.djcxy.com/p/89096.html

上一篇: 码头拉查找index.docker.io：没有这样的主机

下一篇: 比较同一张表的不同顺序