选择一条记录，如果前一条记录具有按月过滤的较低值 [英] Select a record just if the one before it has a lower value filtered by month

问题描述

关于我的问题，我有一个非常复杂的答案:

I've got a very complicated answer about my question here:

选择一条记录只要它之前的值较低

大约 3 周前.

现在我很难改变这个查询.

now I'm troubled with altering this query.

所以这是这个查询的最终版本:

so this is the final version of this query right now:

SELECT  a.ID, DATE_FORMAT(a.Time,'%d/%m/%y') AS T, a.SerialNumber, 
    b.Remain_Toner_Black BeforeCount,
    a.Remain_Toner_Black AfterCount
FROM    
    (
        SELECT  a.ID, 
                a.Time, 
                a.SerialNumber, 
                a.Remain_Toner_Black,
                (
                    SELECT  COUNT(*)
                    FROM    Reports c
                    WHERE   c.SerialNumber = a.SerialNumber AND
                            c.ID <= a.ID) AS RowNumber
        FROM    Reports a
    ) a
    LEFT JOIN
    (
        SELECT  a.ID, 
                a.Time, 
                a.SerialNumber, 
                a.Remain_Toner_Black,
                (
                    SELECT  COUNT(*)
                    FROM    Reports c
                    WHERE   c.SerialNumber = a.SerialNumber AND
                            c.ID <= a.ID) AS RowNumber
        FROM    Reports a
    ) b ON a.SerialNumber = b.SerialNumber AND
            a.RowNumber = b.RowNumber + 1
WHERE b.Remain_Toner_Black < a.Remain_Toner_Black AND b.Remain_Toner_Black >= 0

大约需要 0.0002 秒才能完成.

and it takes about 0.0002 sec to accomplish.

我想要的是编辑此查询的最后一行，使其成为:

what I want is to edit the last line of this query so it would be:

WHERE month(a.Time) = ".$i." AND b.Remain_Toner_Black < a.Remain_Toner_Black AND b.Remain_Toner_Black >= 0

但是，查询大约需要 6.9047 秒才能完成.

but then, the query takes about 6.9047 sec to accomplish.

如何添加:month(a.Time) = ".$i."以最省时的方式进行查询?

How can I add this: month(a.Time) = ".$i." to the query in the most time efficient way?

推荐答案

考虑到这一点，以下方法可能比您已经使用的更快地进行基本选择:-

Looking into this, the following way is possibly a quicker way of doing the basic select than you are already using:-

SELECT AfterSub.ID, 
    AfterSub.SerialNumber, 
    BeforeSub.Remain_Toner_Black BeforeCount,
    AfterSub.Remain_Toner_Black AfterCount
FROM
(
    SELECT ID, SerialNumber, Remain_Toner_Black, @Counter1:=@Counter1+1 AS SeqCnt
    FROM TableName
    CROSS JOIN (SELECT @Counter1:=0) Sub1
    ORDER BY SerialNumber, ID
) AfterSub
INNER JOIN
(
    SELECT ID, SerialNumber, Remain_Toner_Black, @Counter2:=@Counter2+1 AS SeqCnt
    FROM TableName
    CROSS JOIN (SELECT @Counter2:=1) Sub2
    ORDER BY SerialNumber, ID
) BeforeSub
ON BeforeSub.SerialNumber = AfterSub.SerialNumber
AND BeforeSub.SeqCnt = AfterSub.SeqCnt
WHERE   AfterSub.Remain_Toner_Black > BeforeSub.Remain_Toner_Black
ORDER BY AfterSub.SerialNumber, AfterSub.ID

这里检查月份的问题是以下项目可能在不同的月份，这依赖于计数.

The problem with checking the month here is that the following item could be in a different month, and this is relying on a count.

你可以试试:-

SELECT AfterSub.ID, 
    AfterSub.SerialNumber, 
    BeforeSub.Remain_Toner_Black BeforeCount,
    AfterSub.Remain_Toner_Black AfterCount
FROM
(
    SELECT ID, SerialNumber, Remain_Toner_Black, @Counter1:=@Counter1+1 AS SeqCnt
    FROM TableName
    CROSS JOIN (SELECT @Counter1:=0) Sub1
    ORDER BY SerialNumber, ID
) AfterSub
INNER JOIN
(
    SELECT ID, SerialNumber, Remain_Toner_Black, @Counter2:=@Counter2+1 AS SeqCnt
    FROM TableName
    CROSS JOIN (SELECT @Counter2:=1) Sub2
    ORDER BY SerialNumber, ID
) BeforeSub
ON BeforeSub.SerialNumber = AfterSub.SerialNumber
AND BeforeSub.SeqCnt = AfterSub.SeqCnt
AND AfterSub.Remain_Toner_Black > BeforeSub.Remain_Toner_Black
WHERE month(BeforeSub.Time) = ".$i." 
ORDER BY AfterSub.SerialNumber, AfterSub.ID

