我已经问了 2 个关于这个主题的问题，并且在这两个问题上都得到了很好的答案。

1. Select a record just if the one before it has a lower value

2. Select a record just if the one before it has a lower value filtered by month

(问题2目前与主题不相关，因为我不需要按月过滤)

自从我实现了向我建议的代码后，只要我的表不断增长 sql 查询 花的时间越来越长。一开始，它需要 8 秒才能在 1000 行上执行。 现在在一个超过 25,000 行的表上，它就失败了。

您可以在这里观看我的查询 - http://sqlfiddle.com/#!2/5c480/1/0

SELECT  a.ID, DATE_FORMAT(a.Time,'%d/%m/%y') AS T, a.SerialNumber, p.Model,
    b.Remain_Toner_Black BeforeCountBlack,
    a.Remain_Toner_Black AfterCountBlack,
    b.Remain_Toner_Cyan BeforeCountCyan,
    a.Remain_Toner_Cyan AfterCountCyan,
b.Remain_Toner_Magenta BeforeCountMagenta,
    a.Remain_Toner_Magenta AfterCountMagenta,
b.Remain_Toner_Yellow BeforeCountYellow,
    a.Remain_Toner_Yellow AfterCountYellow
FROM    
    (
        SELECT  a.ID, 
                a.Time, 
                a.SerialNumber, 
                a.Remain_Toner_Black,
                a.Remain_Toner_Cyan,
                a.Remain_Toner_Magenta,
                a.Remain_Toner_Yellow,
                (
                    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,
                a.Remain_Toner_Cyan,
                a.Remain_Toner_Magenta,
                a.Remain_Toner_Yellow,
                (
                    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
INNER JOIN Printers p ON a.SerialNumber = p.SerialNumber
INNER JOIN Customers c ON p.IP = c.IP AND c.Company = 5
WHERE   (b.Remain_Toner_Black < a.Remain_Toner_Black AND b.Remain_Toner_Black >= 0) OR (b.Remain_Toner_Cyan < a.Remain_Toner_Cyan AND b.Remain_Toner_Cyan >= 0) OR (b.Remain_Toner_Magenta < a.Remain_Toner_Magenta AND b.Remain_Toner_Magenta >= 0) OR (b.Remain_Toner_Yellow < a.Remain_Toner_Yellow AND b.Remain_Toner_Yellow >= 0)

我需要使用以下 3 个表，以便仅选择属于具有 ID 的特定公司的打印机。

报告:

ID         SerialNumber         Remain_Toner_Black   
29881      Z30PBAHBB00034E      58   
30001      Z30PBAHBB00034E      98
30200      Z30PBAHBB00034E      70
30205      BVCfdgdfgdf329F      50
30207      BVCfdgdfgdf329F      40
30210      Z30PBAHBB00034E      50
30301      Z30PBAHBB00034E      100

打印机:

IP                 SerialNumber         Customer 
80.179.228.81      Z30PBAHBB00034E      52

客户:

ID         IP                      Company        
52         80.179.228.81           5

我的查询完美运行并返回:

ID         SerialNumber            BEFORECOUNTBLACK         AFTERCOUNTBLACK        
30001      Z30PBAHBB00034E         58                       98
30301      Z30PBAHBB00034E         50                       100

但是，当我在 Reports 表中有 25,000 行的表上运行它时，它再次失败。

最佳答案

这是您的问题 1 的解决方案，它将运行得更快，因为您有许多全表扫描和相关子查询。在这里，您最多只有一个表扫描(也可能是一个临时表，具体取决于您的数据量和内存量)。我认为您可以在此处轻松地根据您的问题进行调整。问题 2(我还没有真正读过)可能也得到了回答，因为现在只需添加 where date_column = whatever

select * from (
    select
    t.*,
    if(@prev_toner < Remain_Toner_Black and @prev_sn = SerialNumber, 1, 0) as select_it,
    @prev_sn := SerialNumber,
    @prev_toner := Remain_Toner_Black
    from
    Table1 t
    , (select @prev_toner:=0, @prev_sn:=SerialNumber from Table1 order by SerialNumber limit 1) var_init
    order by SerialNumber, id
) sq  
where select_it = 1

• 看到它在 sqlfiddle 中实时运行

编辑:

解释:

用这条线

    , (select @prev_toner:=0, @prev_sn:=SerialNumber from Table1 order by SerialNumber 

我们只是即时初始化变量@prev_toner 和@prev_sn。这与查询中根本没有这一行而是写在查询前面是一样的

SET @prev_toner = 0;
SET @prev_sn = (select serialnumber from your_table order by serialnumber limit 1);
SELECT ...

那么，为什么查询要给@prev_sn 赋值，为什么要按序列号排序？顺序非常重要。没有 order by 就无法保证返回行的顺序。此外，我们将使用变量访问前一行的值，因此将相同的序列号“组合在一起”很重要。

select 子句中的列是一个接一个地计算的，所以首先选择这一行很重要

if(@prev_toner < Remain_Toner_Black and @prev_sn = SerialNumber, 1, 0) as select_it,

在你选择这两行之前

@prev_sn := SerialNumber,
@prev_toner := Remain_Toner_Black

这是为什么呢？最后两行仅将当前行的值分配给变量。因此在这一行

if(@prev_toner < Remain_Toner_Black and @prev_sn = SerialNumber, 1, 0) as select_it,

变量仍然保留前几行的值。而我们在这里所做的无非是说“如果 Remain_Toner_Black 列中的前几行值小于当前行中的值并且前几行序号与实际行序号相同, 返回 1，否则返回 0。”

然后我们可以在外部查询中简单地说“选择上面返回 1 的每一行”。

鉴于您的查询，您不需要所有这些子查询。它们非常昂贵且不必要。实际上这很疯狂。在这部分查询中

    SELECT  a.ID, 
            a.Time, 
            a.SerialNumber, 
            a.Remain_Toner_Black,
            a.Remain_Toner_Cyan,
            a.Remain_Toner_Magenta,
            a.Remain_Toner_Yellow,
            (
                SELECT  COUNT(*)
                FROM    Reports c
                WHERE   c.SerialNumber = a.SerialNumber AND
                        c.ID <= a.ID) AS RowNumber
    FROM    Reports a

您选择整个表格并对每一行计算该组中的行数。那是一个依赖子查询。所有只是为了有某种行号。然后你第二次这样做，这样你就可以连接这两个临时表来获取前一行。真的，怪不得性能这么差。

那么，如何根据您的查询调整我的解决方案？我没有使用一个变量来获取 Remain_Toner_Black 的前一行，而是使用四个变量来表示黑色、青色、品红色和黄色。像您一样加入 Printers 和 Customers 表。不要忘记 order by 就大功告成了。

关于mysql - 如果之前的记录具有较低的值，则选择一条记录花费的时间太长而失败，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/24035933/