mysql视图分区处理_在MySQL中创建视图或分区示例

Hello All,

This is Regards of creating a View of Additional column with following requirement.

It would be great if I get some information on this

Example

I have a Table with the column as Specified and Sample value as below.TABLENAME

RowNumber UtcTimeStamp TotalRequest

1 2015-03-04 12:20:00 10441856

2 2015-03-04 12:30:00 10447205

3 2015-03-04 12:40:00 10454634

4 2015-03-04 12:50:00 10461790

5 2015-03-04 13:00:00 10469014

6 2015-03-04 13:10:00 10476494

I would need to create a View as following or PartitionLike below

Like always the First Row "ColumnResult" Column is 0

"ColumnResult" of Row 2 is

And from Row 2 Onwards, "ColumnResult"is =

TotalRequest(Value in Row 2 ) - TotalRequest(Value in Row 1 )

"ColumnResult" of Row 3 is

TotalRequest(Value in Row 3 ) - TotalRequest(Value in Row 2 )

and So on..

RowNumber UtcTimeStamp TotalRequest ColumnResult

1 2015-03-04 12:20:00 10441856 0

2 2015-03-04 12:30:00 10447205 5349

3 2015-03-04 12:40:00 10454634 7429

4 2015-03-04 12:50:00 10461790 7156

5 2015-03-04 13:00:00 10469014 ...

6 2015-03-04 13:10:00 10476494 so on...

Thanks and Regards

Durai Velan C.

解决方案Hello Team,

I am closing this case, as I have got solution for it.

=======================================================

CREATE PROCEDURE GetDiffView()

BEGIN

SET @isFirstRow=1;

SET @ColumnThreeValuePrevious=0;

SELECT ColumnOne, ColumnTwo, ColumnThree

FROM (

SELECT ColumnOne, ColumnTwo.

@VColumnThree:=ColumnThree as CColumnThree,

(CASE

WHEN @isFirstRow=1 THEN 0

ELSE @VColumnThree-@ColumnThreeValuePrevious

END) as ColumnThree,

@ColumnThreeValuePrevious:=@VColumnThree,

@isFirstRow:=0

FROM DBTABLE ) Diff

END

Thanks and Regards

Durai Velan C