简化问题: 在表格“ table1”中,我们具有以下列:RowID,ItemID,BranchID,RoomID,日期,数量
我正在尝试在每个BranchID中的每个RoomID中检索ItemID的最后一个数量。
一旦我知道了,计划就是将table1联接到ItemIDTable,BranchIDTable,RoomIDTable以获取ID的名称。
通过使用MAX(Date),我获得了仅在一个RoomID中的ItemID的数量,但是,如果ItemID在多个RoomID中,则函数MAX(Date)返回所有房间中的最新记录,而我需要每个房间的最新信息。
为ItemID = 50和BranchID = 4设置的数据:
+--------+----------+--------+-----+---------------------+------------+------------+--------------+ | ItemID | BranchID | RoomID | Qty | Date | ItemIDName | RoomIDNAme | BranchIDName | +--------+----------+--------+-----+---------------------+------------+------------+--------------+ | 50 | 4 | 1 | 7 | 2019-12-12 13:30:15 | ItemA | RoomB | BranchB | | 50 | 4 | 2 | 5 | 2019-12-12 13:30:20 | ItemA | RoomA | BranchB | | 50 | 4 | 2 | 8 | 2019-12-12 13:30:25 | ItemA | RoomA | BranchB | +--------+----------+--------+-----+---------------------+------------+------------+--------------+
我得到的结果(它从两个RoomID中选择了最新的一个):
+--------+----------+--------+-----+---------------------+------------+------------+--------------+ | ItemID | BranchID | RoomID | Qty | Date | ItemIDName | RoomIDNAme | BranchIDName | +--------+----------+--------+-----+---------------------+------------+------------+--------------+ | 50 | 4 | 2 | 8 | 2019-12-12 13:30:25 | ItemA | RoomA | BranchB | +--------+----------+--------+-----+---------------------+------------+------------+--------------+
预期(每个RoomID的最新数量):
+--------+----------+--------+-----+---------------------+------------+------------+--------------+ | ItemID | BranchID | RoomID | Qty | Date | ItemIDName | RoomIDNAme | BranchIDName | +--------+----------+--------+-----+---------------------+------------+------------+--------------+ | 50 | 4 | 1 | 7 | 2019-12-12 13:30:15 | ItemA | RoomB | BranchB | | 50 | 4 | 2 | 8 | 2019-12-12 13:30:25 | ItemA | RoomA | BranchB | +--------+----------+--------+-----+---------------------+------------+------------+--------------+
查询本身:
SELECT table1.ItemID,table1.BranchID,table1.RoomID,table1.Qty,table1.Date,ItemIDTable.ItemIDName,RoomIDTable.RoomIDName,BranchIDTable.BranchIDName FROM table1 INNER JOIN ItemIDTable ON table1.ItemID = ItemIDTable.ItemID INNER JOIN RoomIDTable ON table1.RoomID = RoomIDTable.RoomID INNER JOIN BranchIDTable ON table1.BranchID = BranchIDTable.BranchID WHERE (table1.Date IN ( SELECT MAX(Date) FROM table1 WHERE (ItemID = table1.ItemID) AND (BranchID = table1.BranchID) ) ) ORDER BY table1.ItemID
我试图缩短和简化标题以使其更具可读性。无论是使此查询正常工作还是使用更好的方法,A都会对此表示赞赏。