如何在SQL中选择DISTINCT 另一列，通过具有MAX(列值)的行

Question

如何选择从具有MAX(列(值的行中区分另一个列引用。这是我的表：

Category |  Desc  | Total1 | Total2 |  MaxDate   | Topic
--------------------------------------------------------
   A     | A Desc |   1    |    3   | 2017-04-01 | Topic1
   A     | A Desc |   1    |    1   | 2017-05-10 | Topic2
   B     | B Desc |   1    |    1   | 2017-03-25 | Topic3

我想从Topic列中选择参考最大值MaxDate列。我希望表格是这样的：

Category |  Desc  | Total1 | Total2 |  MaxDate   | Topic
--------------------------------------------------------
   A     | A Desc |   2    |    4   | 2017-05-10 | Topic2
   B     | B Desc |   1    |    1   | 2017-03-25 | Topic3

然后Total1和 Total2 是 SUM 行中的值具有不同的

Answer 1

以

这种方式基于您的数据，我们也可以实现

Declare @Table1  TABLE 
    (Category varchar(1), Descp varchar(6), Total1 int, Total2 int, MaxDate datetime, Topic varchar(6))
;
INSERT INTO @Table1
    (Category, Descp, Total1, Total2, MaxDate, Topic)
VALUES
    ('A', 'A Desc', 1, 3, '2017-04-01 00:00:00', 'Topic1'),
    ('A', 'A Desc', 1, 1, '2017-05-10 00:00:00', 'Topic2'),
    ('B', 'B Desc', 1, 1, '2017-03-25 00:00:00', 'Topic3')
;

脚本

Select TT.Category,
   TT.Descp,
   TT.Total1,
   TT.Total2,
   TT.MaxDate,
   T.Topic 
            From @Table1 T
   INNER JOIN (
   select 
        Category,Descp,
            SUM(Total1)Total1,
            SUM(Total2)Total2,
            Max(MaxDate)MaxDate 
                    from @Table1
   GROUP BY  Category,Descp )TT
   ON T.MaxDate = TT.MAXDATE

Answer 2

可以使用 row_number 和 sum(( over(( 像这样

  DECLARE @SampleData AS TABLE
  (
      Category varchar(10),  [Desc]  varchar(20), Total1 int, Total2 int,  MaxDate datetime, Topic varchar(10)
  )
  INSERT INTO @SampleData VALUES
 ('A','A Desc', 1, 3, '2017-04-01', 'Topic1'),
 ('A','A Desc', 1, 1, '2017-05-10', 'Topic2'),
 ('B','B Desc', 1, 1, '2017-03-25', 'Topic3')
 ;WITH temp AS
 (
   SELECT  sd.Category, sd.MaxDate, sd.Topic, sd.[Desc], 
         row_number() OVER(PARTITION BY sd.Category ORDER BY sd.MaxDate desc) AS Rn,
         Sum(sd.Total1) OVER(PARTITION BY sd.Category ) AS Total1,
         Sum(sd.Total2) OVER(PARTITION BY sd.Category ) AS Total2
   FROM @SampleData sd
 )
 SELECT t.Category, t.[Desc], t.Total1,t.Total2, t.MaxDate, t.ToPic
 FROM temp t
 WHERE t.Rn = 1

演示链接：http://rextester.com/NQI25173