数据如下:
userId number date
51021 1 2008-12-27 16:05:15.390
51147 2 2008-12-29 10:05:16.217
5118 3 2008-12-29 10:05:24.933
51147 2 2008-12-29 10:05:43.843
51021 1 2008-12-29 10:05:50.390现在我想得到这样的结果
51147 4 2008-12-29 10:05:43.843
5118 3 2008-12-29 10:05:24.933
51021 2 2008-12-29 10:05:50.390
就是number字段从大到小排序了,userId相同的相加
userId number date
51021 1 2008-12-27 16:05:15.390
51147 2 2008-12-29 10:05:16.217
5118 3 2008-12-29 10:05:24.933
51147 2 2008-12-29 10:05:43.843
51021 1 2008-12-29 10:05:50.390现在我想得到这样的结果
51147 4 2008-12-29 10:05:43.843
5118 3 2008-12-29 10:05:24.933
51021 2 2008-12-29 10:05:50.390
就是number字段从大到小排序了,userId相同的相加
-- Author: liangCK 小梁
-- Date : 2008-12-01 10:14:34
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (userId INT,number INT,date DATETIME)
INSERT INTO @T
SELECT 51021,1,'2008-12-27 16:05:15.390' UNION ALL
SELECT 51147,2,'2008-12-29 10:05:16.217' UNION ALL
SELECT 5118,3,'2008-12-29 10:05:24.933' UNION ALL
SELECT 51147,2,'2008-12-29 10:05:43.843' UNION ALL
SELECT 51021,1,'2008-12-29 10:05:50.390'--SQL查询如下:SELECT
userId,
SUM(number) AS number,
MAX(date) AS date
FROM @T
GROUP BY userId
ORDER BY number DESC/*
userId number date
----------- ----------- -----------------------
51147 4 2008-12-29 10:05:43.843
5118 3 2008-12-29 10:05:24.933
51021 2 2008-12-29 10:05:50.390(3 行受影响)
*/
INSERT @TB
SELECT 51021, 1, '2008-12-27 16:05:15.390' UNION ALL
SELECT 51147, 2, '2008-12-29 10:05:16.217' UNION ALL
SELECT 5118, 3, '2008-12-29 10:05:24.933' UNION ALL
SELECT 51147, 2, '2008-12-29 10:05:43.843' UNION ALL
SELECT 51021, 1, '2008-12-29 10:05:50.390'SELECT * FROM (
SELECT userId,SUM(NUMBER) AS NUMBER ,MAX(DATE) AS DATE FROM @TB GROUP BY userId
) T
ORDER BY NUMBER DESC
/*
userId NUMBER DATE
----------- ----------- ------------------------------------------------------
51147 4 2008-12-29 10:05:43.843
5118 3 2008-12-29 10:05:24.933
51021 2 2008-12-29 10:05:50.390
*/
set nocount on
create table test(userId varchar(20),number int,date datetime)
insert into test select '51021','1','2008-12-27 16:05:15.390'
insert into test select '51147','2','2008-12-29 10:05:16.217'
insert into test select '5118','3','2008-12-29 10:05:24.933'
insert into test select '51147','2','2008-12-29 10:05:43.843'
insert into test select '51021','1','2008-12-29 10:05:50.390'
go
--测试
select userid,sum(number),max(date) from test
group by userid order by sum(number) desc
--删除测试环境
drop table test
set nocount off
/*
51147 4 2008-12-29 10:05:43.843
5118 3 2008-12-29 10:05:24.933
51021 2 2008-12-29 10:05:50.390
*/
select userid,sum(number),max(date) from tablename group by userid