有表ACODE DATE
1 2009-10-01
1 2009-10-05
1 2009-10-09
1 2009-10-12
2 2009-10-11
2 2009-10-13
2 2009-10-20
3 2009-10-19
3 2009-10-21想得到的结果是 CODE相同的且DATE在5天以内连续3次以上的列出来。
1 2009-10-01
1 2009-10-05
1 2009-10-09
1 2009-10-12
2 2009-10-11
2 2009-10-13
2 2009-10-20
3 2009-10-19
3 2009-10-21想得到的结果是 CODE相同的且DATE在5天以内连续3次以上的列出来。
SELECT CODE FROM TB A
WHERE (SELECT COUNT(1) FROM TB WHERE A.CODE=CODE AND ([DATE] BETWEEN A.[DATE] AND A.[DATE]+5))>=3
)A
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([CODE] int,[DATE] datetime)
insert [TB]
select 1,'2009-10-01' union all
select 1,'2009-10-03' union all
select 1,'2009-10-04' union all
select 1,'2009-10-08' union all
select 1,'2009-10-09' union all
select 1,'2009-10-12' union all
select 2,'2009-10-11' union all
select 2,'2009-10-13' union all
select 2,'2009-10-20' union all
select 3,'2009-10-19' union all
select 3,'2009-10-21'select CODE,DATE from [TB] t
where datediff(dd,(select min(date) from TB where T.CODE=CODE),Date)<5
and (select count(*) from TB where t.code=code)>3
/*
CODE DATE
----------- ------------------------------------------------------
1 2009-10-01 00:00:00.000
1 2009-10-03 00:00:00.000
1 2009-10-04 00:00:00.000
1 2009-10-05 00:00:00.000(所影响的行数为 4 行)*/
drop table TB
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-10-26 18:47:18
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (CODE INT,DATE DATETIME)
INSERT INTO [tb]
SELECT 1,'2009-10-01' UNION ALL
SELECT 1,'2009-10-05' UNION ALL
SELECT 1,'2009-10-09' UNION ALL
SELECT 1,'2009-10-12' UNION ALL
SELECT 2,'2009-10-11' UNION ALL
SELECT 2,'2009-10-13' UNION ALL
SELECT 2,'2009-10-20' UNION ALL
SELECT 3,'2009-10-19' UNION ALL
SELECT 3,'2009-10-21' UNION ALL
select 1,'2009-10-04' ---增加一条测试数据;with
wang as(select row=row_number() over(order by getdate()),* from tb)select * from wang t
where exists(select 1 from wang where row=t.row+3 and date<=t.date+5 and code=t.code)
改为row=t.row+2
1
因为 1 ,的DATE,有连续3次,都是在间隔5天以内的。
row好像没这个字段吧? 实现不了
wang as(select row=row_number() over(order by getdate()),* from tb)
这句是怎么个意思啊?
DROP TABLE [tb]
go
CREATE TABLE [tb] (CODE INT,DATE DATETIME)
INSERT INTO [tb]
SELECT 1,'2009-10-01' UNION ALL
select 1,'2009-10-04' UNION ALL
SELECT 1,'2009-10-05' UNION ALL
SELECT 1,'2009-10-09' UNION ALL
SELECT 1,'2009-10-12' UNION ALL
SELECT 2,'2009-10-11' UNION ALL
SELECT 2,'2009-10-13' UNION ALL
SELECT 2,'2009-10-20' UNION ALL
SELECT 3,'2009-10-19' UNION ALL
SELECT 3,'2009-10-21'
--DROP TABLE #TSELECT CODE,DATE,
(SELECT COUNT(*) FROM TB WHERE CODE=T.CODE AND DATE<=T.DATE)AS NUM
INTO #T FROM TB T SELECT DISTINCT T.CODE FROM #T T,#T T1 WHERE T.CODE=T1.CODE AND T1.NUM=T.NUM+1 AND T.NUM>=3
(SELECT COUNT(*) FROM TB WHERE CODE=T.CODE AND DATE<=T.DATE)AS NUM
INTO FROM TB T )T,(SELECT CODE,DATE,
(SELECT COUNT(*) FROM TB WHERE CODE=T.CODE AND DATE<=T.DATE)AS NUM
INTO FROM TB T ) T1 WHERE T.CODE=T1.CODE AND T1.NUM=T.NUM+1 AND T.NUM>=3
--若这样的记录行有三条以上,就算此code符合要求!---是这样的吗?
----若是这样,其代码如下:
drop table a;create table a(code int, date datetime);
insert into a(code, date)
select
1,'2009-10-01' union all select
1,'2009-10-05' union all select
1,'2009-10-09' union all select
1,'2009-10-12' union all select
2,'2009-10-11' union all select
2,'2009-10-13' union all select
2,'2009-10-20' union all select
2,'2009-10-26' union all select
3,'2009-10-19' union all select
3,'2009-10-21';
select * from a;select t.code1, count(1)
from (
select t1.nums nums1, t1.code code1, t1.date date1,
t2.nums nums2, t2.code code2, t2.date date2
from
( select ROW_NUMBER() OVER(ORDER BY code, date) as nums, code, date from a ) t1
inner join
( select ROW_NUMBER() OVER(ORDER BY code, date) as nums, code, date from a ) t2
on t1.code=t2.code and t1.nums<t2.nums and abs(datediff(day,t2.date,t1.date))<=5 ) t
group by t.code1
having count(1)>=2;
1 2009-10-01
1 2009-10-05
1 2009-10-09
1 2009-10-12
2 2009-10-11
2 2009-10-13
2 2009-10-20
3 2009-10-19
3 2009-10-21 想得到的结果是 CODE相同的且DATE在5天以内连续3次以上的列出来。
---------------------------------------------------------------
drop table a;create table a(code int, date datetime);
insert into a(code, date)
select
1,'2009-10-01' union all select
1,'2009-10-05' union all select
1,'2009-10-09' union all select
1,'2009-10-12' union all select
2,'2009-10-11' union all select
2,'2009-10-13' union all select
2,'2009-10-20' union all select
2,'2009-10-26' union all select
3,'2009-10-19' union all select
3,'2009-10-21';select t1.nums nums1, t1.code code1, t1.date date1,
t2.nums nums2, t2.code code2, t2.date date2,
t3.nums nums3, t3.code code3, t3.date date3
from
( select ROW_NUMBER() OVER(ORDER BY code, date) as nums, code, date from a ) t1
left join
( select ROW_NUMBER() OVER(ORDER BY code, date) as nums, code, date from a ) t2
on t1.code=t2.code and t1.nums=t2.nums+1 and datediff(day,t2.date,t1.date)<=5
left join
( select ROW_NUMBER() OVER(ORDER BY code, date) as nums, code, date from a ) t3
on t2.code=t3.code and t2.nums=t3.nums+1 and datediff(day,t3.date,t2.date)<=5
where t1.date is not null and t2.date is not null and t3.date is not null
order by t1.nums--连续3次以上的:用2次left join;连续4次以上的:用3次left join;连续5次以上的:用4次left join;......--
--这样的方法比较死板,建议楼主超过连续5次以上的时候,用游标!--
select distinct t.code1
from (
select t1.nums nums1, t1.code code1, t1.date date1,
t2.nums nums2, t2.code code2, t2.date date2,
t3.nums nums3, t3.code code3, t3.date date3
from
( select ROW_NUMBER() OVER(ORDER BY code, date) as nums, code, date from a ) t1
left join
( select ROW_NUMBER() OVER(ORDER BY code, date) as nums, code, date from a ) t2
on t1.code=t2.code and t1.nums=t2.nums+1 and datediff(day,t2.date,t1.date)<=5
left join
( select ROW_NUMBER() OVER(ORDER BY code, date) as nums, code, date from a ) t3
on t2.code=t3.code and t2.nums=t3.nums+1 and datediff(day,t3.date,t2.date)<=5
where t1.date is not null and t2.date is not null and t3.date is not null
) t;
如果CODE有 好几万个, 连续3次以上, 也是用2次LEFT JOIN的吗?
drop table A
go
create table A(code int,date datetime)
insert into A
select 1,'2009-10-1'
union all select 1,'2009-10-05'
union all select 1,'2009-10-04'
union all select 1,'2009-10-02'
union all select 2,'2009-10-01'
union all select 2,'2009-10-02'
union all select 2,'2009-10-03'
union all select 3,'2009-10-05'
union all select 3,'2009-10-06'
select * from A where code in (
select code from A group by code having count(a.code) >= 3 and datediff(dw,min(date),max(date)) <= 5 )
FROM TB A
JOIN TB B
ON A.CODE = B.CODE AND A.DATE BETWEEN B.DATE - 5 AND B.DATE
GROUP BY A.CODE , A.DATE
HAVING COUNT(*) >=3
--(且每组Code存在三条及以上的这样的记录行?)---楼主就不能说明白点?
--这样的方法比较死板,建议楼主超过连续5次以上的时候,用游标!--