SQL连接只到最大行的难题

Question

SQL连接只到最大行的难题

11

给定以下示例数据：

  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    |
+--------------------+------------+-----------+----------------+---------------+-------------+

我的问题是需要对于每个用户选择最大的“Assigned On”日期，并将其用作实际连接的日期。是否有巧妙的方法可以在SQL中完成这个操作？

- Billy ONeal

2

所以你得到了Jon Skeet的旧笔记本电脑？酷！ - APC

1

@APC：没错！公司认为他在 StackOverflow 上花费了太多时间... :) - Billy ONeal

3个回答

8

select * from Users as u
inner join HardwareAssignments as ha
    on u.id = ha.userid
inner join Hardware as h
    on uh.hardwareid = h.id
where ha.AssignedOn = (select max(assignedon)
                       from HardwareAssignments as ha2
                       where ha.userid = ha2.userid)

那可能会让你接近答案，但不确定是否完全准确。

- Justin Niessner

调整列以满足您的需求，但解决方案的关键部分是子查询。 - Marcus Adams

糟糕！*比尔现在感觉有点傻。 - Billy ONeal

0

使用group by和max来过滤连接的结果。

- kubal5003

只返回一行。我需要每个用户一行。 - Billy ONeal

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jonathan Leffler · Accepted Answer

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子句中。这几乎保证只执行一次。当WHERE子句中存在相关子查询时，如Justin的答案所示，优化器可能会为每个行执行子查询-当表很大时，这更加昂贵。一个真正好的优化器可能会展开事物以使得两者等效。