选择一条记录,如果前一条记录具有按月过滤的较低值 [英] 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屋!