sql 分组排序

[srt266]

有表1:
type state wage
a 1 50
a 2 100
a 3 150
a 4 200
s 8 300
s 9 400
s 12 500
写到表2，如下：
type start end sum
a 1 4 500
s 8 9 700
s 12 12 500
写出SQL操作语句。
请问各位大虾怎么写？ 谢谢！

[jimoshatan]

CREATE TABLE #A (TYPE VARCHAR(10),STATE INT,WAGE INT)
INSERT INTO #A SELECT 'A',1,50
INSERT INTO #A SELECT 'A',2,100
INSERT INTO #A SELECT 'A',3,150
INSERT INTO #A SELECT 'A',4,200
INSERT INTO #A SELECT 'S',8,300
INSERT INTO #A SELECT 'S',9,400
INSERT INTO #A SELECT 'S',12,500

CREATE TABLE #B (TYPE VARCHAR(10),STATE INT,WAGE INT,SORT_NO INT)
INSERT INTO #B
SELECT  A.*, Sort_NO= (SELECT  count(1) FROM  #A B where B.TYPE = A.TYPE and B.STATE <=A.STATE) FROM #A A 

SELECT TYPE,MIN(STATE)STATE,MAX(STATE)[END],SUM(WAGE)SUM
FROM #B
GROUP BY TYPE,(STATE-SORT_NO)
DROP TABLE #A,#B

/**
A	1	4	500
S	8	9	700
S	12	12	500

[jimoshatan]

CREATE TABLE #A (TYPE VARCHAR(10),STATE INT,WAGE INT)
INSERT INTO #A SELECT 'A',1,50
INSERT INTO #A SELECT 'A',2,100
INSERT INTO #A SELECT 'A',3,150
INSERT INTO #A SELECT 'A',4,200
INSERT INTO #A SELECT 'S',8,300
INSERT INTO #A SELECT 'S',9,400
INSERT INTO #A SELECT 'S',12,500




SELECT IDENTITY(INT,1,1)SORT_NO,* INTO #C FROM #A ORDER BY TYPE,STATE

SELECT TYPE,MIN(STATE)STATE,MAX(STATE)[END],SUM(WAGE)SUM
FROM #C
GROUP BY TYPE,(STATE-SORT_NO)

DROP TABLE #A,#C

[szflower]

select type,min(state) as start,max(state) as end,sum(wage) as sum
group by type

[时光瞄]

select type,min(state) as start,max(state) as end,sum(wage) from tb group by type

[srt266]

state 需要连续 谢谢两位的回复