我已经问过有关此主题的两个问题,并且两个问题都得到了很好的答案。
(问题2当前与主题无关,因为我不需要按月过滤)
自从我实现了向我建议的代码以来,只要我的表一直在增长,sql查询所花费的时间就会越来越长。首先,要花1000秒执行8秒。现在在具有超过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
但是同样,现在当我在表中有25,000行的Reports
表上运行它时,它会失败。
这是您的问题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
编辑:
说明:
用这条线
, (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分配一个值,为什么要按序列号排序?顺序非常重要。没有顺序,就不能保证返回行的顺序。同样,我们将使用变量访问前一行的值,因此将相同的序列号“分组”非常重要。
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的上一行的一个变量,而是将四个用于黑色,青色,品红色和黄色。就像您一样,只需加入“打印机和客户”表即可。不要忘记订购的顺序,您就完成了。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句