比如表A 有两个字段 日期和收入,数据如下:
日期 收入
10/1 100
10/2 100
10/3 110
10/4 110
10/5 100
...要求按收入生成结果集10/1 - 10/2 100
10/3 - 10/4 110
10/5 - 10/5 100
...
这个分组还有按日期排序
日期 收入
10/1 100
10/2 100
10/3 110
10/4 110
10/5 100
...要求按收入生成结果集10/1 - 10/2 100
10/3 - 10/4 110
10/5 - 10/5 100
...
这个分组还有按日期排序
declare @table table (日期 varchar(4),收入 int)
insert into @table
select '10/1',100 union all
select '10/2',100 union all
select '10/3',110 union all
select '10/4',110 union all
select '10/5',100;WITH maco
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY 日期 ) AS rowid ,
*
FROM @table
)
SELECT a.日期 + '--' + ISNULL(b.日期, a.日期) AS 日期 ,
a.收入
FROM maco a
LEFT JOIN maco b ON a.rowid = b.rowid - 1
WHERE a.收入 = b.收入
OR b.收入 IS NULL
/*
日期 收入
---------- -----------
10/1--10/2 100
10/3--10/4 110
10/5--10/5 100
*/
declare @tablea table (pensonid int,datatime datetime,price int)
insert into @tablea
select 1,'2009-03-01',200 union all
select 1,'2009-04-01',200 union all
select 1,'2009-05-01',240 union all
select 1,'2009-07-01',260 union all
select 1,'2009-08-01',270 union all
select 2,'2009-01-01',200 union all
select 2,'2009-02-01',200 union all
select 2,'2009-05-01',240 union all
select 2,'2009-06-01',240 union all
select 2,'2009-07-01',270 union all
select 2,'2009-12-01',240 SELECT * FROM @tablea
/*
pensonid datatime price
----------- ----------------------- -----------
1 2009-03-01 00:00:00.000 200
1 2009-04-01 00:00:00.000 200
1 2009-05-01 00:00:00.000 240
1 2009-07-01 00:00:00.000 260
1 2009-08-01 00:00:00.000 270
2 2009-01-01 00:00:00.000 200
2 2009-02-01 00:00:00.000 200
2 2009-05-01 00:00:00.000 240
2 2009-06-01 00:00:00.000 240
2 2009-07-01 00:00:00.000 270
2 2009-12-01 00:00:00.000 240
*/declare @tmp table(pensonid int,datatime datetime,price int,flag int);
insert @tmp (pensonid,datatime,price)
select * from @tablea order by pensonid,datatime;declare @flag int;
declare @pensonid int,@price int;select @flag = 0;update @tmp set
@flag = case when @pensonid=pensonid and @price =price then @flag else @flag+1 end,
flag = @flag,
@pensonid=pensonid,
@price=price;select
pensonid,price,
min(datatime) as startdate,
dateadd(month,1,max(datatime)) as enddate
from @tmp
group by pensonid,flag,price
/*
pensonid price startdate enddate
----------- ----------- ----------------------- -----------------------
1 200 2009-03-01 00:00:00.000 2009-05-01 00:00:00.000
1 240 2009-05-01 00:00:00.000 2009-06-01 00:00:00.000
1 260 2009-07-01 00:00:00.000 2009-08-01 00:00:00.000
1 270 2009-08-01 00:00:00.000 2009-09-01 00:00:00.000
2 200 2009-01-01 00:00:00.000 2009-03-01 00:00:00.000
2 240 2009-05-01 00:00:00.000 2009-07-01 00:00:00.000
2 270 2009-07-01 00:00:00.000 2009-08-01 00:00:00.000
2 240 2009-12-01 00:00:00.000 2010-01-01 00:00:00.000
*/ps:1#的不适用于所有情况
你用这个例子改改就可以了。
insert tb select '2010-10-1', 100
insert tb select '2010-10-2', 100
insert tb select '2010-10-3', 110
insert tb select '2010-10-4', 110
insert tb select '2010-10-5', 100with t1 as
(
select id1=row_number() over(order by getdate()),
id2=rank() over(order by 收入),
id3=row_number() over(partition by 收入 order by getdate()),* from tb
)
,t2 as
(
select sd=min(日期),ed=max(日期),收入 from t1 group by 收入,id2,id1-id3
)
select 日期=convert(varchar(5),sd,110)+' -- '+convert(varchar(5),ed,110),收入 from t2 order by sd
/*
日期 收入
-------------- -----------
10-01 -- 10-02 100
10-03 -- 10-04 110
10-05 -- 10-05 100(3 行受影响)
*/
declare @table table (日期 varchar(10),收入 int)
insert into @table
select '2011-10-01',100 union all
select '2011-10-02',100 union ALL
select '2011-10-03',100 union all
select '2011-10-04',110 union all
select '2011-10-05',110 union all
select '2011-10-06',100declare @tmp table(日期 datetime,收入 int,flag int);
insert @tmp (日期,收入) select * from @table order by 日期;
declare @flag int;declare @price int;select @flag = 0;
update @tmp set
@flag = case WHEN @price =收入 then @flag else @flag+1 end,
flag = @flag,@price=收入;
select
CONVERT(VARCHAR(10),min(日期),120) as 开始时间,
CONVERT(VARCHAR(10),max(日期),120) as 结束时间,收入
from @tmp group by flag,收入
ORDER BY min(日期)/*
开始时间 结束时间 收入
---------- ---------- -----------
2011-10-01 2011-10-03 100
2011-10-04 2011-10-05 110
2011-10-06 2011-10-06 100
*/
帮你改好了
想法不错
INSERT @TB
SELECT '10/1', 100 UNION ALL
SELECT '10/2', 100 UNION ALL
SELECT '10/3', 100 UNION ALL
SELECT '10/4', 110 UNION ALL
SELECT '10/5', 110 UNION ALL
SELECT '10/6', 100SELECT A.DAT+'-'+ISNULL(MIN(B.DAT), A.DAT) AS DAT,MAX(A.INC) AS INC
FROM (
SELECT *
FROM @TB T
WHERE NOT EXISTS(SELECT * FROM @TB WHERE INC=T.INC AND ID=T.ID-1)
) A LEFT JOIN
(SELECT *
FROM @TB T
WHERE NOT EXISTS(SELECT * FROM @TB WHERE INC=T.INC AND ID=T.ID+1)
) B ON A.DAT<B.DAT AND A.INC=B.INC
GROUP BY A.DAT/*
DAT INC
----------- -----------
10/1-10/3 100
10/4-10/5 110
10/6-10/6 100
*/
insert tb select '2010-10-1', 100
insert tb select '2010-10-2', 100
insert tb select '2010-10-3', 110
insert tb select '2010-10-4', 110
insert tb select '2010-10-5', 100with t1 as
(
select id1=row_number() over(order by getdate()),
id2=row_number() over(partition by 收入 order by getdate()),* from tb
)
,t2 as
(
select sd=min(日期),ed=max(日期),收入 from t1 group by 收入,id1-id2
)
select 日期=convert(varchar(5),sd,110)+' -- '+convert(varchar(5),ed,110),收入 from t2 order by sd
/*
日期 收入
-------------- -----------
10-01 -- 10-02 100
10-03 -- 10-04 110
10-05 -- 10-05 100(3 行受影响)
*/