错误的查询MySQL选择具有最旧或最新日期的记录

I need to know how to obtain the code of access (IdAccess) the most older (or more recent) date of access that some user (identified with IdUser) made from the table AccessTbl using different queries. Each user.

我需要知道如何获取访问代码(IdAccess)使用不同查询从表AccessTbl创建的某些用户(使用IdUser标识)访问的最旧(或更新)日期。每个用户。

CREATE TABLE AccessTbl ( 
IdAcess VARCHAR(16) UNIQUE NOT NULL, 
IdUser VARCHAR(16) NOT NULL,
TheDate DATE NOT NULL 
);
INSERT INTO AccessTbl (IdAcess, IdUser, TheDate) VALUES 
('A00', '0', '1983-12-30'), 
('A01', '0', '2004-09-09'), 
('A02', '1', '2013-02-01'), 
('A03', '1', '2012-05-09'), 
('A04', '2', '1983-12-30'), 
('A13', '2', '2013-03-01'), 
('A05', '2', '2004-09-09'), 
('A06', '3', '2013-02-01'), 
('A07', '3', '2012-05-09'), 
('A08', '4', '1983-12-30'), 
('A09', '4', '2004-09-09'), 
('A10', '5', '2013-04-01'), 
('A12', '5', '2013-03-01'), 
('A11', '5', '2012-05-01');

Example:

The user 5 the most recent is 'A10' and the most oldest 'A11' The user 4 the most recent is 'A09' and the most oldest 'A08'

最近的用户5是'A10',最老的'A11'最近的用户4是'A09',最老的'A08'

But, I need all newest date of all users in only one query.

但是,我只需要一个查询中的所有用户的所有最新日期。

I tried with...

我试过......

SELECT IdAcess, IdUser, TheDate
FROM AccessTbl
WHERE (
  TheDate IN (SELECT MAX(TheDate) FROM AccessTbl  GROUP BY IdUser)
)  GROUP BY IdUser;

But I have:

但是我有:

+---------+--------+
| IdAcess | IdUser |
+---------+--------+
| A01     | 0      |
| A02     | 1      |
| A05     | 2      |  //BAD!!!!! must be A13
| A06     | 3      |
| A09     | 4      |
| A10     | 5      |
+---------+--------+
6 rows in set (0.13 sec)

Later, I tried with:

后来,我试过:

SELECT IdAcess, IdUser, MAX(TheDate) FROM AccessTbl GROUP BY IdUser;

SELECT IdAcess,IdUser,MAX(TheDate)FROM AccessTbl GROUP BY IdUser;

Having...

+---------+--------+--------------+
| IdAcess | IdUser | MAX(TheDate) |
+---------+--------+--------------+
| A00     | 0      | 2004-09-09   | //Must be A01
| A02     | 1      | 2013-02-01   | 
| A04     | 2      | 2013-03-01   | //Must be A13
| A06     | 3      | 2013-02-01   |
| A08     | 4      | 2004-09-09   | //Must be A09
| A10     | 5      | 2013-04-01   |
+---------+--------+--------------+
6 rows in set (0.00 sec)

Thank you for your valuable help.

感谢您的宝贵帮助。

Ann

3 个解决方案

#1

If you want to get both the oldest and newest simultaneously, you'll need to use subqueries to compare existing dates (filtered by IdUser) with the selected min and max date.

如果要同时获取最旧和最新的,则需要使用子查询将现有日期(按IdUser过滤)与选定的最小和最大日期进行比较。

SELECT IdAcess
FROM AccessTbl
WHERE IdUser = ?
AND (
  TheDate IN (SELECT MAX(TheDate) FROM AccessTbl WHERE IdUser = ?)
  OR TheDate IN (SELECT MIN(TheDate) FROM AccessTbl WHERE IdUser = ?)
)

http://sqlfiddle.com/#!2/38594/20/0

#2

You can do either the newest or the oldest using a LEFT JOIN;

您可以使用LEFT JOIN执行最新或最旧的操作;

To get the newest entry for user 5;

获取用户5的最新条目;

SELECT *
FROM AccessTbl a
LEFT JOIN AccessTbl b
  ON a.TheDate < b.TheDate AND a.IdUser = b.IdUser
WHERE b.TheDate IS NULL AND a.IdUser = '5'

To get the oldest, just flip the < to a >;

要获得最老的,只需将 <翻转到> ;

  ON a.TheDate > b.TheDate AND a.IdUser = b.IdUser

To get the newest row for all users at once, just skip the IdUser condition;

要一次为所有用户获取最新行,只需跳过IdUser条件;

SELECT *
FROM AccessTbl a
LEFT JOIN AccessTbl b
  ON a.TheDate < b.TheDate AND a.IdUser = b.IdUser
WHERE b.TheDate IS NULL

An SQLfiddle to test with.

一个要测试的SQLfiddle。

#3

Just a way:

只是一种方式:

SELECT IdAcess AS Oldest, (SELECT IdAcess FROM AccessTbl
                           WHERE IdUser = 5
                           ORDER BY TheDate DESC LIMIT 1) AS Newest
FROM AccessTbl
WHERE IdUser = 5
ORDER BY TheDate ASC LIMIT 1

Here's the SQL Fiddle to test: http://sqlfiddle.com/#!2/4b0e68/36

这是测试的SQL小提琴:http://sqlfiddle.com/#!2/4b0e68/36

To get the newest IdAcess for every user in just one query execute this:

要在一个查询中为每个用户获取最新的IdAcess,请执行以下操作:

SELECT *
FROM AccessTbl a
LEFT JOIN AccessTbl b
  ON a.TheDate < b.TheDate AND a.IdUser = b.IdUser
WHERE b.TheDate IS NULL
ORDER BY a.IdUser

Result

IDACESS     IDUSER     THEDATE
A01         0          September, 09 2004 00:00:00+0000
A02         1          February, 01 2013 00:00:00+0000
A13         2          March, 01 2013 00:00:00+0000
A06         3          February, 01 2013 00:00:00+0000
A09         4          September, 09 2004 00:00:00+0000
A10         5          April, 01 2013 00:00:00+0000

Here's the SQL Fiddle to test: http://sqlfiddle.com/#!2/4b0e68/44

这是测试的SQL小提琴:http://sqlfiddle.com/#!2/4b0e68/44

#1