给出以下示例数据：
- Users
- +--------------------------------------------------+
- | ID | First Name | Last Name | Network Identifier |
- +--------------------------------------------------+
- | 1 | Billy | O'Neal | bro4 |
- +----+------------+-----------+--------------------+
- | 2 | John | Skeet | jsk1 |
- +----+------------+-----------+--------------------+
- Hardware
- +----+-------------------+---------------+
- | ID | Hardware Name | Serial Number |
- +----------------------------------------+
- | 1 | Latitude E6500 | 5555555 |
- +----+-------------------+---------------+
- | 2 | Latitude E6200 | 2222222 |
- +----+-------------------+---------------+
- HardwareAssignments
- +---------+-------------+-------------+
- | User ID | Hardware ID | Assigned On |
- +-------------------------------------+
- | 1 | 1 | April 1 |
- +---------+-------------+-------------+
- | 1 | 2 | April 10 |
- +---------+-------------+-------------+
- | 2 | 2 | April 1 |
- +---------+-------------+-------------+
- | 2 | 1 | April 11 |
- +---------+-------------+-------------+
我想编写一个SQL查询,这将产生以下结果：
- +--------------------+------------+-----------+----------------+---------------+-------------+
- | Network Identifier | First Name | Last Name | Hardware Name | Serial Number | Assigned On |
- +--------------------------------------------------------------------------------------------+
- | bro4 | Billy | O'Neal | Latitude E6200 | 2222222 | April 10 |
- +--------------------+------------+-----------+----------------+---------------+-------------+
- | jsk1 | John | Skeet | Latitude E6500 | 5555555 | April 11 |
- +--------------------+------------+-----------+----------------+---------------+-------------+
我的麻烦是,每个用户需要为每个用户选择最大的“分配开”日期,并用于实际连接…
有没有一个聪明的方式在sql中完成这个？
解决方法
- SELECT U.NetworkIdentifier,U.FirstName,U.LastName,H.HardwareName,H.SerialNumber
- FROM (SELECT UserID,MAX(AssignedOn) LastAssignment
- FROM HardwareAssignments
- GROUP BY UserID) AS T
- JOIN HardwareAssignments AS HA
- ON HA.UserId = T.UserID AND HA.AssignedOn = T.LastAssignment
- JOIN Users AS U ON U.ID = HA.UserID
- JOIN Hardware AS H ON H.ID = HA.HardwareID
- ORDER BY U.NetworkIdentifier;
这与Justin Niessner的答案之间的区别在于子查询出现在哪里在这里,我已经在FROM子句中创建了它.这几乎保证它被执行一次.当在Justin答案中的WHERE子句中有一个相关的子查询时,优化器可能会为每一行执行一次子查询 – 这在表很大的时候更昂贵.一个非常好的优化器可能会使事情变得平坦,以便两者相当.
