从 3 个表中选择数据，按最新日期值和另一个值分组

debugcn 发表于 Dev

公司

好的，所以我一直在绞尽脑汁，我想是时候问问集体了！

我正在使用 SQLServer，我有 3 个表，定义如下：

VolumeData

__________________________
| dataid | currentReading|
--------------------------
|   1    |      22       |
|   7    |      33       |
|   9    |      25       |
|   12   |      12       |
--------------------------

LatestData

________________________________________________________________
| dataid | unitNumber | unitLocation |      dateTimeStamp      |
----------------------------------------------------------------
|   1    |  2344454   |      2       | 2017-07-10 13:16:29.000 |
|   7    |  2344451   |     44       | 2017-07-10 13:22:29.000 |
|   9    |  2344456   |     92       | 2017-07-10 12:16:29.000 |
|   12   |  2344456   |     12       | 2017-07-10 12:13:23.000 |
----------------------------------------------------------------

unitData

____________________________________________________________________________________
| unitNumber | unitLocation | buildingNumber | officeNumber | officeName | country |
------------------------------------------------------------------------------------
|   2344454  |      2       |       44       |       1      |  Telford   |    UK   |
|   2344451  |     44       |       22       |       1      |  Telford   |    UK   |
|   2344456  |     92       |       12       |       2      |  Hamburg   |    GER  |
|   2344456  |     12       |       33       |       2      |  Hamburg   |    GER  |
------------------------------------------------------------------------------------

我只需要检索最新的 currentReading（基于最新数据中的 dateTimeStamp 字段）以及以下字段，按 unitNumber 分组：

currentReading, unitNumber, officeName, country, buildingNumber

需要注意的另一件事是记录可以按任何顺序到达。

以下是我尝试过的一个示例，我尝试了更多示例，但不幸的是我没有让它们保持打开状态：

SELECT 
      a.currentReading
      ,MAX(b.dateTimeStamp)
      ,c.unitNumber
      ,c.country
      ,c.officeName
  FROM [VolumeData] a INNER JOIN LatestData b ON a.dataid = b.dataid INNER JOIN
    unitData c ON c.[unitNumber] = b.[unitNumber] AND c.[unitLocation] = b.[unitLocation];

这导致： Column 'VolumeData.currentReading' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

任何建议将不胜感激！我尝试的所有方法要么导致检索太多行，要么导致逻辑 SQL 错误。我还应该补充一点，这些表包含数百万行，并且每天都在增长，所以我正在寻找一种非常有效的方法来做到这一点。

谢谢！

帕特尔

与Eric的答案相同的逻辑，使用 CTE 可能会更简洁一些并加入较少的记录。

DECLARE @VolumeData TABLE
( 
    dataid          int, 
    currentReading  int
); 

INSERT INTO @VolumeData VALUES(1, 22);
INSERT INTO @VolumeData VALUES(7, 33);
INSERT INTO @VolumeData VALUES(9, 25);
INSERT INTO @VolumeData VALUES(12,12);

DECLARE @LatestData TABLE
( 
    dataid          int, 
    unitNumber      int,
    unitLocation    int,
    dateTimeStamp   datetime
); 

INSERT INTO @LatestData VALUES(1,  2344454, 2,  '2017-07-10 13:16:29.000');
INSERT INTO @LatestData VALUES(7,  2344451, 44, '2017-07-10 13:22:29.000');
INSERT INTO @LatestData VALUES(9,  2344456, 92, '2017-07-10 12:16:29.000');
INSERT INTO @LatestData VALUES(12, 2344456, 12, '2017-07-10 12:13:23.000');

DECLARE @UnitData TABLE
( 
    unitNumber      int,
    unitLocation    int,
    buildingNumber  int,
    officeNumber    int,
    officeName      varchar(50),
    country         varchar(50)
); 

INSERT INTO @UnitData VALUES(2344454, 2,  44, 1, 'Telford', 'UK');
INSERT INTO @UnitData VALUES(2344451, 44, 22, 1, 'Telford', 'UK');
INSERT INTO @UnitData VALUES(2344456, 92, 12, 2, 'Hamburg', 'GER');
INSERT INTO @UnitData VALUES(2344456, 12, 33, 2, 'Hamburg', 'GER');

WITH LatestData_CTE (dataid, unitNumber, unitLocation, dateTimeStamp, rowNum)  
AS  
(  
    SELECT  dataid
          , unitNumber
          , unitLocation
          , dateTimeStamp
          , ROW_NUMBER() OVER (PARTITION BY unitNumber ORDER BY dateTimeStamp DESC) AS rowNum
    FROM @LatestData
)  
SELECT currentReading, l.unitNumber, officeName, country, buildingNumber
 FROM LatestData_CTE l 
    INNER JOIN @VolumeData v ON v.dataid = l.dataid 
    INNER JOIN @UnitData u ON u.[unitNumber] = l.[unitNumber] AND u.[unitLocation] = l.[unitLocation]
WHERE l.rowNum = 1

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。