对一列进行 SELECT DISTINCT，返回多个其他列 (SQL Server) [英] SELECT DISTINCT on one column, return multiple other columns (SQL Server)

问题描述

我正在尝试编写一个查询，从 GPSReport 表中为每个唯一设备返回最新的 GPS 位置.表中有 50 个设备，所以我只想返回 50 行.

I'm trying to write a query that returns the most recent GPS positions from a GPSReport table for each unique device. There are 50 devices in the table, so I only want 50 rows returned.

这是我目前所拥有的(不工作)

Here is what I have so far (not working)

SELECT TOP(SELECT COUNT(DISTINCT device_serial) FROM GPSReport) * FROM GPSReport AS G1
RIGHT JOIN
(SELECT DISTINCT device_serial FROM GPSReport) AS G2
ON G2.device_serial = G1.device_serial
ORDER BY G2.device_serial, G1.datetime DESC

这会返回 50 行，但不会为每个 device_serial 返回唯一的行.它返回第一个设备的所有报告，然后返回第二个设备的所有报告，依此类推.

This returns 50 rows, but is not returning a unique row for each device_serial. It returns all of the reports for the first device, then all of the reports for the second device, etc.

我在一个查询中尝试做的事情是否可行?

Is what I'm trying to do possible in one query?

推荐答案

SELECT * FROM
GPSReport AS G1
JOIN (SELECT device_serial, max(datetime) as mostrecent 
      FROM GPSReport group by device_serial) AS G2
ON G2.device_serial = G1.device_serial and g2.mostrecent = g1.datetime
ORDER BY G1.device_serial

这篇关于对一列进行 SELECT DISTINCT，返回多个其他列 (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！