Programming thoughts-troublesome paging, to remove duplicate problems

Programming thoughts-troublesome paging, to remove duplicate problems

Recently, I encountered a troublesome problem. When developing the audit function, you need to query the audited records. It must be a single corresponding to multiple audit records, so multiple audit records are displayed. Suddenly the product needs to remove the excess. A single sheet only displays the most recent audit records. I removed the excess in a loop at the beginning. After removing it, I found an embarrassing problem, that is, the front-end pagination display: 10 items per page, total number, and total number of pages...

Assuming there are 6 records in total and 10 records per page, then there is only one page, which should be 6 records. When there is a single multiple audit records, there may be only 4 records after removing the redundant ones, and then the front end also displays There are 6 in total, but only 4 are seen, which is embarrassing. I also thought of subtracting the total number of records at the beginning, but after subtracting the total number of records, the subsequent paging calculations will be affected, resulting in wrong results. So I thought of modifying sql. At the beginning my sql was:

  <!--Statistical total-->
    select count(t.ID)
        from Assets_Transfer_Audit a
        inner join Assets_Transfer_Apply t on t.FK_CODE = a.ID
        where t.SCHOOL_FK_CODE = #{schoolFkCode}
        and t.DEL_STATUS = 0
        and a.AUDITOR_FK_CODE = #{auditorFkCode}
        and a.LINK_STATUS != 0
        
    <!--Paging to obtain audit records-->      
        select
          t.ID,
          a.HANDLE_STATUS,
          t.DOCUMENT_NUMBER
        from Assets_Transfer_Apply t
        inner join Assets_Transfer_Audit a on t.FK_CODE = a.ID
        where t.SCHOOL_FK_CODE = #{schoolFkCode}
          and t.DEL_STATUS = 0
          and a.AUDITOR_FK_CODE = #{auditorFkCode}
          and a.LINK_STATUS != 0
         limit 0, 10
			

After that, I first thought of de-duplicating the total, and then querying the actual record to de-duplicate, so the count(t.ID) of my statistical sql was changed to count(DISTINCT t.ID), which successfully reduced the total; However, it is difficult to do paging and de-duplication, because DISTINCT can only be written at the top, and all the following columns are the same before de-duplication, so it is impossible to merge the paging audit records into one, so I had a whim. First query the real records, build a temporary table, and then check the temporary table and easy ID grouping, so that the duplicate is successfully removed, the sql is as follows:

 <!--Statistical total-->
    select count(DISTINCT t.ID)
        from Assets_Transfer_Audit a
        inner join Assets_Transfer_Apply t on t.FK_CODE = a.ID
        where t.SCHOOL_FK_CODE = #{schoolFkCode}
        and t.DEL_STATUS = 0
        and a.AUDITOR_FK_CODE = #{auditorFkCode}
        and a.LINK_STATUS != 0
        
    <!--Paging to obtain audit records-->      
    select * from(
      select
        t.ID,
        a.HANDLE_STATUS,
        a.LINK_STATUS
        from Assets_Transfer_Apply t
            inner join Assets_Transfer_Audit a on t.FK_CODE = a.ID
            where t.SCHOOL_FK_CODE = #{schoolFkCode}
            and a.AUDITOR_FK_CODE = #{auditorFkCode}      
            ORDER BY a.LINK_STATUS DESC
       ) temp GROUP BY temp.ID DESC limit 0, 10

This solved the problem perfectly, but later I considered that this method is too cumbersome and not conducive to maintenance and expansion, so I still insisted on changing the table structure with my original intention. My original table structure is as follows:

It can be seen from the above that the design of the table is not perfect, because the status of the document becomes 6 rejected after the review is rejected, but it is not clear who rejected it, so I cannot directly check the receipt table when I paginate the page, but need It is cascaded to the audit table, so it is difficult to remove duplicate audit records. But if you add a column of dedicated users to the receipt table to store the current level of audit results, so whether it is approved or rejected, you can know that the person rejected it, and the initial problem above will not exist, so I think I modify the table structure That is the way to go.