求一高难度SQL语句.(200)

有一个表如下:Employeeid CurDate MatchCount 1 2010-04-01 0 1 2010-04-02 1 1 2010-04-03 0 1 2010-04-04 0 1 2010-04-05 0 1 2010-04-06 3 1 2010-04-07 0 1 2010-04-08 4 2 2010-04-01 0 2 2010-04-02 0 2 2010-04-03 0 2 2010-04-04 0 2 2010-04-05 3 2 2010-04-06 5 2 2010-04-07 0 2 2010-04-08 1 10 2010-04-01 2 10 2010-04-02 1 10 2010-04-03 3 10 2010-04-04 0 10 2010-04-05 3 10 2010-04-06 0 10 2010-04-07 0 10 2010-04-08 0要求是,查询出所有连续三天以上(含三天)MatchCount=0的所有Employeeid值.如Employeeid为1的2010-04-03至2010-04-05,Employeeid为2的2010-04-01至2010-04-04,Employeeid为10的2010-04-06至2010-04-08,查询出的记录最好能像下面显示Employeeid StartDate EndDate1 2010-04-03 2010-04-052 2010-04-01 2010-04-0410 2010-04-06 2010-04-08

我提一个思路（针对Oracle数据库）：1、先用 Select Employeeid, CurDate From ta Where MatchCount = 0 Order By Employeeid, CurDate 语句把结果放到游标里；2、打开游标，循环处理上述结果集，符合条件的插入临时表；3、检索临时表的记录...

你的方法我也想过,但感觉速度有点慢.

select Employeeid, Min(CurDate) StartDate, Max(CurDate) EndDatefrom tablegroup by Employeeidwhere条件继续加上以满足其他条件。

不过有个问题，如果是这样的数据，得出什么结果： 2 2010-04-01 0 2 2010-04-02 0 2 2010-04-03 0 2 2010-04-04 0 2 2010-04-05 3 2 2010-04-06 5 2 2010-04-07 0 2 2010-04-08 0 2 2010-04-09 0

to xianjun你的方法行不通,我要的是查询出连续三天matchcount=0的记录

方法应该是可行的，只是写出来效率不知如何where条件中，首先把MatchCount<>0的过滤掉然后把孤立的CurDate过滤掉再把只有连续两个记录的CurDate过滤掉最后可能剩下的记录是这样的： 2 2010-04-01 0 2 2010-04-02 0 2 2010-04-03 0 2 2010-04-04 0 2 2010-04-07 0 2 2010-04-08 0 2 2010-04-09 0 这也就是上面问你的问题了，这种情况你取什么值？

--创建一个临时表create table TMPYY( EMPLOYEEID NUMBER, CURDATE DATE, MATCHCOUNT NUMBER, SCDATE DATE);把数据放到表TMPYY --找到最近的日期update TMPYY tmp set tmp.scdate = (select min(curdate) from TMPYY tt where tt.employeeid = tmp.employeeid and tt.matchcount = 0 and tt.curdate < tmp.curdate and tt.curdate > (select max(curdate) from TMPYY tt where tt.employeeid = tmp.employeeid and tt.matchcount > 0 and tt.curdate < tmp.curdate)) where tmp.matchcount = 0; --删除多余数据 delete TMPYY tmp where tmp.matchcount<>0 or tmp.scdate is null; --返回数据 select tmp.employeeid, tmp.scdate StartDate, max(tmp.curdate) EndDate from TMPYY tmp where tmp.curdate-tmp.scdate+1>=3 group by tmp.employeeid, tmp.scdate;

TO power255你的方法不行啊,结果是全部记录的scdate=null

试试下面的存储过程：PROCEDURE demo_rang_date IS v_Employeeid Integer; tmpEmployeeid Integer; v_CurDate Date; tmpCurDate Date; CURSOR rang_date IS Select Employeeid, CurDate From ta Where MatchCount = 0 Order By Employeeid, CurDate;BEGIN OPEN rang_date; FETCH rang_date INTO v_Employeeid, v_CurDate; -- 得到第一条记录的 Employeeid 与 CurDate if rang_date%FOUND then Delete From tempTA; Insert Into tempTA (Employeeid, StartDate) Select Employeeid, Min(CurDate) From ta Where MatchCount = 0 Group By Employeeid Order By Employeeid; tmpEmployeeid := v_Employeeid; tmpCurDate := v_CurDate; end if; LOOP -- 循环处理(从第二条记录开始) FETCH rang_date INTO v_Employeeid, v_CurDate; EXIT WHEN rang_date%NOTFOUND; IF (tmpEmployeeid = v_Employeeid) and (v_CurDate - tmpCurDate = 1) THEN Update tempTA Set EndDate = v_CurDate Where Employeeid = v_Employeeid; tmpEmployeeid := v_Employeeid; tmpCurDate := v_CurDate; ELSE Update tempTA Set StartDate = v_CurDate Where Employeeid = v_Employeeid and StartDate = tmpCurDate; tmpEmployeeid := v_Employeeid; tmpCurDate := v_CurDate; END IF; END LOOP; CLOSE rang_date;END demo_rang_date;完成后执行 Select * From tempTA 应该可以得到结果，我没有测试及优化。

