在SELECT查询之前获取行 [英] Get row before SELECT query

问题描述

我有一个名为"mytable"的表.列是

I have a table called "mytable". The columns are

Time_Stamp (datetime) PK
Time_stamp_ms (int) PK
data1 (int)
data2 (int)
data3 (int)
data4 (int) 
data5 (int)
data6 (int)
cycle (int)
name (varstring)

我想按Time_Stamp和Time_stamp_ms排序(我知道如何从另一个问题开始)，然后每个时间周期达到1，我想从上一行获取Time_Stamp和Time_Stamp_ms.周期为1,2,3,4 ... n，这意味着它将始终以1递增.

I want to order by Time_Stamp and Time_stamp_ms (I know how to do this from another question) and then each time cycle reaches 1, I want to get the Time_Stamp and Time_Stamp_ms from the previous row. Cycle is 1,2,3,4......n Means it will always increment by 1.

此表可能包含数百万行.

This table will problably have millions and millions of rows.

也没有PHP.

我的桌子上有一个样品:

There is a sample of my table:

Time_Stamp              Time_Stamp_ms   d1      d2      d3      d4      d5      d6      cycle   name    

2014-04-24 09:09:37         765         5555    4444    3333    2222    1111    123     1       name
2014-04-24 09:09:37         845         5555    4444    3333    2222    1111    123     2       name
2014-04-24 09:09:37         925         5555    4444    3333    2222    1111    123     3       name
2014-04-24 09:09:38         5           5555    4444    3333    2222    1111    123     4       name
2014-04-24 09:09:38         85          5555    4444    3333    2222    1111    123     5       name
2014-04-24 09:09:38         165         5555    4444    3333    2222    1111    123     6       name
2014-04-24 09:09:38         245         5555    4444    3333    2222    1111    123     7       name
2014-04-24 09:09:38         325         5555    4444    3333    2222    1111    123     8       name
2014-04-24 09:09:38         405         5555    4444    3333    2222    1111    123     9       name
2014-04-24 09:09:38         485         5555    4444    3333    2222    1111    123     10      name
2014-04-24 09:09:38         565         5555    4444    3333    2222    1111    123     11      name
2014-04-24 09:09:38         645         5555    4444    3333    2222    1111    123     12      name
2014-04-24 09:09:38         725         5555    4444    3333    2222    1111    123     13      name
2014-04-24 09:09:38         805         5555    4444    3333    2222    1111    123     1       name
2014-04-24 09:09:38         885         5555    4444    3333    2222    1111    123     2       name
2014-04-24 09:09:38         965         5555    4444    3333    2222    1111    123     3       name
2014-04-24 09:09:39         45          5555    4444    3333    2222    1111    123     4       name
2014-04-24 09:09:39         125         5555    4444    3333    2222    1111    123     5       name
2014-04-24 09:09:39         205         5555    4444    3333    2222    1111    123     6       name
2014-04-24 09:09:39         285         5555    4444    3333    2222    1111    123     1       name
2014-04-24 09:09:39         365         5555    4444    3333    2222    1111    123     2       name
2014-04-24 09:09:39         445         5555    4444    3333    2222    1111    123     3       name
2014-04-24 09:09:39         525         5555    4444    3333    2222    1111    123     4       name
2014-04-24 09:09:39         605         5555    4444    3333    2222    1111    123     5       name
2014-04-24 09:09:39         685         5555    4444    3333    2222    1111    123     6       name
2014-04-24 09:09:39         765         5555    4444    3333    2222    1111    123     1       name
2014-04-24 09:09:39         845         5555    4444    3333    2222    1111    123     2       name
2014-04-24 09:09:39         925         5555    4444    3333    2222    1111    123     3       name

应该还给我:

    Time_Stamp              Time_Stamp_ms   d1      d2      d3      d4      d5      d6      cycle   name    


2014-04-24 09:09:38         725         5555    4444    3333    2222    1111    123     13      name
2014-04-24 09:09:39         205         5555    4444    3333    2222    1111    123     6       name
2014-04-24 09:09:39         685         5555    4444    3333    2222    1111    123     6       name

推荐答案

正如注释中所述，您确实需要一个指示行顺序的字段. pkey int primary key auto_increment字段不能保证最新的行始终具有最大的id，因此严格来说，它不能100％地起作用.包含确切插入时间的列即可.

As said in the comments, you really do need a field that indicates the order of the rows. A pkey int primary key auto_increment field does not guarantee that the newest row always has the largest id, so strictly speaking that does not work 100% of the time. A column containing exact insert time would do.

尽管(错误地知道)假设您的value字段是可以排序的字段，但此查询将使您获得id = 1之前的每一行.为了获得正确的结果，请创建一个顺序正确的字段，然后在两个order by子句中将value替换为该字段

Assuming though (falsely i know), that your value field is one that can be sorted with, this query will get you every row that comes before a id=1. To get a proper result, create a field that is in order, and replace value with that field in the two order by clauses

更新后的查询: http://sqlfiddle.com/#!2/9cf7d1/1 /0

SELECT Time_Stamp, Time_stamp_ms, cycle FROM (
    SELECT 
        COALESCE((@preVal=1), 0) AS afterOne, 
        m.*, 
        @preVal:=m.cycle  
    FROM mytable as m, 
    (SELECT @preVal:=NULL) AS d 
    ORDER BY Time_Stamp desc, Time_stamp_ms desc
) t 
WHERE afterOne = 1 
ORDER BY Time_Stamp, Time_stamp_ms;

另外一个注释.如果要处理大型数据集，则可以通过将内部查询插入到临时表中，在afterOne之后建立索引，然后选择最终结果，来发现性能得到极大提高. MySQL因子查询运行缓慢而臭名昭著.

One additional note. If you are dealing with a big dataset, you may find drastically improved performance by inserting the inner query in to a temporary table, indexing afterOne, and then selecting the final result. MySQL is notorious for being slow with subquerys.

PS.嗯，我现在看到我可能选择得很差，afterOne的意思是在命令升序之前.哦，好吧，无论如何，它都可以用作占位符.

PS. hmm, i see now that i may have chosen poorly, afterOne really means before when ordered ascending. Oh well, its a placeholder anyways, can be named anything that makes sense.

这篇关于在SELECT查询之前获取行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！