合并
有一个表,
姓名 开始年 结束年 金额
1 2010 2011 20
1 2012 2014 10
2 2011 2011 10
2 2012 2012 10
3 2009 2010 10
3 2012 2013 10
想查询得到
1 2010 2014 30
2 2011 2012 20
3 2009 2010 10
3 2012 2013 10
[解决办法]
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([姓名] INT,[开始年] INT,[结束年] INT,[金额] INT)INSERT [tb]SELECT 1,2010,2011,20 UNION ALLSELECT 1,2012,2014,10 UNION ALLSELECT 2,2011,2011,10 UNION ALLSELECT 2,2012,2012,10 UNION ALLSELECT 3,2009,2010,10 UNION ALLSELECT 3,2012,2013,10--------------开始查询--------------------------;WITH t AS(SELECT *,isnull((select [开始年] FROM tb WHERE [姓名]=t.[姓名] AND ([开始年]=t.[结束年]+1 )),[开始年]) AS name FROM [tb] AS t)SELECT [姓名],MIN([开始年]),MAX([结束年]),SUM([金额])FROM t GROUP BY [姓名],nameORDER BY [姓名]----------------结果----------------------------/* 姓名 (无列名) (无列名) (无列名)1 2010 2014 302 2011 2012 203 2009 2010 103 2012 2013 10*/
[解决办法]
--测试数据
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([姓名] INT,[开始年] INT,[结束年] INT,[金额] INT)
INSERT [tb]
SELECT 1,2010,2011,20 UNION ALL
SELECT 1,2012,2014,10 UNION ALL
select 1,2015,2015,10 union all
select 1,2017,2017,10 union all
select 1,2018,2019,10 union all
select 1,2022,2022,10 union all
SELECT 2,2011,2011,10 UNION ALL
SELECT 2,2012,2012,10 UNION ALL
SELECT 3,2009,2010,10 UNION ALL
SELECT 3,2012,2013,10
GO
with a as(
select
*
,case when not exists(select 1 from tb t2 where 姓名=t1.姓名 and t1.开始年=t2.结束年+1) then 1 else 0 end IsHeader
,case when not exists(select 1 from tb t2 where 姓名=t1.姓名 and t1.结束年=t2.开始年-1) then 1 else 0 end IsRooter
,ROW_NUMBER()over(PARTITION by 姓名 order by 姓名,开始年) 姓名RN
from tb t1
),b as(
select
a1.*
,(select (SUM(IsHeader+IsRooter)+1)/2
from a a2
where a2.姓名=a1.姓名 and a2.姓名RN<=a1.姓名RN) stage
from a a1
)
select
姓名,MIN(开始年) 开始年,MAX(结束年) 结束年,SUM(金额) 金额
from b
group by 姓名,stage
order by 姓名,stage
--写这段费死牛劲了...