MYSQL-从大表的第二行选择数据 [英] MYSQL - Selecting data from second row in a large table

问题描述

我有一个外部第三方程序将数据库实时导出到mysql，并且我想显示数据以进行报告.因此，我无法更改结构，因为它是实时同步的.

I have an external 3rd party program export the database to mysql in real time, and I want to show data for reporting. So, I can't change the structure, because it's being sync in real time.

表结构是这样的

ID | Date       | Transaction
-----------------------------
12 | 2012-11-01 | 200
12 | 2012-11-02 | 250
12 | 2012-11-03 | 150
12 | 2012-11-04 | 1000
12 | 2012-11-05 | 225
....
13 | 2012-11-01 | 175
13 | 2012-11-02 | 20
13 | 2012-11-03 | 50
13 | 2012-11-04 | 100
13 | 2012-11-05 | 180
13 | 2012-11-06 | 195

数据非常大，而且每天都在增加.

The data are very large and keep getting bigger each day.

我想要做的就是基于这样的内容来构建报告(视图表):

What I want to do is to build a report (view table) based on something like this:

ID | Date       | Transaction | Prev Day Transaction
----------------------------------------------------
12 | 2012-11-01 | 200         | 0
12 | 2012-11-02 | 250         | 200
12 | 2012-11-03 | 150         | 250
12 | 2012-11-04 | 1000        | 150
12 | 2012-11-05 | 225         | 1000
....
13 | 2012-11-01 | 175         | 0
13 | 2012-11-02 | 20          | 175
13 | 2012-11-03 | 50          | 20
13 | 2012-11-04 | 100         | 50
13 | 2012-11-05 | 180         | 100
13 | 2012-11-06 | 195         | 180

我只是无法获得快速选择语句.当前，原始数据已经是283,120行.每天将增加约500行.

I just can't get the fast select statement. Currently the original data is already 283,120 rows. And it will grow like 500 rows daily.

我尝试过类似的事情:

SELECT *, (SELECT transaction FROM table as t2 WHERE t1.id=t2.id 
AND t1.date>t2.date ORDER BY t2.date DESC LIMIT 0,1)
FROM table AS t1

它正在工作，但是select语句非常慢.在大多数情况下，它在操作过程中会被切掉.

It's working, but the select statement is very slow. Most of the time, it's getting cut of in the middle of the operation.

我需要帮助的是一条非常快速的sql语句，稍后我可以使用它来构建视图表.

What I need help is a very fast sql statement, which later on I could use to build the view table.

推荐答案

查看此链接: http: //sqlfiddle.com/#!2/54a5e/12

select t.id,t.cDate,t.cTrans
  ,(case when @pID=t.id then @pTran else 0 end) as preT 
  ,(@pID :=t.id) as `tID`,(@pTran := t.cTrans) as `tTrans` 
from tb_test_1 as t,(select @pID = 0, @pTran = 0) as t2
order by id,cDate;

tID和tTrans列必须保留，并且不能在页面上显示.

tID and tTrans column must be retained, and cannot display on page.

请原谅我，因为我只会一点英语！

Please forgive me as I only know a little english!

这篇关于MYSQL-从大表的第二行选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！