获取所有最新日期的记录[错误]

l2osamch 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(333)

我有一个表，我想返回所有记录的名字和父亲的名字与最新的数据，应该是动态的，每当一个新的最新日期被插入返回该记录。我试过了

SELECT rh.firstname, rh.fathername, rh.bloodonation_date
FROM personprofile rh,
  (SELECT MAX(bloodonationdate) AS maxdate, firstname
   FROM report_history
   GROUP BY firstname) maxresults
WHERE rh.firstname= maxresults.firstname
AND rh.bloodonation_date= maxresults.bloodonation_date;

如果我有这张table

firstname, fathername,bloodonation_date
--------------------------
Ahmed     issa   2018-12-24
Noora     issa   2018-12-21
Joseph    John   2018-12-24
Ash       Scott  2018-12-24
Isacf     jaack  2018-12-24
Ashley    Make   2018-12-24
James     Ma     2018-12-20
cd        cd     2018-12-24
cde       cde    2018-12-24
Marc      Mac    2018-12-16
Noora     Ahmed  2018-12-15
Rabeew    fbdb   2018-11-15

我怎样才能让它回来

firstname, fathername,bloodonation_date
--------------------------
Ahmed     issa   2018-12-24
Joseph    John   2018-12-24
Ash       Scott  2018-12-24
Isacf     jaack  2018-12-24
Ashley    Make   2018-12-24
cd        cd     2018-12-24

sql mysql

来源：https://stackoverflow.com/questions/53128929/getting-all-records-with-latest-date-error

2条答案

按热度按时间

hmtdttj41#

您可以尝试使用关联子查询。
架构（mysql v5.7）

CREATE TABLE T(
   firstname VARCHAR(50),
    fathername VARCHAR(50),
bloodonation_date DATE
);
INSERT INTO T VALUES ('Ahmed','issa','2018-12-24');
INSERT INTO T VALUES ('Noora','issa','2018-12-21');
INSERT INTO T VALUES ('Joseph','John','2018-12-24');
INSERT INTO T VALUES ('Ash','Scott','2018-12-24');
INSERT INTO T VALUES ('Isacf','jaack','2018-12-24');
INSERT INTO T VALUES ('Ashley','Make','2018-12-24');
INSERT INTO T VALUES ('James','Ma','2018-12-20');
INSERT INTO T VALUES ('cd','cd','2018-12-24');
INSERT INTO T VALUES ('cde','cde','2018-12-24');
INSERT INTO T VALUES ('Marc','Mac','2018-12-16');
INSERT INTO T VALUES ('Noora','Ahmed','2018-12-15');
INSERT INTO T VALUES ('Rabeew','fbdb','2018-11-15');

查询#1

SELECT *
FROM T 
WHERE bloodonation_date = (
  SELECT  MAX(bloodonation_date) 
  FROM T                       
);
| firstname | fathername | bloodonation_date |
| --------- | ---------- | ----------------- |
| Ahmed     | issa       | 2018-12-24        |
| Joseph    | John       | 2018-12-24        |
| Ash       | Scott      | 2018-12-24        |
| Isacf     | jaack      | 2018-12-24        |
| Ashley    | Make       | 2018-12-24        |
| cd        | cd         | 2018-12-24        |
| cde       | cde        | 2018-12-24        |