如何使用SQL Server获取max(日期)的行记录?

I have two table called TableNumber and TableDetails

我有两个表叫做TableNumber和TableDetails

TableNumber

TableNumber

ItemID  TableDetailsID   Qty
----------------------------
111     12121             5
111     12121            20 
112     12121            10
123     12121             5

111     22121            25
111     22121            25
123     22121            2

TableDetails

TableDetails

ID       placed   TableDetailsNumber    Date
--------------------------------------------------
12121    London    555                  2017-05-31
22121    Dubai     556                  2017-07-31   <-- Max Date of Item 111

Expecting output

期望输出

ItemID    Placed    TableDetailsNumber    Date         Qty
----------------------------------------------------------
111       Dubai     556                  2017-07-31    50    //(25 + 25) of 22121
112       London    555                  2017-05-31    10
123       Dubai     556                  2017-07-31     2

I tried to do MAX(Date), But I cannot bring the column Placed,TableDetails using Grouping

我试着做MAX(Date)，但是我不能使用分组的方式来放置列和表细节

As well as I don't how to make sum the Qty of ItemID with Same TableDetailsNumber

另外，我也不知道如何用相同的TableDetailsNumber来对ItemID的Qty进行求和。

Please give me explanation with your answer to understand, Thanks

请给我解释一下你的答案，谢谢。

3 个解决方案

#1

Row_number() is a ranking window function that will assign a number that resets starting with 1 for each given set of columns in the partition by and in the order of the columns in the order by.

Row_number()是一个排名窗口函数，它将为分区中每一组给定的列分配一个以1开头的数字，并按照列的顺序进行排序。

If we partition by ItemId and order by Date desc then the row with the latest Date for each ItemId is given the row_number() of 1.

如果我们按ItemId划分，并按日期desc排序，那么每个ItemId的最新日期的行就会得到1的row_number()。

With that we can filter the results of the join and aggregation with either a subquery, common table expression, or with top with ties.

有了它，我们可以使用子查询、通用表表达式或顶部带有连接来过滤连接和聚合的结果。

using top with ties and row_number():

使用top with ties和row_number():

select top 1 with ties
    tn.ItemId
  , td.Placed 
  , td.TableDetailsNumber
  , td.Date
  , Qty = sum(tn.Qty)
from TableNumber tn
  inner join TableDetails td
    on tn.TableDetailsId = td.Id
group by
    tn.ItemId
  , td.Placed 
  , td.TableDetailsNumber
  , td.Date
order by row_number() over (partition by tn.ItemId order by td.Date desc);

or a common table expression with row_number()

或者使用row_number()的公共表表达式

;with cte as (
select 
    tn.ItemId
  , td.Placed 
  , td.TableDetailsNumber
  , td.Date
  , Qty = sum(tn.Qty)
  , rn = row_number() over (partition by tn.ItemId order by td.Date desc)
from TableNumber tn
  inner join TableDetails td
    on tn.TableDetailsId = td.Id
group by
    tn.ItemId
  , td.Placed 
  , td.TableDetailsNumber
  , td.Date
)
select ItemId, Placed, TableDetailsNumber, Date, Qty 
from cte
where rn = 1;

Another option using cross apply():

另一个使用cross apply()的选项:

select
    i.ItemId
  , x.Placed 
  , x.TableDetailsNumber
  , x.Date
  , x.Qty 
from (select distinct ItemId from TableNumber) i
  cross apply (
    select top 1
        tn.ItemId
      , td.Placed 
      , td.TableDetailsNumber
      , td.Date
      , Qty = sum(tn.Qty)
    from TableNumber tn
      inner join TableDetails td
        on tn.TableDetailsId = td.Id
    where tn.ItemId = i.Itemid
    group by
        tn.ItemId
      , td.Placed 
      , td.TableDetailsNumber
      , td.Date
    order by td.Date desc
    ) x

rextester demo: http://rextester.com/QNV39265

rextester演示:http://rextester.com/QNV39265

Each return:

每一个回报:

+--------+--------+--------------------+------------+-----+
| ItemId | Placed | TableDetailsNumber |    Date    | Qty |
+--------+--------+--------------------+------------+-----+
|    111 | Dubai  |                556 | 2017-07-31 |  50 |
|    112 | London |                555 | 2017-05-31 |  10 |
|    123 | Dubai  |                556 | 2017-07-31 |   2 |
+--------+--------+--------------------+------------+-----+

#2

This would appear to return the results that you want:

这似乎会返回您想要的结果:

select n.itemid, d.placed, d.TableDetailsNumber, d.date, sum(n.qty)
from tablenumber n join
     tabledetails d
     on n.TableDetailsId = d.TableDetailsId
group by n.itemid, d.placed, d.TableDetailsNumber;

But this doesn't get the latest date. For that:

但这不是最新的日期。:

select nd.*
from (select n.itemid, d.placed, d.TableDetailsNumber, d.date, sum(n.qty),
             max(d.date) over (partition by n.itemid) as maxdate
      from tablenumber n join
           tabledetails d
           on n.TableDetailsId = d.TableDetailsId
      group by n.itemid, d.placed, d.TableDetailsNumber
     ) nd
where date = maxdate;

#3

You could also do an inner join. I included all of the table creation code to replicate the test case and prove its accuracy.

您还可以进行内部连接。我包含了所有的表创建代码来复制测试用例并证明它的准确性。

Table Creation and Inserts

表的创建和插入

CREATE TABLE TableNumber (ItemID INT, TableDetailsID INT, Qty INT)
CREATE TABLE TableDetails (ID INT, placed VARCHAR(25), TableDetailsNumber INT, DetailsDate Date)


INSERT INTO TableNumber 
    values 
     (111,12121,5),
     (111,12121,20),
     (112,12121,10),
     (123,12121,5),
     (111,22121,25),
     (111,22121,25),
     (123,22121,2)

INSERT INTO TableDetails 
    values 
     (12121,'London',555,'2017-05-31'),
     (22121,'Dubai',556,'2017-07-31')

Query

查询

SELECT
     TN.ItemID
    ,TD.Placed
    ,TD.TableDetailsNumber
    ,MaxTable.maxDate
    ,Sum(TN.Qty) 'Qty'
FROM TableNumber TN
JOIN TableDetails TD 
    on TD.ID = TN.TableDetailsID
JOIN (
        SELECT 
         ItemID
        ,max(TD.DetailsDate) maxDate
        FROM TableNumber TN
        JOIN TableDetails TD 
            on TD.ID = TN.TableDetailsID
        GROUP BY 
         ItemID
        ) as MaxTable
    on MaxTable.maxDate = TD.DetailsDate
    and MaxTable.ItemID = TN.ItemID
GROUP BY 
     tn.ItemId
  , td.Placed 
  , td.TableDetailsNumber
  , MaxTable.maxDate

#1