用户号 耗度 计费日期
000026-04 3 2008-12-01
000026-04 3 2009-01-01
000026-04 3 2009-02-01
000026-04 7 2009-03-01
000026-04 3 2009-04-01
000030-07 4 2008-12-01
000030-07 4 2009-01-01
000030-07 4 2009-02-01
000030-07 4 2009-04-01
000030-07 4 2009-05-01
000036-11 2 2008-12-01
000036-11 2 2009-01-01
000036-11 2 2009-02-01
000036-11 2 2009-03-01
000036-11 8 2009-04-01
000036-11 2 2009-05-01
000036-24 4 2008-12-01
000036-24 4 2009-01-01
000036-24 4 2009-02-01
000036-24 4 2009-06-01
000036-24 4 2009-07-01
000037-21 6 2009-02-01
000037-21 6 2009-03-01
000037-21 6 2009-05-01
000037-21 6 2009-06-01
000037-21 6 2009-07-01
000045-10 6 2008-12-01
000045-10 6 2009-01-01
000045-10 6 2009-02-01
000045-10 6 2009-04-01
000045-10 6 2009-05-01
000045-15 5 2008-12-01
000045-15 5 2009-03-01
000045-15 5 2009-04-01
000045-15 5 2009-05-01
000045-15 5 2009-06-01
000053-13 3 2009-02-01
000053-13 3 2009-03-01
000053-13 6 2009-04-01
000053-13 3 2009-05-01
000053-13 3 2009-06-01
000053-27 4 2009-01-01
000053-27 4 2009-02-01
000053-27 4 2009-04-01
000053-27 4 2009-05-01
000053-27 4 2009-06-01
---------------------------------------
如何查询 用户号 和 耗度 相同的并且连续大于等于3个月的记录.
应得到如下结果:
用户号 耗度 计费日期
000026-04 3 2008-12-01
000026-04 3 2009-01-01
000026-04 3 2009-02-01
000030-07 4 2008-12-01
000030-07 4 2009-01-01
000030-07 4 2009-02-01
000036-11 2 2008-12-01
000036-11 2 2009-01-01
000036-11 2 2009-02-01
000036-11 2 2009-03-01
000036-24 4 2008-12-01
000036-24 4 2009-01-01
000036-24 4 2009-02-01
000037-21 6 2009-05-01
000037-21 6 2009-06-01
000037-21 6 2009-07-01
000045-10 6 2008-12-01
000045-10 6 2009-01-01
000045-10 6 2009-02-01
000045-15 5 2009-03-01
000045-15 5 2009-04-01
000045-15 5 2009-05-01
000045-15 5 2009-06-01
000053-27 4 2009-04-01
000053-27 4 2009-05-01
000053-27 4 2009-06-01
请看清楚问题,测试成功了在贴上来,谢谢!
000026-04 3 2008-12-01
000026-04 3 2009-01-01
000026-04 3 2009-02-01
000026-04 7 2009-03-01
000026-04 3 2009-04-01
000030-07 4 2008-12-01
000030-07 4 2009-01-01
000030-07 4 2009-02-01
000030-07 4 2009-04-01
000030-07 4 2009-05-01
000036-11 2 2008-12-01
000036-11 2 2009-01-01
000036-11 2 2009-02-01
000036-11 2 2009-03-01
000036-11 8 2009-04-01
000036-11 2 2009-05-01
000036-24 4 2008-12-01
000036-24 4 2009-01-01
000036-24 4 2009-02-01
000036-24 4 2009-06-01
000036-24 4 2009-07-01
000037-21 6 2009-02-01
000037-21 6 2009-03-01
000037-21 6 2009-05-01
000037-21 6 2009-06-01
000037-21 6 2009-07-01
000045-10 6 2008-12-01
000045-10 6 2009-01-01
000045-10 6 2009-02-01
000045-10 6 2009-04-01
000045-10 6 2009-05-01
000045-15 5 2008-12-01
000045-15 5 2009-03-01
000045-15 5 2009-04-01
000045-15 5 2009-05-01
000045-15 5 2009-06-01
000053-13 3 2009-02-01
000053-13 3 2009-03-01
000053-13 6 2009-04-01
000053-13 3 2009-05-01
000053-13 3 2009-06-01
000053-27 4 2009-01-01
000053-27 4 2009-02-01
000053-27 4 2009-04-01
000053-27 4 2009-05-01
000053-27 4 2009-06-01
---------------------------------------
如何查询 用户号 和 耗度 相同的并且连续大于等于3个月的记录.
应得到如下结果:
用户号 耗度 计费日期
000026-04 3 2008-12-01
000026-04 3 2009-01-01
000026-04 3 2009-02-01
000030-07 4 2008-12-01
000030-07 4 2009-01-01
000030-07 4 2009-02-01
000036-11 2 2008-12-01
000036-11 2 2009-01-01
000036-11 2 2009-02-01
000036-11 2 2009-03-01
000036-24 4 2008-12-01
000036-24 4 2009-01-01
000036-24 4 2009-02-01
000037-21 6 2009-05-01
000037-21 6 2009-06-01
000037-21 6 2009-07-01
000045-10 6 2008-12-01
000045-10 6 2009-01-01
000045-10 6 2009-02-01
000045-15 5 2009-03-01
000045-15 5 2009-04-01
000045-15 5 2009-05-01
000045-15 5 2009-06-01
000053-27 4 2009-04-01
000053-27 4 2009-05-01
000053-27 4 2009-06-01
请看清楚问题,测试成功了在贴上来,谢谢!
---
declare @tb table (用户名 nvarchar(20),耗度 int,计费日期 datetime)
insert into @tb select '000026-04',3,'2008-12-01'
union all select '000026-04',3,'2009-01-01'
union all select '000026-04',3,'2009-02-01'
union all select '000026-04',7,'2009-03-01'
union all select '000026-04',3,'2009-04-01'
union all select '000030-07',4,'2008-12-01'
union all select '000030-07',4,'2009-01-01'
union all select '000030-07',4,'2009-02-01'
union all select '000030-07',4,'2009-04-01'
select *,时间间格=DATEDIFF(MONTH,计费日期,GETDATE()) into # from @tb order by 耗度
declare @str int
set @str=500;
update # set 时间间格=时间间格-@str,@str=@str-1
select b.用户名,b.耗度,b.计费日期 from
(select 用户名,耗度,时间间格 from # a
group by 用户名,耗度,时间间格 having(count(*)>2)) a join # b
on a.用户名=b.用户名 and a.耗度=b.耗度 and a.时间间格=b.时间间格
go
drop table #用户名 耗度 计费日期
-------------------- ----------- -----------------------
000026-04 3 2008-12-01 00:00:00.000
000026-04 3 2009-01-01 00:00:00.000
000026-04 3 2009-02-01 00:00:00.000
000030-07 4 2008-12-01 00:00:00.000
000030-07 4 2009-01-01 00:00:00.000
000030-07 4 2009-02-01 00:00:00.000(6 行受影响)
declare @t table ([UserId] varchar(9),[Used] int,[Date] datetime)
insert into @t
select '000026-04',3,'2008-12-01' union all
select '000026-04',3,'2009-01-01' union all
select '000026-04',3,'2009-02-01' union all
select '000026-04',7,'2009-03-01' union all
select '000026-04',3,'2009-04-01' union all
select '000030-07',4,'2008-12-01' union all
select '000030-07',4,'2009-01-01' union all
select '000030-07',4,'2009-02-01' union all
select '000030-07',4,'2009-04-01' union all
select '000030-07',4,'2009-05-01' union all
select '000036-11',2,'2008-12-01' union all
select '000036-11',2,'2009-01-01' union all
select '000036-11',2,'2009-02-01' union all
select '000036-11',2,'2009-03-01' union all
select '000036-11',8,'2009-04-01' union all
select '000036-11',2,'2009-05-01' union all
select '000036-24',4,'2008-12-01' union all
select '000036-24',4,'2009-01-01' union all
select '000036-24',4,'2009-02-01' union all
select '000036-24',4,'2009-06-01' union all
select '000036-24',4,'2009-07-01' union all
select '000037-21',6,'2009-02-01' union all
select '000037-21',6,'2009-03-01' union all
select '000037-21',6,'2009-05-01' union all
select '000037-21',6,'2009-06-01' union all
select '000037-21',6,'2009-07-01' union all
select '000045-10',6,'2008-12-01' union all
select '000045-10',6,'2009-01-01' union all
select '000045-10',6,'2009-02-01' union all
select '000045-10',6,'2009-04-01' union all
select '000045-10',6,'2009-05-01' union all
select '000045-15',5,'2008-12-01' union all
select '000045-15',5,'2009-03-01' union all
select '000045-15',5,'2009-04-01' union all
select '000045-15',5,'2009-05-01' union all
select '000045-15',5,'2009-06-01' union all
select '000053-13',3,'2009-02-01' union all
select '000053-13',3,'2009-03-01' union all
select '000053-13',6,'2009-04-01' union all
select '000053-13',3,'2009-05-01' union all
select '000053-13',3,'2009-06-01' union all
select '000053-27',4,'2009-01-01' union all
select '000053-27',4,'2009-02-01' union all
select '000053-27',4,'2009-04-01' union all
select '000053-27',4,'2009-05-01' union all
select '000053-27',4,'2009-06-01'
;with tt as
(
select
t1.*--,t2.date,t3.date
from
@t t1
inner join @t t2
on t1.userId = t2.userId
and t1.used = t2.used
and datediff(MM,t1.date,t2.date)=1
inner join @t t3
on t2.userId = t3.userId
and t2.used = t3.used
and datediff(MM,t2.date,t3.date)=1
union all
select
t.*
from tt inner join @t t
on tt.userId = t.userId
and tt.used = t.used
and datediff(MM,tt.date,t.date)=1
)
select distinct
userid [用户号]
,used [耗度]
,date [计费日期]
from tt
order by userid
go
create table tb(用户号 varchar(20), 耗度 int, 计费日期 varchar(10))
go
insert tb select'000026-04', 3, '2008-12-01 ' union all select
'000026-04', 3, '2009-01-01 ' union all select
'000026-04', 3, '2009-02-01 'union all select
'000026-04', 7, '2009-03-01 'union all select
'000026-04', 3, '2009-04-01 'union all select
'000030-07', 4, '2008-12-01 'union all select
'000030-07', 4, '2009-01-01 'union all select
'000030-07', 4, '2009-02-01 'union all select
'000030-07', 4, '2009-04-01 'union all select
'000030-07', 4, '2009-05-01 'union all select
'000036-11', 2, '2008-12-01 'union all select
'000036-11', 2, '2009-01-01 'union all select
'000036-11', 2, '2009-02-01 'union all select
'000036-11', 2, '2009-03-01' select * from tbalter table tb add id int identity(1,1)select * from tb a where exists(select 1 from tb where 用户号=a.用户号)select a.* from tb a where (select COUNT(1) from tb where 用户号=a.用户号 and a.id>id)/3=0/*
用户号 耗度 计费日期 id
000026-04 3 2008-12-01 1
000026-04 3 2009-01-01 2
000026-04 3 2009-02-01 3
000030-07 4 2008-12-01 6
000030-07 4 2009-01-01 7
000030-07 4 2009-02-01 8
000036-11 2 2008-12-01 11
000036-11 2 2009-01-01 12
000036-11 2 2009-02-01 13
*/昨天不是发了吗?
select a.* from tb a where (select COUNT(1) from tb where 用户号=a.用户号 and a.id>id)/3=0这两句就OK了!相当简单
declare @t table ([UserId] varchar(9),[Used] int,[Date] datetime)
insert into @t
select '000026-04',3,'2008-12-01' union all
select '000026-04',3,'2009-01-01' union all
select '000026-04',3,'2009-02-01' union all
select '000026-04',7,'2009-03-01' union all
select '000026-04',3,'2009-04-01' union all
select '000030-07',4,'2008-12-01' union all
select '000030-07',4,'2009-01-01' union all
select '000030-07',4,'2009-02-01' union all
select '000030-07',4,'2009-04-01' union all
select '000030-07',4,'2009-05-01' union all
select '000036-11',2,'2008-12-01' union all
select '000036-11',2,'2009-01-01' union all
select '000036-11',2,'2009-02-01' union all
select '000036-11',2,'2009-03-01' union all
select '000036-11',8,'2009-04-01' union all
select '000036-11',2,'2009-05-01' union all
select '000036-24',4,'2008-12-01' union all
select '000036-24',4,'2009-01-01' union all
select '000036-24',4,'2009-02-01' union all
select '000036-24',4,'2009-06-01' union all
select '000036-24',4,'2009-07-01' union all
select '000037-21',6,'2009-02-01' union all
select '000037-21',6,'2009-03-01' union all
select '000037-21',6,'2009-05-01' union all
select '000037-21',6,'2009-06-01' union all
select '000037-21',6,'2009-07-01' union all
select '000045-10',6,'2008-12-01' union all
select '000045-10',6,'2009-01-01' union all
select '000045-10',6,'2009-02-01' union all
select '000045-10',6,'2009-04-01' union all
select '000045-10',6,'2009-05-01' union all
select '000045-15',5,'2008-12-01' union all
select '000045-15',5,'2009-03-01' union all
select '000045-15',5,'2009-04-01' union all
select '000045-15',5,'2009-05-01' union all
select '000045-15',5,'2009-06-01' union all
select '000053-13',3,'2009-02-01' union all
select '000053-13',3,'2009-03-01' union all
select '000053-13',6,'2009-04-01' union all
select '000053-13',3,'2009-05-01' union all
select '000053-13',3,'2009-06-01' union all
select '000053-27',4,'2009-01-01' union all
select '000053-27',4,'2009-02-01' union all
select '000053-27',4,'2009-04-01' union all
select '000053-27',4,'2009-05-01' union all
select '000053-27',4,'2009-06-01'
select
t.UserId [用户号]
,t.Used [耗度]
,t.Date [计费日期]
from
(
select
t.UserId,t.Used,t.Date
,count(*)over(partition by t.UserId,t.Used,t.c) cnt
from
(select
*
,dateadd(MM,-(Dense_rank()Over(partition by t.UserId,t.Used order by t.Date)),t.Date) c
from @t t
) t
)t
where t.cnt >= 3用户号 耗度 计费日期
--------- ----------- -----------------------
000026-04 3 2008-12-01 00:00:00.000
000026-04 3 2009-01-01 00:00:00.000
000026-04 3 2009-02-01 00:00:00.000
000030-07 4 2008-12-01 00:00:00.000
000030-07 4 2009-01-01 00:00:00.000
000030-07 4 2009-02-01 00:00:00.000
000036-11 2 2008-12-01 00:00:00.000
000036-11 2 2009-01-01 00:00:00.000
000036-11 2 2009-02-01 00:00:00.000
000036-11 2 2009-03-01 00:00:00.000
000036-24 4 2008-12-01 00:00:00.000
000036-24 4 2009-01-01 00:00:00.000
000036-24 4 2009-02-01 00:00:00.000
000037-21 6 2009-05-01 00:00:00.000
000037-21 6 2009-06-01 00:00:00.000
000037-21 6 2009-07-01 00:00:00.000
000045-10 6 2008-12-01 00:00:00.000
000045-10 6 2009-01-01 00:00:00.000
000045-10 6 2009-02-01 00:00:00.000
000045-15 5 2009-03-01 00:00:00.000
000045-15 5 2009-04-01 00:00:00.000
000045-15 5 2009-05-01 00:00:00.000
000045-15 5 2009-06-01 00:00:00.000
000053-27 4 2009-04-01 00:00:00.000
000053-27 4 2009-05-01 00:00:00.000
000053-27 4 2009-06-01 00:00:00.000(26 行)