选择一条记录，即使该记录的值较低 [英] Select a record just if the one before it has a lower value

问题描述

我使用mysql，但从未遇到过如此巨大的挑战. 希望您能提供帮助.

I work with mysql and I've never encountered such a great challenge. I hope you can help..

我有1个表，称为报告":

I have 1 table called Reports:

ID         SerialNumber         Remain_Toner_Black   
7099       Z5UEBJAC900002Y      37   
7281       Z5UEBJAC900002Y      36  
7331       Z5UEBJAC900002Y      100  
7627       8Z37B1DQ100105N      58  
7660       8Z37B1DQ100105N      57  
5996       CND8DDM2FH           83 
5971       CND8DDM2FH           83 
7062       3960125290           0 
7088       3960125290           93 
7100       3960125290           100 

现在，我希望能够从表中选择Remain_Toner_Black高于表中具有相同SerialNumber的上一行的Remain_Toner_Black的记录(以前，我的意思是具有相同SerialNumber的较低ID).

Now I want to be able to select records from the table where Remain_Toner_Black is higher than Remain_Toner_Black of the previous row in the table with the same SerialNumber (by previous I mean lower ID with the same SerialNumber).

对于以上记录，我希望得到以下结果:

For the above records I want the results below:

ID         SerialNumber        Remain_Toner_Black_Before    Remain_Toner_Black_After
7331       Z5UEBJAC900002Y     36                           100
7088       3960125290          0                            93
7100       3960125290          93                           100

推荐答案

SELECT  a.ID, a.SerialNumber, 
        b.Remain_Toner_Black BeforeCount,
        a.Remain_Toner_Black AfterCount
FROM    
        (
            SELECT  A.ID, 
                    A.SerialNumber, 
                    A.Remain_Toner_Black,
                    (
                        SELECT  COUNT(*)
                        FROM    tableName c
                        WHERE   c.SerialNumber = a.SerialNumber AND
                                c.ID <= a.ID) AS RowNumber
            FROM    TableName a
        ) a
        INNER JOIN
        (
            SELECT  A.ID, 
                    A.SerialNumber, 
                    A.Remain_Toner_Black,
                    (
                        SELECT  COUNT(*)
                        FROM    tableName c
                        WHERE   c.SerialNumber = a.SerialNumber AND
                                c.ID <= a.ID) AS RowNumber
            FROM    TableName a
        ) b ON a.SerialNumber = b.SerialNumber AND
                a.RowNumber = b.RowNumber + 1
WHERE   b.Remain_Toner_Black < a.Remain_Toner_Black

• SQLFiddle演示
• SQLFiddle Demo

输出

╔══════╦═════════════════╦═════════════╦════════════╗
║  ID  ║  SERIALNUMBER   ║ BEFORECOUNT ║ AFTERCOUNT ║
╠══════╬═════════════════╬═════════════╬════════════╣
║ 7331 ║ Z5UEBJAC900002Y ║          36 ║        100 ║
║ 7088 ║ 3960125290      ║           0 ║         93 ║
║ 7100 ║ 3960125290      ║          93 ║        100 ║
╚══════╩═════════════════╩═════════════╩════════════╝

BRIEF解释

上面的查询所做的是，它为ID按升序排列的每个SerialNumber生成一个序列号，该序列号模仿其他RDBS上的ROW_NUMBER().

What the query above does is it generates a sequential number which mimics ROW_NUMBER() on other RDBS for every SerialNumber ordered by ID in ascending order.

然后通过SerialNumber将两个子查询合并，并生成顺序号.在生成的数字上，第一个子查询上的值必须等于第二个子查询上的值的一个，再加上下一个就可以得到下一个墨粉的数量.

The two subquery is then joined via the SerialNumber and sequential number generated. On the generated number, the value on the first subquery must be equal to plus one of the value on the second subquery to get the number of toner on the next reord.

这篇关于选择一条记录，即使该记录的值较低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！