CREATE TABLE TB([ID] INT, [num] INT, SEQ INT IDENTITY(1,1)) INSERT TB SELECT 1, 10 UNION ALL SELECT 1, 10 UNION ALL SELECT 2, 5 UNION ALL SELECT 2, 5 UNION ALL SELECT 1, 6 UNION ALL SELECT 3, 6;WITH CTE AS ( SELECT *,GRP=SEQ FROM TB WHERE SEQ=1 UNION ALL SELECT TB.*,CASE WHEN CTE.ID=TB.ID THEN GRP ELSE GRP+1 END FROM TB,CTE WHERE TB.SEQ=CTE.SEQ+1 )SELECT ID,SUM(num) AS num FROM CTE GROUP BY GRP,IDDROP TABLE TB /* ID num ----------- ----------- 1 20 1 6 2 10 3 6 */
select id,sum(num) as num from test group by id order by id
4楼的,我例子举得不好,3楼的例子举得很好,跟num大小没关系 只跟ID是否连续有关
--加个ORDER ;WITH CTE AS ( SELECT *,GRP=SEQ FROM TB WHERE SEQ=1 UNION ALL SELECT TB.*,CASE WHEN CTE.ID=TB.ID THEN GRP ELSE GRP+1 END FROM TB,CTE WHERE TB.SEQ=CTE.SEQ+1 )SELECT ID,SUM(num) AS num FROM CTE GROUP BY GRP,ID ORDER BY GRP
--一种解法: if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[num] int) insert [tb] select 1,10 union all select 1,25 union all select 2,8 union all select 2,5 union all select 1,6 union all select 3,6 go --select * from [tb]select *,grp=0 into # from tbdeclare @id int,@grp int update # set @grp=case when id=@id then @grp else isnull(@grp+1,0) end ,@id=id ,grp=@grpselect id,num=sum(num) from # group by id,grp /* id num ----------- ----------- 1 35 2 13 1 6 3 6(4 行受影响) */ drop table #
num 是随便举例的, 表里数据也不只这么几条,但ID只有1,2,3
if object_id('[tb]') is not null drop table [tb] create table [tb]([ID] int,[num] int) insert [tb] select 1,10 union all select 1,10 union all select 2,5 union all select 2,5 union all select 1,6 union all select 3,6select tid=identity(int,1,1),* into newtb from tb select ID,num=sum(num) from (select *,place=case when ID=(select ID from newtb where Tid=t.Tid-1) then tid-1 else tid end from newtb t) g group by place,id order by ID /* ID num ----------- ----------- 1 20 1 6 2 10 3 6(所影响的行数为 4 行) */
假如数据库表为test,内容如下:ID num 1 10 1 10 2 5 2 5 1 6 3 6执行SQL后能得到 1 20 2 10 1 6 3 6 请问能实现吗?select id,sum(num) from test A where exists(select 1 from test where id=a.id and num=num )
select id,num1 as num
from
(select id, num, sum(num) as num1
from table
group by id,num
) A
order by num1 desc
1 10
1 10
2 5
2 5
1 6
3 6 执行SQL后能得到
1 20
2 10
1 6
3 6
这个例子不够典型,2条1 10加在一起,是因为id-num均相同吗?还是我说的仅仅因为顺序挨在一起?
如果这样,就比较典型:
ID num
1 10
1 25
2 8
2 5
1 6
3 6 执行SQL后能得到
1 35
2 13
1 6
3 6
sz_haitao,理解正确,只跟ID有关
INSERT TB
SELECT 1, 10 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 1, 6 UNION ALL
SELECT 3, 6;WITH CTE AS
(
SELECT *,GRP=SEQ FROM TB WHERE SEQ=1
UNION ALL
SELECT TB.*,CASE WHEN CTE.ID=TB.ID THEN GRP ELSE GRP+1 END FROM TB,CTE WHERE TB.SEQ=CTE.SEQ+1
)SELECT ID,SUM(num) AS num
FROM CTE
GROUP BY GRP,IDDROP TABLE TB
/*
ID num
----------- -----------
1 20
1 6
2 10
3 6
*/
只跟ID是否连续有关
;WITH CTE AS
(
SELECT *,GRP=SEQ FROM TB WHERE SEQ=1
UNION ALL
SELECT TB.*,CASE WHEN CTE.ID=TB.ID THEN GRP ELSE GRP+1 END FROM TB,CTE WHERE TB.SEQ=CTE.SEQ+1
)SELECT ID,SUM(num) AS num
FROM CTE
GROUP BY GRP,ID
ORDER BY GRP
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[num] int)
insert [tb]
select 1,10 union all
select 1,25 union all
select 2,8 union all
select 2,5 union all
select 1,6 union all
select 3,6
go
--select * from [tb]select *,grp=0 into # from tbdeclare @id int,@grp int
update #
set @grp=case when id=@id then @grp else isnull(@grp+1,0) end
,@id=id
,grp=@grpselect id,num=sum(num)
from #
group by id,grp
/*
id num
----------- -----------
1 35
2 13
1 6
3 6(4 行受影响)
*/
drop table #
表里数据也不只这么几条,但ID只有1,2,3
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] int,[num] int)
insert [tb]
select 1,10 union all
select 1,10 union all
select 2,5 union all
select 2,5 union all
select 1,6 union all
select 3,6select tid=identity(int,1,1),* into newtb from tb
select ID,num=sum(num) from (select *,place=case when ID=(select ID from newtb where Tid=t.Tid-1) then tid-1 else tid end
from newtb t) g group by place,id order by ID
/*
ID num
----------- -----------
1 20
1 6
2 10
3 6(所影响的行数为 4 行)
*/
1 10
1 10
2 5
2 5
1 6
3 6执行SQL后能得到
1 20
2 10
1 6
3 6
请问能实现吗?select id,sum(num) from test A where exists(select 1 from test where id=a.id and num=num )
,SUM(num)
from @table
group by ID,num