这个SUM分组怎么解决，求教

[czyoooo]

这个SUM分组怎么解决，求教

ID TOTAL KIND 日期
B11 10 苹果 2018-01-03
B11 50 香蕉 2018-01-05
B11 -20 梨 2018-03-03
B22 -50 葡萄 2018-03-08

要求SUM后得这样的效果,SUM(TOTAL)后，日期取最后一条
B11 40 梨 2018-03-03
B22 -50 葡萄 2018-03-08

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(id varchar(10), TOTAL INT, KIND nvarchar(10),theDate date)
insert into t
select 'B11','10','苹果','2018-01-03'
union all select 'B11','50','香蕉','2018-01-05'
union all select 'B11','-20','梨','2018-03-03'
union all select 'B22','-50','葡萄','2018-03-08'

select a.id,a.total,b.kind,b.theDate from (
	select id,sum(total) as total 
	from t
	group by id
) as a
inner join (
	select ROW_NUMBER() over(partition by id order by theDate desc) as rid,* 
	from t
) as b
on a.id=b.id and b.rid=1

/*
id         total       kind       theDate
---------- ----------- ---------- ----------
B11        40          梨          2018-03-03
B22        -50         葡萄         2018-03-08
*/

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] nvarchar(23),[TOTAL] int,[KIND] nvarchar(22),[日期] Date)
Insert #T
select N'B11',10,N'苹果','2018-01-03' union all
select N'B11',50,N'香蕉','2018-01-05' union all
select N'B11',-20,N'梨','2018-03-03' union all
select N'B22',-50,N'葡萄','2018-03-08'
Go
--测试数据结束
SELECT  ID,TOTAL,KIND,日期
FROM    ( SELECT    ID ,
                    SUM(TOTAL) OVER ( PARTITION BY ID ) AS TOTAL ,
                    KIND ,
                    日期 ,
                    ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY 日期 DESC ) AS rn
          FROM      #T
        ) t
WHERE   rn = 1

[laj0600310219]

分完组统计的时候，直接用max（日期）作为不就可以了

[中国风]

e.g.

WITH CTET(id , TOTAL , KIND ,theDate )
AS (
select 'B11',10,'苹果','2018-01-03'
union all select 'B11',50,'香蕉','2018-01-05'
union all select 'B11',-20,'梨','2018-03-03'
union all select 'B22',-50,'葡萄','2018-03-08'
)
SELECT a.id
     , a.TOTAL
     , b.KIND
     , b.theDate
FROM
(SELECT id, SUM(TOTAL) AS TOTAL FROM CTET GROUP BY id) AS a
CROSS APPLY
(SELECT TOP 1
     KIND
   , theDate
 FROM CTET
 WHERE id=a.id
 ORDER BY theDate DESC) AS b;
 /*
 id	TOTAL	KIND	theDate
B11	40	梨	2018-03-03
B22	-50	葡萄	2018-03-08
*/

[chenjiecao123]

;WITH CET1 AS (
          SELECT t.id,
                 SUM(t.TOTAL)  AS TOTAL
          FROM   t             AS t
          GROUP BY
                 t.id
      ),
      CET2 AS(
          SELECT t.id,
                 t.TOTAL,
                 t.KIND,
                 t.theDate
          FROM   t AS t
          WHERE  t.theDate = (
                     SELECT MAX(t2.theDate)
                     FROM   t AS t2
                     WHERE  t2.id = t.id
                 )
      )
 
 SELECT CET1.id,
        CET1.TOTAL,
        CET2.KIND,
        CET2.theDate
 FROM   CET1
        JOIN CET2
             ON  CET1.id = CET2.id