分组查询前三条记录的和

[Sam大哥]

有一张表
ID time core
9587 2008/01/01 10
9586 2008/02/01 20
9587 2008/05/04 21
9587 2008/07/01 15
9587 2008/04/01 30
我想根据时间,查出最早三个记录的和
9587 2008/07/01 61
.
.

[fbmsyu]

支持1楼,不明白2楼.

[lintf1986]

UP

[lonlyhawk]

select  distinct [ID],
	(select sum(core) from 
		(select top 3 core from @tb where [ID]=t.[ID] order by [time]) a )
from @TB t 

[Lucifer-He]

[Quote=引用 6 楼 LiCeRForDream 的回复:]
引用 4 楼 chenjunsheep 的回复:
SQL codeselect top 3 sum(core) from 表 order by time

这个貌似不对吧， 2楼的不错，简单方便，易于理解 ：）
[/Quote]

看错楼层，应该是1楼的不错:)

[tjg5202]

小梁的方法很不错呀。。。高手就是不一样

[冷箫轻笛]

--借小梁的测试数据:
DECLARE @T TABLE (ID INT,time DATETIME,core INT)
INSERT INTO @T
SELECT 9587,'2008/01/01',10 UNION ALL
SELECT 9586,'2008/02/01',20 UNION ALL
SELECT 9587,'2008/05/04',21 UNION ALL
SELECT 9587,'2008/07/01',15 UNION ALL
SELECT 9587,'2008/04/01',30

--SQL查询如下:
select id,sum(core) as sum_core
from @t a
where time in (select top 3 time from @t where id = a.id order by time)
group by id

--结果集
/*
id          sum_core
----------- -----------
9586        20
9587        61

(2 行受影响)
*/

[阳新互联]

简单问题复杂化
让别人觉得你很NB？？

[水族杰纶]

select sum(core) from (select top 3 * from tb order by time)T

[liangCK]

---------------------------------
--  Author: liangCK 小梁
--  Date  : 2008-12-08 11:40:51
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,time DATETIME,core INT)
INSERT INTO @T
SELECT 9587,'2008/01/01',10 UNION ALL
SELECT 9586,'2008/02/01',20 UNION ALL
SELECT 9587,'2008/05/04',21 UNION ALL
SELECT 9587,'2008/07/01',15 UNION ALL
SELECT 9587,'2008/04/01',30

--SQL查询如下:

SELECT DISTINCT
    B.ID,
    MAX(B.time) AS time,
    SUM(B.core) AS core
FROM (
    SELECT DISTINCT ID FROM @T
)AS A
    CROSS APPLY(
        SELECT TOP(3)
            *
        FROM @T
        WHERE ID=A.ID
        ORDER BY time
    ) AS B
GROUP BY B.ID

/*
ID          time                    core
----------- ----------------------- -----------
9586        2008-02-01 00:00:00.000 20
9587        2008-05-04 00:00:00.000 61

(2 行受影响)

*/

[csdyyr]

DECLARE @TB TABLE(ID INT, time SMALLDATETIME,   core INT)
INSERT @TB
SELECT 9587,  '2008/01/01',  10 UNION ALL 
SELECT 9586,  '2008/02/01',  20 UNION ALL 
SELECT 9587,  '2008/05/04',  21 UNION ALL 
SELECT 9587,  '2008/07/01',  15 UNION ALL 
SELECT 9587,  '2008/04/01',  30

SELECT *,ID2=IDENTITY(INT,1,1) INTO # FROM @TB ORDER BY ID,time

SELECT SUM(CORE) AS CORE
FROM #
WHERE ID2<=3

DROP TABLE #
/*
CORE        
----------- 
60
*/

[chenjunsheep]

select top 3 sum(core) from 表 order by time

[csdyyr]

DECLARE @TB TABLE(ID INT, time SMALLDATETIME,   core INT)
INSERT @TB
SELECT 9587,  '2008/01/01',  10 UNION ALL 
SELECT 9586,  '2008/02/01',  20 UNION ALL 
SELECT 9587,  '2008/05/04',  21 UNION ALL 
SELECT 9587,  '2008/07/01',  15 UNION ALL 
SELECT 9587,  '2008/04/01',  30

SELECT *,ID2=IDENTITY(INT,1,1) INTO # FROM @TB ORDER BY ID,time

SELECT ID,SUM(CORE) AS CORE  
FROM (SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM # WHERE ID<A.ID) FROM # AS A) T
WHERE SEQ<=3
GROUP BY ID
ORDER BY ID

DROP TABLE #
/*
ID          CORE        
----------- ----------- 
9586        20
9587        61
*/

[Lucifer-He]

[Quote=引用 4 楼 chenjunsheep 的回复:]
SQL codeselect top 3 sum(core) from 表 order by time
[/Quote]
这个貌似不对吧， 2楼的不错，简单方便，易于理解 ：）