刚学存储过程,希望各位大虾给个标准点的格式!多谢!Q1,表结构:Name Num
------- -------
AAA 145入参: Name varchar(20)1.如果Name已存在,则 Num + 1
2.如果Name不存在,则插入新记录,Num=1Q2, 表结构:
Today MaxDay TodayNum MaxNum
---------- ---------- -------- --------
2006-12-23 2006-12-20 4656 45654入参: Daystr varchar(20)1.如果Daystr = Today ,则 TodayNum + 1 否则 Today = Daystr ,TodayNum=1
2.如果TodayNum > MaxNum ,则 MaxNum = TodayNum ,MaxDay = Today
------- -------
AAA 145入参: Name varchar(20)1.如果Name已存在,则 Num + 1
2.如果Name不存在,则插入新记录,Num=1Q2, 表结构:
Today MaxDay TodayNum MaxNum
---------- ---------- -------- --------
2006-12-23 2006-12-20 4656 45654入参: Daystr varchar(20)1.如果Daystr = Today ,则 TodayNum + 1 否则 Today = Daystr ,TodayNum=1
2.如果TodayNum > MaxNum ,则 MaxNum = TodayNum ,MaxDay = Today
解决方案 »
- sql调用推荐
- 数据库MDF文件很大
- 数据库查询问题
- 上亿条记录,表的设计问题,请教!!!!!
- 菜鸟求救 :如何在更新st表的同时更新sc表中对应的字段
- 各位大侠!谁能看出下面这个SqL语句什么意思??我想不出来.
- 树形结构,取一某节的所有子类节点ID值,并要包含它本身的ID,我有一个存储过程,只有取它子节点的ID集,不包含它本身节点ID,如何改才能包含进去
- 存储过程的问题+字段变化是怎么做
- 我要一起向数据库里插入20条数据用,sql语句如何实现(在线等)
- 如何读写nText类型数据,帮助中说用ReadText,不过不知道ReadText的返回值在什么地方?
- 究竟是什么原因造成这么大的查询时间差别
- 能否写这样一个视图:视图中一列是另外一个表中sum()(group by排序),详细见正文。解决即给分!
as
declare @flag int
select @flag = count(1) from Q1 where [name] = @Name
if @flag > 0
update Q1 set Num = Num + 1 where [name] = @Name
else
insert into Q1 values(@name,1)
create proc p2(@Daystr varchar(20))
as
declare @equ int
select @equ = count(1) from Q2 where Today = @Daystr
if @flag > 0
update Q2 set TodayNum = TodayNum + 1 where Today = @Daystr
else
update Q2 set Today = Daystr ,TodayNum=1 update Q2 set MaxNum = TodayNum ,MaxDay = Today where TodayNum > MaxNum
as
declare @equ int
select @equ = count(1) from Q2 where Today = @Daystr
if @flag > 0 --- 这里换成 @equ
update Q2 set TodayNum = TodayNum + 1 where Today = @Daystr
else
update Q2 set Today = Daystr ,TodayNum=1 update Q2 set MaxNum = TodayNum ,MaxDay = Today where TodayNum > MaxNum
create proc test_p @name varchar(20)
as
if exists(select 1 from Q1 where name=@name )
begin
update Q1
set num=num+1
where name=@name
end
else
begin
insert Q1 select @name,1
end
drop proc spTest1
if object_id('spTest2') is not null
drop proc spTest2
GO
----第一个存储过程
create proc spTest1 @Name varchar(20)
as
if exists(select 1 from Q1 where Name = @Name)
update Q1 set Num = Num + 1 where Name = @Name
else
insert into Q1(Name,Num) values(@Name,1)
GO
----第二个存储过程
create proc spTest2 @Daystr varchar(20)
as
update Q2 set
TodayNum = case when Today = @Daystr then TodayNum + 1 else TodayNnum end,
Today = case when Today = @Daystr then Today else @Daystr end,
TodayNum = case when Today = @Daystr then TodayNum else 1 end
update Q2 set MaxNum = TodayNum,MaxDay = Today where TodayNum > MaxNum
GO
as
if exists(select 1 from Q2 where convert(varchar(10),today,120)=@Daystr)--today为datetime属性需要转换,不是可以用today=@Daystr
begin
update Q2
set todaynum=todaynum+1
where convert(varchar(10),today,120)=@Daystr
end
if not exists(select 1 from Q2 where convert(varchar(10),today,120)=@Daystr )
begin
insert Q2(Today,TodayNum) select @Daystr,1
end
update Q2
set MaxNum = TodayNum,MaxDay = Today
where TodayNum > MaxNum