Выберите самую последнюю строку, отфильтрованную по нескольким столбцам

у меня есть sql таблица

+------+-----------+-----------+--------+--------------+
| rowID| location | fk_Item_ID |Balance |Inventory_Date|
+------+-----------+-----------+--------+--------------+
| 1    | 1         | p1        |500     |2019-01-10    |
| 2    | 1         | p2        |450     |2019-01-05    | 
| 3    | 2         | p2        |500     |2019-01-10    |
| 4    | 1         | p1        |300     |2019-01-15    |
| 5    | 1         | p1        |350     |2019-01-16    |
+------+-----------+-----------+--------+--------------+

Я пытаюсь написать оператор SQL или, предпочтительно, LINQ, чтобы получить самую последнюю строку по дате, а затем максимальный номер строки для каждой части в каждом месте

SELECT * FROM Inventory_Log AS l1
WHERE 
    rowID IN (
        SELECT MAX(rowID) FROM Inventory_Log 
        WHERE 
            fk_Item_ID = l1.fk_Item_ID 
            AND Inventory_Date = (
                SELECT MAX(Inventory_Date) 
                FROM Inventory_Log 
                WHERE fk_Item_ID = l1.fk_Item_ID
            )
    )
ORDER by l1.fk_Item_ID

Ожидаемые результаты:

+------+-----------+-----------+--------+--------------+
| rowID| location | fk_Item_ID |Balance |Inventory_Date|
+------+-----------+-----------+--------+--------------+
| 2    | 1         | p2        |450     |2019-01-05    | 
| 3    | 2         | p2        |500     |2019-01-10    |
| 5    | 1         | p1        |350     |2019-01-16    |
+------+-----------+-----------+--------+--------------+

Я не уверен, как включить несколько местоположений для одной и той же части. Она будет помещена в следующий класс.

public class _ItemLocations
{
   public string Item_ID { get; set; }
   public string Location { get; set; }
   public decimal Qty { get; set; }
}

Я еще не пробовал в LINQ, потому что я покупаю его в sql


sql sql-server linq ef-core-2.2
person RYANLIL    schedule 18.03.2019    source источник

Ответы (1)


arrow_upward
1
arrow_downward

В SQL Server используйте ROW_NUMBER():

SELECT * FROM (
    SELECT 
        i.*, 
        ROW_NUMBER() OVER(PARTITION BY location, fk_Item_ID ORDER BY Inventory_Date DESC, rowID DESC) rn
    FROM Inventory_Log i
) x WHERE rn = 1

NB: мне неясно, какую сортировку вы ожидаете от rowID, поскольку вы заявили, что хотите, чтобы записи были отсортированы по Inventory_Date: я использовал его в качестве второго критерия сортировки (следовательно, при необходимости он нарушит верхние связи по Inventory_Date).

Демонстрация скрипта БД:

rowID | location | fk_Item_ID | Balance | Inventory_Date      | rn
----: | -------: | :--------- | ------: | :------------------ | :-
    2 |        1 | p2         |     450 | 05/01/2019 00:00:00 | 1 
    3 |        2 | p2         |     500 | 10/01/2019 00:00:00 | 1 
    5 |        1 | p1         |     350 | 16/01/2019 00:00:00 | 1
person GMB    schedule 18.03.2019