统计连续出现的次数,尽量不使用游标,多谢大家了!!! 本帖最后由 ken2002 于 2010-03-01 17:15:38 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 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' , 10.0000 )select max([日期]) as [日期],[代码],count([数值]) as [数值] from tttt awhere not exists(select 1 from tttt b where a.[代码]=b.[代码] and b.[日期]>=a.[日期] and b.[数值]<15)group by [代码]日期 代码 数值----------------------- -------------------------------------------------- -----------2010-01-03 00:00:00.000 1 22010-01-03 00:00:00.000 2 32010-01-03 00:00:00.000 3 12010-01-02 00:00:00.000 4 1(4 行受影响) IF OBJECT_ID('TTTT') IS NOT NULLBEGIN DROP TABLE [TTTT] END 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' , 10.0000 )SELECT [日期],[代码],SUM(CASE WHEN [数值]>15.0000 THEN 1 ELSE 0 END) OVER(PARTITION BY [代码]) AS [统计数值]FROM [TTTT]ORDER BY [代码]DROP TABLE [TTTT]没看懂你的意思,应该不是按照[日期],[代码]分组吧 --DROP TABLE [TTTT] ,#T 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-04 00:00:00.000' , '2' , 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' , 10.0000 )SELECT 日期,代码,数值 INTO #TFROM TTTT T WHERE 数值>=15ORDER BY 代码SELECT MAX(日期)日期,代码, ( SELECT SUM(NUM)+1 FROM (SELECT *,(SELECT COUNT (1) FROM #T WHERE 代码=T.代码 AND 日期=T.日期-1)NUM FROM #T T )AS T1 WHERE 代码=T.代码 )AS NUMFROM (SELECT *, (SELECT COUNT (1) FROM #T WHERE 代码=T.代码 AND 日期=T.日期-1)NUM FROM #T T) TGROUP BY 代码日期 代码 NUM ------------------------------------------------------ -------------------------------------------------- ----------- 2010-01-03 00:00:00.000 1 22010-01-03 00:00:00.000 2 32010-01-03 00:00:00.000 3 12010-01-02 00:00:00.000 4 1(所影响的行数为 4 行) 不过还想继续问个问题,就是以下要怎么统计呢,多谢了!!!!!!CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 15.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' , 15.0000 ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 15.0000 ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 15.0000 ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-04 00:00:00.000' , '2' , 10.0000 ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 15.0000 ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 10.0000 ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 15.0000 ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '2' , 15.0000 )想得到以下的结果:日期 代码 数值2010-01-03 1 32010-01-03 2 22010-01-04 2 12010-01-01 3 12010-01-03 3 1就是中间大于15也显示出来,连续的话,就统计连续几天出现的次数,多谢了!!!!!! 今日问题是MSSQL数据更新 查询结果建表保存 邀请zjcxc(邹建)回答一个:实现数据由SQLServer到Access case when 中转换为数据类型为 int 的列时发生语法错误 【请问】问个简单的问题,"... N'[dbo].[sp_drop_constraints] ... "里的 N' 这种形式是什么意思... 关于替换的问题 高分请高手,在线等待 无法初始化SSPI软件包 一个异常报错?紧急求助 求帮助 ??? SQL 简单两表连接问题 ??? 求一条插入的SQL语句
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' , 10.0000 )
select max([日期]) as [日期],[代码],count([数值]) as [数值] from tttt a
where not exists(select 1 from tttt b where a.[代码]=b.[代码] and b.[日期]>=a.[日期] and b.[数值]<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(4 行受影响)
BEGIN
DROP TABLE [TTTT]
END
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' , 10.0000 )SELECT [日期],[代码],SUM(CASE WHEN [数值]>15.0000 THEN 1 ELSE 0 END) OVER(PARTITION BY [代码]) AS [统计数值]
FROM [TTTT]
ORDER BY [代码]
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-04 00:00:00.000' , '2' , 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' , 10.0000 )SELECT 日期,代码,数值 INTO #T
FROM TTTT T WHERE 数值>=15
ORDER BY 代码SELECT MAX(日期)日期,代码,
(
SELECT SUM(NUM)+1 FROM
(SELECT *,(SELECT COUNT (1) FROM #T WHERE 代码=T.代码 AND 日期=T.日期-1)NUM FROM #T T )AS T1
WHERE 代码=T.代码 )AS NUMFROM
(SELECT
*,
(SELECT COUNT (1) FROM #T WHERE 代码=T.代码 AND 日期=T.日期-1)NUM
FROM #T T) T
GROUP BY 代码日期 代码 NUM
------------------------------------------------------ -------------------------------------------------- -----------
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(所影响的行数为 4 行)
CREATE TABLE [TTTT] ( [日期] [datetime] NULL , [代码] [varchar] (50) NULL , [数值] [smallmoney] NULL ) INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '1' , 15.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' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '2' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '2' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-04 00:00:00.000' , '2' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-01 00:00:00.000' , '3' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-02 00:00:00.000' , '3' , 10.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-03 00:00:00.000' , '3' , 15.0000 )
INSERT [TTTT] ( [日期] , [代码] , [数值] ) VALUES ( '2010-01-05 00:00:00.000' , '2' , 15.0000 )想得到以下的结果:日期 代码 数值
2010-01-03 1 3
2010-01-03 2 2
2010-01-04 2 1
2010-01-01 3 1
2010-01-03 3 1就是中间大于15也显示出来,连续的话,就统计连续几天出现的次数,多谢了!!!!!!