可以加上 Where enddate - startdate >= 2 条件。

奇怪，之前我发的只是Employeeid=2的数据没有取出来，怎么可以都为空呢--创建一个临时表create table TMPYY( EMPLOYEEID NUMBER, CURDATE DATE, MATCHCOUNT NUMBER, SCDATE DATE);--放数据insert into TMPYY values (1,to_date('2010-04-01', 'yyyy-mm-dd'),0,null);insert into TMPYY values (1,to_date('2010-04-02', 'yyyy-mm-dd'),1,null);insert into TMPYY values (1,to_date('2010-04-03', 'yyyy-mm-dd'),0,null);insert into TMPYY values (1,to_date('2010-04-04', 'yyyy-mm-dd'),0,null);insert into TMPYY values (1,to_date('2010-04-05', 'yyyy-mm-dd'),0,null);insert into TMPYY values (1,to_date('2010-04-06', 'yyyy-mm-dd'),3,null);insert into TMPYY values (1,to_date('2010-04-07', 'yyyy-mm-dd'),0,null);insert into TMPYY values (1,to_date('2010-04-08', 'yyyy-mm-dd'),4,null);insert into TMPYY values (2,to_date('2010-04-01', 'yyyy-mm-dd'),0,null);insert into TMPYY values (2,to_date('2010-04-02', 'yyyy-mm-dd'),0,null);insert into TMPYY values (2,to_date('2010-04-03', 'yyyy-mm-dd'),0,null);insert into TMPYY values (2,to_date('2010-04-04', 'yyyy-mm-dd'),0,null);insert into TMPYY values (2,to_date('2010-04-05', 'yyyy-mm-dd'),3,null);insert into TMPYY values (2,to_date('2010-04-06', 'yyyy-mm-dd'),5,null);insert into TMPYY values (2,to_date('2010-04-07', 'yyyy-mm-dd'),0,null);insert into TMPYY values (2,to_date('2010-04-08', 'yyyy-mm-dd'),1,null);insert into TMPYY values (10,to_date('2010-04-01', 'yyyy-mm-dd'),2,null);insert into TMPYY values (10,to_date('2010-04-02', 'yyyy-mm-dd'),1,null);insert into TMPYY values (10,to_date('2010-04-03', 'yyyy-mm-dd'),3,null);insert into TMPYY values (10,to_date('2010-04-04', 'yyyy-mm-dd'),0,null);insert into TMPYY values (10,to_date('2010-04-05', 'yyyy-mm-dd'),3,null);insert into TMPYY values (10,to_date('2010-04-06', 'yyyy-mm-dd'),0,null);insert into TMPYY values (10,to_date('2010-04-07', 'yyyy-mm-dd'),0,null);insert into TMPYY values (10,to_date('2010-04-08', 'yyyy-mm-dd'),0,null); --找到最近的日期update TMPYY tmp set tmp.scdate = (select min(curdate) from TMPYY tt where tt.employeeid = tmp.employeeid and tt.matchcount = 0 and tt.curdate < tmp.curdate and tt.curdate > nvl((select max(curdate) from TMPYY kk where kk.employeeid = tmp.employeeid and kk.matchcount > 0 and kk.curdate < tmp.curdate), to_date('2010-01-01', 'yyyy-mm-dd'))) where tmp.matchcount = 0; select * from TMPYY; 结果 EMPLOYEEID CURDATE MATCHCOUNT SCDATE1 2010-04-01 0 1 2010-04-02 1 1 2010-04-03 0 1 2010-04-04 0 2010-04-031 2010-04-05 0 2010-04-031 2010-04-06 3 1 2010-04-07 0 1 2010-04-08 4 2 2010-04-01 0 2 2010-04-02 0 2010-04-012 2010-04-03 0 2010-04-012 2010-04-04 0 2010-04-012 2010-04-05 3 2 2010-04-06 5 2 2010-04-07 0 2 2010-04-08 1 10 2010-04-01 2 10 2010-04-02 1 10 2010-04-03 3 10 2010-04-04 0 10 2010-04-05 3 10 2010-04-06 0 10 2010-04-07 0 2010-04-0610 2010-04-08 0 2010-04-06 --删除多余数据 delete TMPYY tmp where tmp.matchcount<>0 or tmp.scdate is null; select * from TMPYY;结果 1 2010-04-04 0 2010-04-031 2010-04-05 0 2010-04-032 2010-04-02 0 2010-04-012 2010-04-03 0 2010-04-012 2010-04-04 0 2010-04-0110 2010-04-07 0 2010-04-0610 2010-04-08 0 2010-04-06 --返回数据select tmp.employeeid, tmp.scdate StartDate, max(tmp.curdate) EndDate from TMPYY tmp where tmp.curdate - tmp.scdate + 1 >= 3 group by tmp.employeeid, tmp.scdate order by tmp.employeeid;结果1 2010-04-03 2010-04-052 2010-04-01 2010-04-0410 2010-04-06 2010-04-08

