如何在mysql中获取多行的最后一个值？

How to select last value in multiple rows in MySQL ?

如何选择MySQL中多行的最后一个值?

select name,date,value from table1;

从table1中选择名称,日期,值;

Name    Date               Value
A       01-Jan-2013         3
A       02-Jan-2013         4
B       04-Jan-2013         2
B       05-Jan-2013         8

Result I need :

结果我需要:

Name    Date               Value
A       01-Jan-2013         3
A       02-Jan-2013         4
        Last_value          4
B       04-Jan-2013         2
B       05-Jan-2013         8
        Last_value          8

How to do that in MySQL store procedure ?

如何在MySQL存储过程中执行此操作?

Regards

3 个解决方案

#1

There's no "last" record. You need to sort them by the column they should be ordered by, then only select the last row.

没有“最后”记录。您需要按照它们应该排序的列对它们进行排序,然后只选择最后一行。

/* SELECT ... */ ORDER BY sort_column DESC LIMIT 1

#2

This seems better suited for your Presentation Layer, but it is possible to write it using SQL. Something like this should work using UNION ALL to return that last record per group:

这似乎更适合您的表示层,但可以使用SQL编写它。像这样的东西应该使用UNION ALL来返回每组的最后一条记录:

SELECT Name, Dt, Value
FROM (
    SELECT Name, Dt, Value, Name Name2
    FROM YourTable 
    UNION ALL
    SELECT '' Name, 'Last_Value', T.Value, T.Name Name2
    FROM YourTable T
       JOIN (
         SELECT Name, MAX(dt) MaxDt
         FROM YourTable
         GROUP BY Name
       ) T2 ON T.Name = T2.Name AND T.dt = T2.MaxDt
    ORDER BY Name2, Dt, Value
  ) t

SQL Fiddle Demo

SQL小提琴演示

Resulting in:

NAME         DT                      VALUE
A            2013-01-01 00:00:00     3
A            2013-01-02 00:00:00     4
             Last_Value              4
B            2013-01-04 00:00:00     2
B            2013-01-05 00:00:00     8
             Last_Value              8

EDIT: To get the Summed Group Total, you have to introduce the use of user defined variables. Here is an example:

编辑:要获得Summed Group Total,您必须引入用户定义变量的使用。这是一个例子:

SELECT Name, Dt, IF(Name='',summedTotal,Value) Value
FROM (
  SELECT @summedTotal:=IF(@prevRow=Name,@summedTotal+Value,Value) summedTotal,
    Name, Dt, Value, Name Name2,
    @prevRow:=Name
  FROM YourTable
    JOIN (SELECT @summedTotal:=0) t
  UNION ALL
  SELECT summedTotal, '' Name, 'Last_Value', T.Value, T.Name Name2, pr
  FROM (
      SELECT @summedTotal:=IF(@prevRow=Name,@summedTotal+Value,Value) summedTotal,
        Name, Dt, Value, Name Name2,
        @prevRow:=Name pr
      FROM YourTable
        JOIN (SELECT @summedTotal:=0) t
    ) T
    JOIN (
      SELECT Name, MAX(dt) MaxDt
      FROM YourTable
      GROUP BY Name
    ) T2 ON T.Name = T2.Name AND T.dt = T2.MaxDt
  ORDER BY Name2, Dt, Value
  ) t

And more SQL Fiddle

还有更多的SQL小提琴

#3

You can select the MAX value using inner join. But you haven't specified your primary key column.

您可以使用内部连接选择MAX值。但是您尚未指定主键列。

SELECT
  m.*
FROM mytable AS m
  INNER JOIN (SELECT
        MAX(primary_key_column)
          FROM mytable
          GROUP BY Name) AS l
    ON l.primary_key_column = m.primary_key_column

#1