重复行统计问题？？？？求助！！！

[qingniao8]

有如下表：

bill_id time
1 2
1 4
2 1
2 3
2 5
2 10
3 3
3 20
3 50
想实现同一ID的最后一个time值减去第一个值？帮忙写下SQL（SQL SERVER2000）,谢谢

[幸运的意外]

是不是time有一定规律，比如同一个ID下是从小到大排列的？如果是可以这样来：
select bill_id, (max(time) - min(time)) as time
from 表
group by bill_id

[liangCK]

SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM tb;

SELECT
    A.bill_id,
    C.time-B.time AS time
FROM (
      SELECT bill_id,MIN(ID) AS min_id,MAX(ID) AS max_id
      FROM #T
      GROUP BY bill_id
    ) AS A
JOIN #T AS B
    ON A.bill_id=B.bill_id
       AND A.min_id=B.ID
JOIN #T AS C
    ON A.bill_id=C.bill_id
       AND A.max_id=C.ID

[qingniao8]

结果：
bill_id t

1 2
2 9
3 47

[liangCK]

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (bill_id INT,time INT)
INSERT INTO @T
SELECT 1,2 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,3 UNION ALL
SELECT 2,5 UNION ALL
SELECT 2,10 UNION ALL
SELECT 3,3 UNION ALL
SELECT 3,20 UNION ALL
SELECT 3,50

--SQL查询如下:

SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM @T;

SELECT
    A.bill_id,
    C.time-B.time AS time
FROM (
      SELECT bill_id,MIN(ID) AS min_id,MAX(ID) AS max_id
      FROM #T
      GROUP BY bill_id
    ) AS A
JOIN #T AS B
    ON A.bill_id=B.bill_id
       AND A.min_id=B.ID
JOIN #T AS C
    ON A.bill_id=C.bill_id
       AND A.max_id=C.ID
       
DROP TABLE #T;

/*
bill_id     time
----------- -----------
1           2
2           9
3           47

(3 行受影响)
*/

[qingniao8]

哇，这么快～～～～～太感谢了，time是有规律的，同一ID都是由小到大～

[htl258_Tony]

if object_id('[tb]') is not null drop table [tb] 
 go 
create table [tb]([bill_id] int,[time] int)
insert [tb] select 1,2
union all select 1,4
union all select 2,1
union all select 2,3
union all select 2,5
union all select 2,10
union all select 3,3
union all select 3,20
union all select 3,50
go
select bill_id,max([time])-min([time]) [time]  
from tb 
group by bill_id
/*
bill_id     time
----------- -----------
1           2
2           9
3           47

(3 行受影响)
*/

[liangCK]

[Quote=引用 5 楼 qingniao8 的回复:]
哇，这么快～～～～～太感谢了，time是有规律的，同一ID都是由小到大～
[/Quote]

直接MAX(time)-MIN(time)

[htl258_Tony]

有规律的好办。

[qingniao8]

谢了，结贴～