to power255我用的是SQL SERVER,你的语句有些执行不了.

power255的方法可能有点儿问题：如果插入如下数据insert into TA values (1,to_date('2010-04-01', 'yyyy-mm-dd'),2,null);insert into TA values (1,to_date('2010-04-02', 'yyyy-mm-dd'),1,null);insert into TA values (1,to_date('2010-04-03', 'yyyy-mm-dd'),0,null);insert into TA values (1,to_date('2010-04-04', 'yyyy-mm-dd'),0,null);insert into TA values (1,to_date('2010-04-05', 'yyyy-mm-dd'),0,null);insert into TA values (1,to_date('2010-04-05', 'yyyy-mm-dd'),3,null);insert into TA values (1,to_date('2010-04-06', 'yyyy-mm-dd'),0,null);insert into TA values (1,to_date('2010-04-07', 'yyyy-mm-dd'),0,null);insert into TA values (1,to_date('2010-04-08', 'yyyy-mm-dd'),4,null);结果是否正常呢？我测试了一下，Oracle中用我的存储过程执行的时间不会太长。

to:szhcracker你是指同一天有多条记录吗？abcls的数据没有这种情况的，所以我也没有处理它。如果有就先把insert into TA values (1,to_date('2010-04-05', 'yyyy-mm-dd'),0,null);这种数据删除就可以了,反正都是把数据先放在临时表的。to;abclsto_date('2010-01-01', 'yyyy-mm-dd')改为CONVERT(DATETIME,'2010-01-01') 应该就可以了。SQL SERVER我没有装，没有办法测试

toower255那么针对 xianjun 提出的如下情况怎样处理？ 2 2010-04-01 0 2 2010-04-02 0 2 2010-04-03 0 2 2010-04-04 0 2 2010-04-07 0 2 2010-04-08 0 2 2010-04-09 0 针对这种情况，我改了一下我的存储过程，如下所示：CREATE OR REPLACE PROCEDURE DEMO_RANG_DATE AS v_Employeeid Integer; tmpEmployeeid Integer; I Integer; v_CurDate Date; tmpCurDate Date; CURSOR rang_date IS Select Employeeid, CurDate From ta Where MatchCount = 0 Order By Employeeid, CurDate;BEGIN OPEN rang_date; FETCH rang_date INTO v_Employeeid, v_CurDate; -- 得到第一条记录的 Employeeid 与 CurDate if rang_date%FOUND then I := 0; Delete From tempTA; Insert Into tempTA (Employeeid, StartDate) Values (v_Employeeid, v_CurDate); tmpEmployeeid := v_Employeeid; tmpCurDate := v_CurDate; end if; LOOP -- 循环处理(从第二条记录开始) FETCH rang_date INTO v_Employeeid, v_CurDate; EXIT WHEN rang_date%NOTFOUND; IF (tmpEmployeeid = v_Employeeid) and (v_CurDate - tmpCurDate = 1) THEN Update tempTA Set EndDate = v_CurDate Where Employeeid = v_Employeeid and StartDate + I = tmpCurDate; tmpEmployeeid := v_Employeeid; tmpCurDate := v_CurDate; I := I + 1; ELSE Insert Into tempTA (Employeeid, StartDate) Values (v_Employeeid, v_CurDate); tmpEmployeeid := v_Employeeid; tmpCurDate := v_CurDate; I := 0; END IF; END LOOP; CLOSE rang_date; Delete From tempTA Where EndDate is null;END DEMO_RANG_DATE;其中临时表tempTA只有三个字段：Employeeid Integer； StartDate Date 以及 EndDate Date

to power255,你的Update代码在SQL SERVER语根本无法执行.

那是Oracle环境的，估计SQL Server是不支持的，还有 power255 的方法是存在漏洞的，个人认为类似这种问题，特别要注意数据集的各种情况，感觉还是要用存储过程来处理，这样逻辑判断清晰。祝楼主好运。

power255的方法对于SQL SERVER根本没法执行,scdate全部为null

根本不用作存储过程Select a.Employeeid, a.CurDate as StartDate, c.CurDate as EndDate From (Select Employeeid, CurDate From 表 Where MatchCount=0)aLeft Outer Join (Select Employeeid, CurDate From 表 Where MatchCount=0)b On (DateDiff(Day, a.CurDate, b.CurDate)=1) And (a.Employeeid=b.Employeeid)Left Outer Join (Select Employeeid, CurDate From 表 Where MatchCount=0)c On (DateDiff(Day, a.CurDate, c.CurDate)=2) And (a.Employeeid=c.Employeeid)Where (b.CurDate is not null) and (c.CurDate is not null)