在MySQL中创建视图或分区示例 [英] Create a View or Partition Example in MySQL

问题描述

大家好，



这是关于根据以下要求创建附加视图的问题。

如果我这样会很棒得到一些关于这个的信息



例子

我有一个表，其中列为Specified和Sample值，如下所示。

 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需要创建一个View如下或PartitionLike下面



像第一行一样ColumnResult列是0


第2行的

ColumnResult是
从第2行开始，ColumnResult是= 
 TotalRequest（第2行中的值） -  TotalRequest（Value in第1行
 
第3行的ColumnResult是
 TotalRequest（第3行中的值） -  TotalRequest（第2行中的值）
等等.. 

 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 所以  ... 

谢谢和问候

Durai Velan C.

解决方案

Hello Team，



我正在结束这个案子，因为我有解决方案。



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

< pre lang =sql> 
 
 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那么0 
 ELSE @ VColumnThree- @ ColumnThreeValuePrevious 
 END）作为ColumnThree，
 @ColumnThreeValuePrevious：= @ VColumnThree，
 @isFirstRow：= 0 
 FROM DBTABLE）差价
结束

谢谢和问候

Durai Velan C

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.

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

<pre lang="sql">

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

这篇关于在MySQL中创建视图或分区示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！