dawugui 请进，请您帮忙，sql查询问题

dawugui 请进，请您帮忙，sql查询问题 [已结贴,结贴人:wowo228]

wowo228
ffsdsd
等级:

发表于：2008-04-24 14:45:17 楼主

如何查询表统计代码

1 最近价格为35连续出现1的次数（以下是价格为35的1连续出现4次，）
2 价格为35的代码中1连续出现的最多次数,（以下是价格为35的代码中1连续出现的最多次数是8，）

表名＝统计

代码日期数目价格
1 2008-4-20 35
1 2008-4-13 77
1 2008-4-10 35
1 2008-4-6 43
1 2008-4-2 35
1 2008-3-30 35
2 2008-3-23 35
1 2008-3-23 35
2 2008-3-20 35
1 2008-3-15 89
1 2008-3-8 35
1 2008-3-5 35
1 2008-3-3 35
1 2008-3-1 35
1 2008-2-8 35
1 2008-2-5 35
1 2008-2-3 35
1 2008-2-1 35
2 2008-1-4 35

以下是您上次的回复给的代码，请帮忙把条件加个价格 = 35 ，谢谢，我是新手，弄了半天老是弄不好，万分感谢

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

如何查询表统计代码最近连续出现1的次数

表名＝统计

代码时间数目价格
1 2008-4-20
1 2008-4-13
1 2008-4-10
1 2008-4-6
1 2008-4-2
1 2008-3-30
2 2008-3-23
1 2008-3-20
1 2008-3-15
1 2008-3-8
2 2008-3-5
1 2008-3-2
1 2008-3-1

（以上是1连续出现6次，）

create table tb(代码 int, 时间 datetime)
insert into tb values(1 ,'2008-4-20' )
insert into tb values(1 ,'2008-4-13' )
insert into tb values(1 ,'2008-4-10' )
insert into tb values(1 ,'2008-4-6' )
insert into tb values(1 ,'2008-4-2' )
insert into tb values(1 ,'2008-3-30' )
insert into tb values(2 ,'2008-3-23' )
insert into tb values(1 ,'2008-3-20' )
insert into tb values(1 ,'2008-3-15' )
insert into tb values(1 ,'2008-3-8' )
insert into tb values(2 ,'2008-3-5' )
insert into tb values(1 ,'2008-3-2')
insert into tb values(1 ,'2008-3-1')
go

select 次数 = n1.px - n2.px from
(
select px from
(
select * , px = (select count(1) from tb where 时间 > t.时间) + 1 from tb t
) m1,
(
select top 1 * from tb where 时间 < (select top 1 时间 from tb where 时间 < getdate() and 代码 = 1 order by 时间 desc) and 代码 <> 1 order by 时间 desc
) m2
where m1.时间 = m2.时间
) n1,
(
select px from
(
select * , px = (select count(1) from tb where 时间 > t.时间) + 1 from tb t
) m1,
(
select top 1 时间 from tb where 时间 < getdate() and 代码 = 1 order by 时间 desc
) m2
where m1.时间 = m2.时间
) n2

drop table tb

/*
次数
-----------
6

（所影响的行数为 1 行）
*/
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

问题点数：100 回复次数：12 修改删除举报引用回复

set nocount on create table tb(代码 int, 时间 datetime,jg int) insert into tb values(1 ,'2008-4-20', 35 ) insert into tb values(1 ,'2008-4-13', 77 ) insert into tb values(1 ,'2008-4-10', 35 ) insert into tb values(1 ,'2008-4-6', 43 ) insert into tb values(1 ,'2008-4-2', 35 ) insert into tb values(1 ,'2008-3-30', 35 ) insert into tb values(2 ,'2008-3-23', 35 ) insert into tb values(1 ,'2008-3-23', 35 ) insert into tb values(2 ,'2008-3-20', 35 ) insert into tb values(1 ,'2008-3-15', 89 ) insert into tb values(1 ,'2008-3-8', 35 ) insert into tb values(1 ,'2008-3-5', 35 ) insert into tb values(1 ,'2008-3-3', 35 ) insert into tb values(1 ,'2008-3-1', 35 ) insert into tb values(1 ,'2008-2-8', 35 ) insert into tb values(1 ,'2008-2-5', 35 ) insert into tb values(1 ,'2008-2-3', 35 ) insert into tb values(1 ,'2008-2-1', 35 ) insert into tb values(2 ,'2008-1-4', 35 ) go select px = identity(int,1,1) ,* into # from tb select * into #1 from # c where exists(select 1 from # where c.代码 = 代码 and px=c.px-1 and jg = 35) and jg = 35 select count(1) + 2 from #1 a where exists (select 1 from #1 where px = a.px + 1 ) drop table tb,#,#1 /* ----------- 8 */

