A表 有spbh,rq,kcsl等字段 B表字段 有spbh,jjsl等字段
比如A
spbh rq kcslaaa 2009-08-01 100
aaa 2009-08-02 80
aaa 2009-08-03 30
aaa 2009-08-04 130
aaa 2009-08-05 80B
spbh jjslaaa 100C
spbh cishu
aaa ?
要求 当A表的kcsl小于B表的jjsl时候往C表里记一次 连续几天都小于的只记一次
如2009-08-02~2009-08-03 连续两个小于jjsl 就只记一次
在8月1日到8月30日之间想要查看aaa的kcsl小于jjsl有多少次? 我看见一位的解答 declare @A table(spbh varchar(10),rq datetime,kcs1 int)
declare @B table(spbh varchar(10),jjs1 int)
insert @A select 'aaa','2009-8-01',80 union all select 'aaa','2009-8-02', 180
union all select 'aaa','2009-8-03', 30 union all select 'aaa','2009-8-04',10
union all select 'aaa','2009-8-05', 80 union all select 'aaa','2009-8-06',18
union all select 'aaa','2009-8-07',18
insert @B select 'aaa',100
declare ab_cur cursor FOR
select A.rq,A.kcs1,B.jjs1 from @A A left join @B B on A.spbh=B.spbh and A.rq>='2009-8-1' and A.rq<='2009-8-30' order by A.rq
open ab_cur
declare @rq datetime,@kcs1 int,@jjs1 int,@count int
set @count=0
fetch next from ab_cur into @rq,@kcs1,@jjs1
while @@fetch_status=0
BEGIN
IF( @kcs1<@jjs1 )
set @count=@count+1
fetch next from ab_cur into @rq,@kcs1,@jjs1
END
close ab_cur deallocate ab_cur
SELECT @count
SELECT * FROM @a
SELECT * FROM @b
他的这个没有解决好连续几天都小于的只记一次
比如A
spbh rq kcslaaa 2009-08-01 100
aaa 2009-08-02 80
aaa 2009-08-03 30
aaa 2009-08-04 130
aaa 2009-08-05 80B
spbh jjslaaa 100C
spbh cishu
aaa ?
要求 当A表的kcsl小于B表的jjsl时候往C表里记一次 连续几天都小于的只记一次
如2009-08-02~2009-08-03 连续两个小于jjsl 就只记一次
在8月1日到8月30日之间想要查看aaa的kcsl小于jjsl有多少次? 我看见一位的解答 declare @A table(spbh varchar(10),rq datetime,kcs1 int)
declare @B table(spbh varchar(10),jjs1 int)
insert @A select 'aaa','2009-8-01',80 union all select 'aaa','2009-8-02', 180
union all select 'aaa','2009-8-03', 30 union all select 'aaa','2009-8-04',10
union all select 'aaa','2009-8-05', 80 union all select 'aaa','2009-8-06',18
union all select 'aaa','2009-8-07',18
insert @B select 'aaa',100
declare ab_cur cursor FOR
select A.rq,A.kcs1,B.jjs1 from @A A left join @B B on A.spbh=B.spbh and A.rq>='2009-8-1' and A.rq<='2009-8-30' order by A.rq
open ab_cur
declare @rq datetime,@kcs1 int,@jjs1 int,@count int
set @count=0
fetch next from ab_cur into @rq,@kcs1,@jjs1
while @@fetch_status=0
BEGIN
IF( @kcs1<@jjs1 )
set @count=@count+1
fetch next from ab_cur into @rq,@kcs1,@jjs1
END
close ab_cur deallocate ab_cur
SELECT @count
SELECT * FROM @a
SELECT * FROM @b
他的这个没有解决好连续几天都小于的只记一次
为什么要不游标呢?
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-09-11 18:43:22
---------------------------------
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
go
CREATE TABLE [a] (spbh VARCHAR(3),rq DATETIME,kcsl INT)
INSERT INTO [a]
SELECT 'aaa','2009-08-01',100 UNION ALL
SELECT 'aaa','2009-08-02',80 UNION ALL
SELECT 'aaa','2009-08-03',30 UNION ALL
SELECT 'aaa','2009-08-04',130 UNION ALL
SELECT 'aaa','2009-08-05',80select * from [a]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-09-11 18:43:37
---------------------------------
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
go
CREATE TABLE [B] (spbh VARCHAR(3),jjsl INT)
INSERT INTO [B]
SELECT 'aaa',100select * from [B]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-09-11 18:44:15
---------------------------------
IF OBJECT_ID('[C]') IS NOT NULL
DROP TABLE [C]
go
CREATE TABLE [C] (spbh VARCHAR(3),cishu int)
INSERT INTO [C]
SELECT 'aaa',nullselect * from [C]
select cnt=count(*)
from
(select a.* from a join b on a.kcsl<b.jjsl) K
where not exists (select 1 from (select a.* from a join b on a.kcsl<b.jjsl) L where k.rq=l.rq+1)2
insert into @a select 'aaa','2009-08-01',100
union all select 'aaa','2009-08-02',80
union all select 'aaa','2009-08-03',30
union all select 'aaa','2009-08-04',130
union all select 'aaa','2009-08-05',80
declare @b table (spbh nvarchar(10),jjsl int)
insert into @b select 'aaa',100
;with china as
(
select a.*,DATEPART(DD,rq) 天 from @a a join @b b
on a.spbh=a.spbh and a.kcsl<b.jjsl
)
select a.spbh,a.rq,a.kcsl from china a where not exists (
select 1 from china b where a.天=b.天-1)spbh rq kcsl
---------- ----------------------- -----------
aaa 2009-08-03 00:00:00.000 30
aaa 2009-08-05 00:00:00.000 80(2 行受影响)
declare @A table (spbh varchar(3),rq datetime,kcsl int)
insert into @A
select 'aaa','2009-08-01',100 union all
select 'aaa','2009-08-02',80 union all
select 'aaa','2009-08-03',30 union all
select 'aaa','2009-08-04',130 union all
select 'aaa','2009-08-05',80
--> 测试数据: @B
declare @B table (spbh varchar(3),jjsl int)
insert into @B
select 'aaa',100select count(*) from
(select a.* from @a a,@b b where a.kcsl<b.jjsl)a
where not exists(select 1 from (select a.* from @a a,@b b where a.kcsl<b.jjsl)t
where spbh=a.spbh and datediff(dd,rq,a.rq)=1)
--我晕,楼主每次都求最麻烦和最低级的解法。无语了。
--> 测试数据: a
create table a(spbh varchar(3),rq datetime,kcsl int)
insert into a
select 'aaa','2009-08-01',100 union all
select 'aaa','2009-08-02',80 union all
select 'aaa','2009-08-03',30 union all
select 'aaa','2009-08-04',140 union all
select 'aaa','2009-08-05',80--> 测试数据: b
create table b(spbh varchar(3),jjsl int)
insert into b
select 'aaa',100
gocreate proc f_wsp
@spbh varchar(10)
as
declare @n int,@dt datetime,@olddt datetime
set @n=0
declare wsp cursor for select a.rq from a,b where a.spbh=@spbh and a.spbh=b.spbh and a.kcsl<b.jjsl order by rq
open wsp
fetch wsp into @dt
set @olddt=@dt
while(@@fetch_status=0)
begin
print @olddt
print @dt
if(@olddt=@dt)
set @n=@n+1
else
begin
if(datediff(dd,@olddt,@dt)>1)
set @n=@n+1
set @olddt=@dt
end
fetch wsp into @dt
end
close wsp
deallocate wsp
select @n
goexec f_wsp 'aaa'