但这不会使用索引(但我希望的行数很少，所以我希望不是问题).

but this won't use an index (but the number of rows I would hope is low so I would hope not an issue).

你可以选择获取序列号，然后只检查当月的项目，然后再加入下个月:-

You could possibly do the select to get the sequence numbers, then only check the items for that month, before joining to the next month:-

SELECT AfterSub.ID, 
    AfterSub.SerialNumber, 
    BeforeSub.Remain_Toner_Black BeforeCount,
    AfterSub.Remain_Toner_Black AfterCount
FROM
(
    SELECT ID, SerialNumber, Remain_Toner_Black, @Counter1:=@Counter1+1 AS SeqCnt
    FROM TableName
    CROSS JOIN (SELECT @Counter1:=0) Sub1
    ORDER BY SerialNumber, ID
) AfterSub
INNER JOIN
(
    SELECT ID, SerialNumber, Remain_Toner_Black, SeqCnt
    FROM
    (
        SELECT ID, SerialNumber, Remain_Toner_Black, `Time`, @Counter2:=@Counter2+1 AS SeqCnt
        FROM TableName
        CROSS JOIN (SELECT @Counter2:=1) Sub2
        ORDER BY SerialNumber, ID
    ) BeforeSub
    WHERE month(BeforeSub.Time) = ".$i." 
) BeforeSub
ON BeforeSub.SerialNumber = AfterSub.SerialNumber
AND BeforeSub.SeqCnt = AfterSub.SeqCnt
AND AfterSub.Remain_Toner_Black > BeforeSub.Remain_Toner_Black
ORDER BY AfterSub.SerialNumber, AfterSub.ID

(注意，最后两个选择都没有被测试)

(note, neither of the last 2 selects are tested)

编辑

向 2 个子选择添加年/月检查.但是，由于正在格式化日期以进行此检查，我不确定该索引是否有用:-

Adding a check for year / month to the 2 subselects. However as the date is being formatted to do this check I am not sure the index will be useful:-

SELECT AfterSub.ID, 
    AfterSub.SerialNumber, 
    BeforeSub.Remain_Toner_Black BeforeCount,
    AfterSub.Remain_Toner_Black AfterCount
FROM
(
    SELECT ID, SerialNumber, Remain_Toner_Black, @Counter1:=@Counter1+1 AS SeqCnt
    FROM TableName
    CROSS JOIN (SELECT @Counter1:=0) Sub1
    WHERE DATE_FORMAT(`Time`,'%Y %m') >= '2013 01' 
    ORDER BY SerialNumber, ID
) AfterSub
INNER JOIN
(
    SELECT ID, SerialNumber, Remain_Toner_Black, `Time`, @Counter2:=@Counter2+1 AS SeqCnt
    FROM TableName
    CROSS JOIN (SELECT @Counter2:=1) Sub2
    WHERE DATE_FORMAT(`Time`,'%Y %m') = '2013 01' 
    ORDER BY SerialNumber, ID
) BeforeSub
ON BeforeSub.SerialNumber = AfterSub.SerialNumber
AND BeforeSub.SeqCnt = AfterSub.SeqCnt
AND AfterSub.Remain_Toner_Black > BeforeSub.Remain_Toner_Black
ORDER BY AfterSub.SerialNumber, AfterSub.ID

在子选择中使用日期(这意味着计算出该月的最后一天)可能更有效:-

Using a date in the subselects (which means working out the last day of the month) might be more efficient:-

SELECT AfterSub.ID, 
    AfterSub.SerialNumber, 
    BeforeSub.Remain_Toner_Black BeforeCount,
    AfterSub.Remain_Toner_Black AfterCount
FROM
(
    SELECT ID, SerialNumber, Remain_Toner_Black, @Counter1:=@Counter1+1 AS SeqCnt
    FROM TableName
    CROSS JOIN (SELECT @Counter1:=0) Sub1
    WHERE `Time` >= '2013-01-01' 
    ORDER BY SerialNumber, ID
) AfterSub
INNER JOIN
(
    SELECT ID, SerialNumber, Remain_Toner_Black, `Time`, @Counter2:=@Counter2+1 AS SeqCnt
    FROM TableName
    CROSS JOIN (SELECT @Counter2:=1) Sub2
    WHERE `Time` BETWEEN '2013-01-31' AND '2013-01-31'
    ORDER BY SerialNumber, ID
) BeforeSub
ON BeforeSub.SerialNumber = AfterSub.SerialNumber
AND BeforeSub.SeqCnt = AfterSub.SeqCnt
AND AfterSub.Remain_Toner_Black > BeforeSub.Remain_Toner_Black
ORDER BY AfterSub.SerialNumber, AfterSub.ID

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