set nocount on create table tb(代码 int, 时间 datetime,jg int) insert into tb values(1 ,'2008-4-20', 35 ) insert into tb values(1 ,'2008-4-13', 77 ) insert into tb values(1 ,'2008-4-10', 35 ) insert into tb values(1 ,'2008-4-6', 43 ) insert into tb values(1 ,'2008-4-2', 35 ) insert into tb values(1 ,'2008-3-30', 35 ) insert into tb values(2 ,'2008-3-23', 35 ) insert into tb values(1 ,'2008-3-23', 35 ) insert into tb values(2 ,'2008-3-20', 35 ) insert into tb values(1 ,'2008-3-15', 89 ) insert into tb values(1 ,'2008-3-8', 35 ) insert into tb values(1 ,'2008-3-5', 35 ) insert into tb values(1 ,'2008-3-3', 35 ) insert into tb values(1 ,'2008-3-1', 35 ) insert into tb values(1 ,'2008-2-8', 35 ) insert into tb values(1 ,'2008-2-5', 35 ) insert into tb values(1 ,'2008-2-3', 35 ) insert into tb values(1 ,'2008-2-1', 35 ) insert into tb values(2 ,'2008-1-4', 35 ) go select px = identity(int,1,1) ,0 as id,* into # from tb declare @i int,@dm int set @i = 0 update # set id = @I,@i = @i + (case when 代码= @dm then 0 else 1 end ),@dm = 代码 select count(*) from # where id = 1 and jg = 35 drop table tb,# /* ----------- 4 */

create table ta(代码 int, 时间 datetime,jg int) insert into ta values(1 ,'2008-4-20', 35 ) insert into ta values(1 ,'2008-4-13', 77 ) insert into ta values(1 ,'2008-4-10', 35 ) insert into ta values(1 ,'2008-4-6', 43 ) insert into ta values(1 ,'2008-4-2', 35 ) insert into ta values(1 ,'2008-3-30', 35 ) insert into ta values(2 ,'2008-3-23', 35 ) insert into ta values(1 ,'2008-3-22', 35 ) insert into ta values(2 ,'2008-3-20', 35 ) insert into ta values(1 ,'2008-3-15', 89 ) insert into ta values(1 ,'2008-3-8', 35 ) insert into ta values(1 ,'2008-3-5', 35 ) insert into ta values(1 ,'2008-3-3', 35 ) insert into ta values(1 ,'2008-3-1', 35 ) insert into ta values(1 ,'2008-2-8', 35 ) insert into ta values(1 ,'2008-2-5', 35 ) insert into ta values(1 ,'2008-2-3', 35 ) insert into ta values(1 ,'2008-2-1', 35 ) insert into ta values(2 ,'2008-1-4', 35 ) go --用个临时表。 select * into tb from ta where jg = 35 select 次数 = n1.px - n2.px from ( select px from ( select * , px = (select count(1) from tb where 时间 > t.时间) + 1 from tb t ) m1, ( select top 1 * from tb where 时间 < (select top 1 时间 from tb where 时间 < getdate() and 代码 = 1 order by 时间 desc) and 代码 <> 1 order by 时间 desc ) m2 where m1.时间 = m2.时间 ) n1, ( select px from ( select * , px = (select count(1) from tb where 时间 > t.时间) + 1 from tb t ) m1, ( select top 1 时间 from tb where 时间 < getdate() and 代码 = 1 order by 时间 desc ) m2 where m1.时间 = m2.时间 ) n2 drop table ta,tb /* 次数 ----------- 4 （所影响的行数为 1 行） */