即使 2 条记录具有相同的时间戳，也会获取最新条目 [英] get latest entry even if 2 records have same timestamp

问题描述

我正在使用下面的代码来检索所有用户的最新数据.但是如果用户在同一时间戳添加了点，那么我想获得最后一个条目，而不是像下面的示例中那样.我该怎么做即使 2 条记录具有相同的时间戳，也要确保我获得最新条目.

I am using the code below to retrieve the latest data w.r.t all users .But if the user had points added at the same time stamp then I would like to get the last entry not both like in the example below.How do I make sure that I get latest entry even if 2 records have same timestamp.

http://sqlfiddle.com/#!2/374db/1

非常感谢您的帮助.提前致谢.

I really appreciate any help.Thanks in Advance.

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
sender varchar(255),
receiver varchar(255),
 msg varchar(255),
 date timestamp,
  points varchar(255),
  refno varchar(255),
 PRIMARY KEY (id)
);

CREATE TABLE if not exists tblB
(
id int(11) NOT NULL auto_increment ,
sno varchar(255),
name varchar(255),
 PRIMARY KEY (id)
);

CREATE TABLE if not exists tblC
(
id int(11) NOT NULL auto_increment ,
data varchar(255),
  refno varchar(255),
  extrarefno varchar(255),
 PRIMARY KEY (id)
);


INSERT INTO tblA (sender, receiver,msg,date,points,refno ) VALUES
('1', '2', 'buzz ...','2011-08-21 14:11:09','10','001'),
('1', '2', 'test ...','2011-08-21 14:12:19','20','002'),
('4', '2', 'test ...','2011-08-21 14:13:19','30','003'),
('1', '3', 'buzz ...','2011-08-21 14:11:09','10','004'),
('1', '3', 'test ...','2011-08-21 14:12:19','20','005'),
('1', '4', 'buzz ...','2011-08-21 14:11:09','10','006'),
('1', '4', 'test ...','2011-08-21 14:12:19','20','007'),
('3', '4', 'test ...','2011-08-21 14:13:19','20','008'),
('2', '4', 'test ...','2011-08-21 14:13:19','20','009');



INSERT INTO tblB (sno, name ) VALUES
('1', 'Aa'),
('2', 'Bb'),
('3', 'Cc'),
('4', 'Dd'),
('5', 'Ee'),
('6', 'Ff'),
('7', 'Gg'),
('8', 'Hh');


INSERT INTO tblC (data,refno,extrarefno ) VALUES
('data1', '001', '101'),
('data2', '002', '102'),
('data3', '003', '103'),
('data4', '004', '101'),
('data5', '005', '102'),
('data6', '006', '103'),
('data7', '007', '101'),
('data8', '008', '101'),
('data9', '009', '101');

////查询:

SELECT *
FROM (

  SELECT tblB.*, MAX(tblA.date) AS date
  FROM tblB
  JOIN tblA ON tblB.sno = tblA.receiver
  GROUP BY tblB.sno

) AS subset
JOIN tblA ON subset.sno = tblA.receiver 
            AND subset.date = tblA.date JOIN tblC ON tblA.refno=tblC.refno 

推荐答案

关键思想是使用 id 列而不是 date 列.它是自动递增的，所以最大的 id 应该是最近的.

The key idea is to use the id column instead of the date column. It is auto-incremented, so the biggest id should be more recent.

但是，您的查询还有另一个问题，就是在子查询中连接到 tblB.tblB 中的任意(不确定")值将在外部查询中返回.相反，只需在 tblA 上聚合并将 join 移动到 tblB 到外层:

However, your query has another problem which is the join to tblB in the subquery. Arbitrary ("indeterminate") values from tblB would be returned in the outer query. Instead, just aggregate on tblA and move the join to tblB to the outer level:

SELECT *
FROM (SELECT tblA.receiver, MAX(tblA.id) AS id
      FROM tblA
      GROUP BY  tblA.receiver
    ) subset JOIN
    tblA
    ON subset.receiver = tblA.receiver AND subset.id = tblA.id JOIN
    tblB
    on tblA.receiver = tblB.sno join
    tblC
    ON tblA.refno=tblC.refno ;

这篇关于即使 2 条记录具有相同的时间戳，也会获取最新条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！