DROP TABLE [TTTT]
CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '1' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '1' , 16.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 17.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '2' , 18.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 2.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 21.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '4' , 22.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '5' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '5' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '5' , 10.0000 )统计结果为: 日期 代码 统计数值
2010-01-03 1 2
2010-01-03 2 3
2010-01-03 3 1
2010-01-02 4 1
2010-01-03 5 2意思是统计数值大于或等于15的数据,但是中间如果出现小于15就不统计,就象代码3一样,中间出现小于15的,就重新统计
其中代码5的统计数值是如果最后一个小于15,就统计上一次出现大于15的统计数值,多谢你了!!!!!!
CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '1' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '1' , 16.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 17.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '2' , 18.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 2.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 21.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '4' , 22.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '5' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '5' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '5' , 10.0000 )统计结果为: 日期 代码 统计数值
2010-01-03 1 2
2010-01-03 2 3
2010-01-03 3 1
2010-01-02 4 1
2010-01-03 5 2意思是统计数值大于或等于15的数据,但是中间如果出现小于15就不统计,就象代码3一样,中间出现小于15的,就重新统计
其中代码5的统计数值是如果最后一个小于15,就统计上一次出现大于15的统计数值,多谢你了!!!!!!
CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '1' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '1' , 16.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 17.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '2' , 18.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 2.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 21.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '4' , 22.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '5' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '5' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '5' , 10.0000 ) select max([日期]) as [日期],
[代码],
count(1) - (select count(1) from [TTTT] r
where [代码]=t.[代码] and [日期]<=
(select max(日期) from [TTTT] where [代码]=r.[代码] and 数值 < 15)) as 统计数值
from [TTTT] t
group by [代码]
------------------------------------
2010-01-03 00:00:00.000 1 2
2010-01-03 00:00:00.000 2 3
2010-01-03 00:00:00.000 3 1
2010-01-02 00:00:00.000 4 1
2010-01-05 00:00:00.000 5 0
where
not exists(select 1 from [TTTT] c where a.[代码]=c.[代码] and a.[日期]<=c.[日期] and c.[数值]<15)
group by [代码]不是写过这个吗
CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '1' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '1' , 16.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 17.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '2' , 18.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 2.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 21.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '4' , 22.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '5' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '5' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '5' , 10.0000 ) select max([日期]) as [日期],
[代码],
count(1) - (select count(1) from [TTTT] r
where [代码]=t.[代码] and [日期]<=
case (select max(日期) from [TTTT] where [代码]=r.[代码] and 数值 < 15)
when max(t.[日期]) then min(t.[日期]) else
(select max(日期) from [TTTT] where [代码]=r.[代码] and 数值 < 15)
end
)
as 统计数值
from [TTTT] t
group by [代码]------------------------------------
2010-01-03 00:00:00.000 1 2
2010-01-03 00:00:00.000 2 3
2010-01-03 00:00:00.000 3 1
2010-01-02 00:00:00.000 4 1
2010-01-05 00:00:00.000 5 2
DROP TABLE [TTTT]
CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '1' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '1' , 16.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 17.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '2' , 18.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 2.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 21.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '4' , 22.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '5' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '5' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '5' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-06 00:00:00.000' , '5' , 10.0000 )
CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '1' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '1' , 16.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 17.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '2' , 18.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 2.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 21.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '4' , 22.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '5' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '5' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '5' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-06 00:00:00.000' , '5' , 10.0000 )
go
select max(convert(char(10),[日期],120)),[代码],sum(case when [数值]<15 then 0 else 1 end)
from tttt
group by [代码]
DROP TABLE [TTTT]
代码
---------- -------------------------------------------------- -----------
2010-01-03 1 2
2010-01-03 2 3
2010-01-03 3 2
2010-01-02 4 1
2010-01-06 5 2(所影响的行数为 5 行)
CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '1' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '1' , 16.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 17.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '2' , 18.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 2.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 21.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '4' , 22.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '5' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '5' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '5' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-06 00:00:00.000' , '5' , 10.0000 )
SELECT *,x=NULL,y=null INTO # FROM ttttDECLARE @i INT,@j INT ,@d varchar(20),@sz SMALLMONEY
SELECT @i=0,@j=0UPDATE # SET @i=CASE WHEN 代码<>@d THEN @i+1 else
case when 数值<15 then @i+1
WHEN 数值>=15 and @j=0 THEN @i
ELSE @i+1 END
END
,@d=代码,@j=case when 代码=@d and 数值<15 then 1 else 0 end,y=1-@J,x=@i
SELECT 日期=(select max(日期) from # WHERE y=1 and 代码=a.代码),
代码,
统计数值=(select count(1) from # where 代码=a.代码 and x=(select max(x) from # where y=1 and 代码=a.代码))
FROM # a
GROUP BY 代码DROP TABLE tttt,#--result
/*日期 代码 统计数值
------------------------------ ------------------------------ -----------
2010-01-03 00:00:00.000 1 2
2010-01-03 00:00:00.000 2 3
2010-01-03 00:00:00.000 3 1
2010-01-02 00:00:00.000 4 1
2010-01-03 00:00:00.000 5 2(所影响的行数为 5 行)
*/
(select * from [TTTT] a
where exists(select 1 from [TTTT] c where a.[代码]=c.[代码] and a.[日期]<=c.[日期] and c.[数值]>15)
) a
where not exists(select 1 from ( select*from [TTTT] a
where exists(select 1 from [TTTT] c
where a.[代码]=c.[代码] and a.[日期]<=c.[日期] and c.[数值]>15)) c
where a.[代码]=c.[代码] and a.[日期]<=c.[日期] and c.[数值]<15)
group by [代码]
日期 代码 统计数值
---------- -------------------------------------------------- -----------
2010-01-03 1 2
2010-01-03 2 3
2010-01-03 3 1
2010-01-02 4 1
2010-01-03 5 2(5 行受影响)
DROP TABLE [TTTT]
CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '1' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '1' , 16.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 17.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '2' , 18.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 2.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 21.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '4' , 22.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '5' , 19.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '5' , 20.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '5' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-06 00:00:00.000' , '5' , 10.0000 )select max([日期]) as [日期],
[代码],
count(1) - (select count(1) from [TTTT]
where [代码]=t.[代码] and
[日期] <= (select max(日期) from [TTTT] where [代码]=t.[代码] and 数值 < 15
and [日期] <= (select max([日期]) from [TTTT] where [代码]=t.[代码] and 数值 >= 15))
and [日期] <= (select max([日期]) from [TTTT] where [代码]=t.[代码] and 数值 >= 15))
as 统计数值
from [TTTT] t
where [日期] <= (select max([日期]) from [TTTT] where [代码]=t.[代码] and 数值 >= 15)
group by [代码]
---------------------------------------
2010-01-03 00:00:00.000 1 2
2010-01-03 00:00:00.000 2 3
2010-01-03 00:00:00.000 3 1
2010-01-02 00:00:00.000 4 1
2010-01-03 00:00:00.000 5 2