如何使用分组获取（选择）具有特定列的最大值的行

I have a table with duplicate data of books issued by students. Students can issue books(one book at a time in this case). SQL for the table trn_books:

我有一张桌子,上面有学生发的书籍重复数据。学生可以发行书籍(在这种情况下,一次一本书)。表trn_books的SQL:

CREATE TABLE IF NOT EXISTS `trn_books` (
  `tb_id` int(11) NOT NULL,
  `tb_roll` varchar(50) NOT NULL,
  `tb_isbn` varchar(50) NOT NULL,
  `tb_date_issue` varchar(100) NOT NULL,
  `tb_date_return` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

INSERT INTO `trn_books` (`tb_id`, `tb_roll`, `tb_isbn`, `tb_date_issue`, `tb_date_return`) VALUES
(1, 'L23', '1234441225', '2017-01-10', '2017-01-20'),
(2, 'L54', '1225565412', '2017-01-12', '2017-01-22'),
(3, 'L23', '1225565412', '2017-01-22', '2017-01-31'),
(4, 'L24', '1225565412', '2017-02-01', '2017-02-11'),
(5, 'L23', '1225565412', '2017-02-15', '2017-02-25');

ALTER TABLE `trn_books`
  ADD PRIMARY KEY (`tb_id`);

Record storing in this way: Query: SELECT * FROM trn_books

以这种方式记录存储:查询:SELECT * FROM trn_books

Now I want to track the last activity of students using grouping by their tb_roll. As we see that, the student with roll number L23 issued books three times where the last activity was:(according to max value of tb_date_return). (5,L23,1225565412, 2017-02-15,2017-02-25). If I group by the table I am getting :

现在我想通过他们的tb_roll跟踪学生使用分组的最后一项活动。正如我们所看到的那样,卷号为L23的学生发布了三次最后一次活动的书籍:(根据tb_date_return的最大值)。 (5,L23,1225565412,2017-02-15,2017-02-25)。如果我按照表格分组:

Query: SELECT * FROM trn_books group by tb_roll

查询:按tb_roll选择SELECT * FROM trn_books

But I need the result of the students' latest activity(max value of tb_date_return). Desire result should be tb_id's-->5,4,2.

但我需要学生最新活动的结果(tb_date_return的最大值)。欲望结果应该是tb_id的 - > 5,4,2。

But I am getting tb_id's->1,4,2 How to achieve it by mysql query.

但我得到tb_id's-> 1,4,2如何通过mysql查询实现它。

Thank you for your help...

感谢您的帮助...

3 个解决方案

#1

You want the max date returned for any given tb_roll?

您想要为任何给定的tb_roll返回最大日期吗?

select tb_roll, max(tb_date_return) as max_return
from trn_books
group by tb_roll

#2

This is normally accomplished using a join and group by:

这通常通过以下方式使用join和group来完成:

select b.*
from trn_books b join
     (select tb_roll, max(tb_date_return) as max_tb_date_return
      from trn_books
      group by tb_roll
     ) b2
     on b2.tb_roll = b.tb_roll and
        b2.max_tb_date_return = b.tb_date_return;

Another common method is a correlated subquery:

另一种常见方法是相关子查询:

select b.*
from trn_books b
where b.tb_date_return = (select max(b2.tb_date_return)
                          from trn_books b2
                          where b2.tb_roll = b.tb_roll
                         );

#3

This is a max group wise problem. You need to solve this with a delivered table.

这是一个最大的群体问题。您需要使用已交付的表来解决此问题。

Query

    SELECT
     trn_books.*
    FROM (
      SELECT
         tb_roll
       , MAX(tb_date_return) as max_return 
      FROM 
       trn_books
      GROUP BY
       tb_roll
      ORDER BY 
       tb_roll ASC
    )
     AS 
      tb_roll_max
    INNER JOIN 
      trn_books
    ON
       tb_roll_max.tb_roll = trn_books.tb_roll
     AND
       tb_roll_max.max_return = trn_books.tb_date_return
     ORDER BY 
       trn_books.tb_id DESC

Result

 tb_id  tb_roll  tb_isbn     tb_date_issue  tb_date_return  
------  -------  ----------  -------------  ----------------
     5  L23      1225565412  2017-02-15     2017-02-25      
     4  L24      1225565412  2017-02-01     2017-02-11      
     2  L54      1225565412  2017-01-12     2017-01-22

#1