给出以下示例数据：
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子句中有一个相关的子查询时,优化器可能会为每一行执行一次子查询 – 这在表很大的时候更昂贵.一个非常好的优化器可能会使事情变得平坦,以便两者相当.
