SQL 连接 + GROUPBY 从具有 MAX(Date) 的行中选择数据

Question

我无法找到此 SQL 查询的解决方案。

架构

编辑：添加项目表

项目表

• PK ItemID
• 许多其他列

链接表

• FK ItemID uniqueidentifier
• FK TransactionID uniqueidentifier

交易表

• PK ID uniqueidentifier
• EntryDateTime DateTime
• （其他几行 int、varchar...）

编辑 ：我想我还没有弄清楚关系。每个项目（表未显示）可以有多个事务。多个项目可以共享同一个事务（因此有链接表）。

请参阅我当前查询的底部。我留下了这个删除线以显示问题的进展。

我想做这样的查询。诀窍是我希望 t.varchar 和 t.int 列是 MAX(t.EntryDateTime) 行中的任何值。我什至不知道 group by 是否是执行此查询的正确方法。

SELECT lt.ItemID, MAX(t.EntryDateTime), t.varchar, t.int 
FROM LinkingTable lt
LEFT JOIN Transactions t ON lt.TransactionID = t.ID
GROUP BY lt.ItemID 

该表将在此 SQL 查询中进行连接，因此请尝试为我提供性能最佳的解决方案。假设 Table1 将包含数百万条记录。

SELECT 
(many columns)
FROM Table1
LEFT JOIN Table2 ON Table1.Table2ID = Table2.ID
LEFT JOIN Table3 ON ....
LEFT JOIN Table4 ON (Table2.ID = Table4.Table2ID and Table4.LocaleID = 127 and Table4.Type = 0)
LEFT JOIN **the query above** AS vTable1 ON  vTable1.ItemID = Table1.ID
WHERE Table1.CheckID IN (SELECT ID FROM Checks WHERE ....)

编辑：这是我的查询正在工作，但我不确定它是最有效的。 LinkingTable 有大约 200k 条记录，运行需要 6 秒。

SELECT DISTINCT lt.ItemID, t.EntryDateTime,  t.varchar, t.int
FROM LinkingTable lt 
     LEFT JOIN Transactions t ON t.id = (SELECT Top 1 t2.id FROM LinkingTable lt2
LEFT JOIN Transactions t2 on lt2.TransactionID = t2.ID
where lt2.ItemID = lt.ItemID ORDER BY t2.PrintTime DESC)

Answer 1

尝试一下，

SELECT i.*, outerT.EntryDateTime, outerT.varchar, outerT.int
FROM Item i
     LEFT JOIN
     (SELECT ItemId AS outerItemId, EntryDateTime, varchar, int
      FROM (SELECT ROW_NUMBER() OVER (PARTITION BY lt.ItemId ORDER BY t.EntryDateTime) AS RowNumber, lt.ItemId, t.EntryDateTime, t.varchar, t.int
            FROM Tranaction t INNER JOIN LinkingTable lt ON lt.TransactionId = t.ID) innerT
      WHERE RowNumber = 1) outerT ON outerT.outerItemId = Item.ID

希望这能解决您的问题

Answer 2

即使有超过一百万条记录，您也会遇到一些性能问题，但我会确保基于（ ItemID，主键）对事务表建立索引。原因是主键而不是日期——如果它是自动递增的，并且它是在事务发生时标记的日期/时间戳，那么它们本质上是一一的。文件中的最后一个条目始终具有最新日期。也就是说，ID 列使用索引应该比日期/时间更快。这也避免了查看最近日期的两个元素以及与该日期关联的交易 ID 的需要。这是我首先尝试查询的方式。

select 
      I.*,
      T2.*
   from
      Item I
         JOIN 
            ( select T.ItemID, MAX( T.PrimaryKey ) as LastEntryPerItem 
                 from Transactions T
                 group by T.ItemID ) MaxPerItem
            ON I.ItemID = T.ItemID

            JOIN Transactions T2
               on MaxPerItem.LastEntryPerItem = T2.PrimaryKey
   order by
      whatever

Answer 3

select lt.ItemId, t.entrydatetime, t.varchar, t.int
from LinkingTable lt
left join transactions t 
     on lt.transactionId = t.id
        and t.entryDateTime = (select max(t.EntryDateTime)
                               from transactions t2
                               where t2.id = t.id)

我之前也有过类似的问题
( SQL 连接以获取属于最近日期的值< /a>）。 JNK 还有另一种解决方案，涉及两个连接，可能会更快。我已经在下面发帖了。您需要进行测试，看看哪个性能更好。

select lt.ItemId, t.entrydatetime, t.varchar, t.int
from LinkingTable lt
inner join transactions t 
   on lt.ItemId= t.ItemId
Inner join (SELECT ItemId, MAX(entrydatetime) entrydatetime
            FROM transactions t2
            GROUP BY ItemId) SubQ
ON SubQ.ItemId= t.ItemId
AND SubQ.entrydatetime= t.entrydatetime

Answer 4

为什么不创建一个包含所有“多列”的视图，然后对该视图运行查询？