declare @t table (c1 char(1),c2 int,c3 int,c4 int)
insert into @t
select 'a',7,1,100 union
select 'a',7,2,80 union
select 'a',7,3,100 union
select 'a',7,4,100 union
select 'a',7,5,100 union
select 'a',7,6,100 union
select 'a',7,7,90 union
select 'a',7,8,100 union
select 'a',7,9,100 union
select 'a',7,10,100 union
select 'a',7,11,100 union
select 'a',7,12,100 union
select 'a',7,13,100 ;with s as
(select c1,c2,c3,case when c4=100 then 1 else 0 end as times,
cast(case when c4=100 then cast(c3 as varchar(2)) else '' end as varchar(100)) as ds
from @t where c3=1
union all
select b.c1,b.c2,b.c3,case when c4=100 then a.times+1 else 0 end as times,
cast(case when c4=100 then a.ds+'/'+cast(b.c3 as varchar(2)) else '' end as varchar(100)) as ds
from s a
inner join @t b on a.c1=b.c1 and a.c2=b.c2 and a.c3+1=b.c3
)
select c1,c2,100,stuff(ds,1,1,'') from s where times>5/*
c1 c2
---- ----------- ----------- -----------------------------
a 7 100 8/9/10/11/12/13(1 行受影响)
*/
解决方案 »
- 虚拟用户数据
- 这样SQL语句问题,从来没有遇到过!!!!
- 如果设定为Windows身份验证,其他计算机是否无法通过网络连接该服务器了?
- sql中数据量大的时候怎么处理?
- A机子windows xp 安装sql2000,delphi编程,程序能让B机使用并调用A数据库,请指导一下。
- 与top相反的是那个关键字???也就是说我在一个数据库表中排好后,选出其中后48条记录怎么实现??
- 请教一条SQL语句(在线等)
- sql中如何判断一个money数为正数(不是整数)
- 系统存储过程的结果集如何才能拿来用?急!
- 大家来帮我
- 大虾来帮忙啊,sql 问题
- 表的外键关系 alter table person add constraint fk_id(p_id) foreign key references permission (q_id)
CREATE TABLE #([name] varchar(10),[month] varchar(10),id int,value int)
INSERT INTO #
SELECT 'a','7',1,100 UNION ALL
SELECT 'a','7',2,80 UNION ALL
SELECT 'a','7',3,100 UNION ALL
SELECT 'a','7',4,100 UNION ALL
SELECT 'a','7',5,100 UNION ALL
SELECT 'a','7',6,100 UNION ALL
SELECT 'a','7',7,90 UNION ALL
SELECT 'a','7',8,100 UNION ALL
SELECT 'a','7',9,100 UNION ALL
SELECT 'a','7',10,100 UNION ALL
SELECT 'a','7',11,100 UNION ALL
SELECT 'a','7',12,100 UNION ALL
SELECT 'a','7',13,100 UNION ALL
SELECT 'b','7',1,60 UNION ALL
SELECT 'b','7',2,100 UNION ALL
SELECT 'b','7',3,100
DECLARE @s varchar(1000)
SELECT @s=''
SELECT @s=isnull(@s+'/','')+ltrim(id) from
(
SELECT * FROM # A
WHERE NOT EXISTS(SELECT 1 FROM # WHERE value<100 and id>=A.id)
)a
SELECT @s='SELECT [name],[month], '''+@s+''' as id FROM # A WHERE NOT EXISTS(SELECT 1 FROM # WHERE value<100 and id>=A.id) group by name,month'
exec(@s) /*
name month id
---------- ---------- ----------------
a 7 /8/9/10/11/12/13
*/
INSERT INTO @TB SELECT 'a',7,1,100
UNION ALL SELECT 'a',7,2,80
UNION ALL SELECT 'a',7,3,100
UNION ALL SELECT 'a',7,4,100
UNION ALL SELECT 'a',7,5,100
UNION ALL SELECT 'a',7,6,100
UNION ALL SELECT 'a',7,7,90
UNION ALL SELECT 'a',7,8,100
UNION ALL SELECT 'a',7,9,100
UNION ALL SELECT 'a',7,10,100
UNION ALL SELECT 'a',7,11,100
UNION ALL SELECT 'a',7,12,100
UNION ALL SELECT 'a',7,13,100 DECLARE @A VARCHAR(50)
SELECT @A=''
SELECT @A=isnull(@A,'')+'/'+ltrim(DD) FROM
(
SELECT * FROM @TB A
WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE NUM<>100 AND DD>=A.DD AND MM=A.MM)
) B
SET @A=STUFF(@A,1,1,'')SELECT TOP 1 IDNO,MM,NUM,DD=@A FROM @TB A
WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE NUM<>100 AND DD>=A.DD AND MM=A.MM)/*
IDNO MM NUM DD
---- ----------- ----------- --------------------------------------------------
a 7 100 8/9/10/11/12/13(所影响的行数为 1 行)
*/
b,7,4,100
b,7,5,100
b,7,6,100
b,7,7,100
b,7,8,100
b,7,9,100
....
得到应该是 下面的才对的啊,但是你们的不行啊?是我没有说明白么?
项目,月,目标,目标月
a,7,100,8/9/10/11/12/13
b,7,100,2/3/4/5/6/7/8/9
---- ----------- ----------- ----------- -----------
a 7 100 8 13
b 7 100 2 9
这样的结果不知道行不?
INSERT INTO @TB SELECT 'a',7,1,100
UNION ALL SELECT 'a',7,2,80
UNION ALL SELECT 'a',7,3,100
UNION ALL SELECT 'a',7,4,10
UNION ALL SELECT 'a',7,5,100
UNION ALL SELECT 'a',7,6,100
UNION ALL SELECT 'a',7,7,90
UNION ALL SELECT 'a',7,8,100
UNION ALL SELECT 'a',7,9,100
UNION ALL SELECT 'a',7,10,100
UNION ALL SELECT 'a',7,11,100
UNION ALL SELECT 'a',7,12,100
UNION ALL SELECT 'a',7,13,100
UNION ALL SELECT 'a',7,14,10
UNION ALL SELECT 'a',7,15,100
UNION ALL SELECT 'a',7,16,100
UNION ALL SELECT 'a',7,17,100
UNION ALL SELECT 'a',7,18,100
UNION ALL SELECT 'a',7,19,100
UNION ALL SELECT 'a',7,20,100
UNION ALL SELECT 'b',7,4,100
UNION ALL SELECT 'b',7,5,100
UNION ALL SELECT 'b',7,6,100
UNION ALL SELECT 'b',7,7,100
UNION ALL SELECT 'b',7,8,100
UNION ALL SELECT 'b',7,9,100
SELECT * FROM (
SELECT IDNO,MM,NUM,
DD1=DD,DD2=(
SELECT MIN(DD) FROM @TB B
WHERE IDNO=A.IDNO AND MM=A.MM AND NUM=A.NUM AND DD>=A.DD
AND NOT EXISTS(
SELECT * FROM @TB WHERE IDNO=B.IDNO AND NUM=A.NUM AND MM=B.MM AND DD=B.DD+1))
FROM @TB A
WHERE NOT EXISTS(
SELECT * FROM @TB WHERE IDNO=A.IDNO AND MM=A.MM AND NUM=A.NUM AND DD=A.DD-1)
AND NUM=100
) A
WHERE A.DD2-A.DD1>=5/*
IDNO MM NUM DD1 DD2
---- ----------- ----------- ----------- -----------
a 7 100 8 13
a 7 100 15 20
b 7 100 4 9(所影响的行数为 3 行)
*/
---- ----------- ----------- ----------- -----------
a 7 100 8 13
a 7 100 15 20
b 7 100 4 9(所影响的行数为 3 行)
*/
dd1是开始的日期 DD2是结束的日期
DECLARE @TB TABLE(IDNO VARCHAR(2),MM INT,DD INT,NUM INT)
INSERT INTO @TB SELECT 'a',7,1,100
UNION ALL SELECT 'a',7,2,80
UNION ALL SELECT 'a',7,3,100
UNION ALL SELECT 'a',7,4,10
UNION ALL SELECT 'a',7,5,100
UNION ALL SELECT 'a',7,6,100
UNION ALL SELECT 'a',7,7,90
UNION ALL SELECT 'a',7,8,100
UNION ALL SELECT 'a',7,9,100
UNION ALL SELECT 'a',7,10,100
UNION ALL SELECT 'a',7,11,100
UNION ALL SELECT 'a',7,12,100
UNION ALL SELECT 'a',7,13,100
UNION ALL SELECT 'a',7,14,10UNION ALL SELECT 'b',7,8,100
UNION ALL SELECT 'b',7,9,100 declare @sql varchar(500)
declare @idno varchar(10)
declare @mm intDECLARE @TBT TABLE(IDNO VARCHAR(2),MM INT,DD varchar(500),NUM INT)DECLARE TempCursor cursor STATIC FORWARD_ONLY FOR
select distinct idno,mm from @TB where num = 100
OPEN TempCursor
FETCH NEXT FROM TempCursor
INTO @idno,@mm
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = ''
select @sql = @sql + cast(DD as varchar) + '/' from @TB where num = 100 and idno = @idno and mm =@MM
INSERT INTO @TBt values(@idno,@mm,left(@sql,len(@sql)-1),100)
FETCH NEXT FROM TempCursor
INTO @idno,@mm
END CLOSE TempCursor
DEALLOCATE TempCursorselect * from @TBt