如下SQL中,我往一个网格表中插入数据,如果表中已经有了数据了,就不要插入了,改成Update
条件:考核人和考核日期相同
Update:平均值=(平均值×10)+新的平均值
Declare @i int
Set @i=0 While @i<5
begin
insert into [网格表]([考核人],[级别],[平均值],[考核日期])
Select 考核人,@i as '级别',avg(任务审核) as '平均值',
convert(varchar(10),getdate(),120)
from 任务表,审核表
Where 任务表.任务ID=审核表.任务ID
And 任务表.组级别=@i
Group by 考核人
Set @i=@i+1
end
测试环境如下
create table [任务表](
[任务Id] [int] IDENTITY(1,1) NOT NULL,
[组级别] varchar(10),
[考核人] [varchar](10),
CONSTRAINT [PK_renwub] PRIMARY KEY ([任务Id]))
go
create table [审核表](
[审核ID] [int] IDENTITY(1,1) NOT NULL,
[任务Id] [int] NULL,
[任务审核] [int] NULL,
CONSTRAINT [PK_renwu] PRIMARY KEY ([审核ID]))
GO
create table [网格表](
[网格ID] [int] IDENTITY(1,1) NOT NULL,
[考核人] [varchar](10),
[级别] [varchar](10),
[平均值] [int] NULL,
[考核日期] [varchar](10),
CONSTRAINT [PK_wangge] PRIMARY KEY ([网格ID]))
GO
insert into [任务表]([组级别], [考核人])values('1', '李四')
insert into [任务表]([组级别], [考核人])values('2', '张三')
insert into [任务表]([组级别], [考核人])values('2', '王二')
insert into [任务表]([组级别], [考核人])values('3', '刘大')
insert into [任务表]([组级别], [考核人])values('3', '王二')
insert into [任务表]([组级别], [考核人])values('3', '刘大')
insert into [任务表]([组级别], [考核人])values('3', '刘大')
insert into [任务表]([组级别], [考核人])values('3', '王二')insert into [审核表]([任务Id], [任务审核])values(1, 4)
insert into [审核表]([任务Id], [任务审核])values(2, 1)
insert into [审核表]([任务Id], [任务审核])values(3, 1)
insert into [审核表]([任务Id], [任务审核])values(4, 2)
insert into [审核表]([任务Id], [任务审核])values(5, 1)
insert into [审核表]([任务Id], [任务审核])values(6, 3)
insert into [审核表]([任务Id], [任务审核])values(7, 1)
insert into [审核表]([任务Id], [任务审核])values(8, 2)
Go
Go
SELECT * FROM 任务表
SELECT * FROM 审核表
SELECT * FROM 网格表
GO
DROP TABLE 任务表
DROP TABLE 审核表
DROP TABLE 网格表
条件:考核人和考核日期相同
Update:平均值=(平均值×10)+新的平均值
Declare @i int
Set @i=0 While @i<5
begin
insert into [网格表]([考核人],[级别],[平均值],[考核日期])
Select 考核人,@i as '级别',avg(任务审核) as '平均值',
convert(varchar(10),getdate(),120)
from 任务表,审核表
Where 任务表.任务ID=审核表.任务ID
And 任务表.组级别=@i
Group by 考核人
Set @i=@i+1
end
测试环境如下
create table [任务表](
[任务Id] [int] IDENTITY(1,1) NOT NULL,
[组级别] varchar(10),
[考核人] [varchar](10),
CONSTRAINT [PK_renwub] PRIMARY KEY ([任务Id]))
go
create table [审核表](
[审核ID] [int] IDENTITY(1,1) NOT NULL,
[任务Id] [int] NULL,
[任务审核] [int] NULL,
CONSTRAINT [PK_renwu] PRIMARY KEY ([审核ID]))
GO
create table [网格表](
[网格ID] [int] IDENTITY(1,1) NOT NULL,
[考核人] [varchar](10),
[级别] [varchar](10),
[平均值] [int] NULL,
[考核日期] [varchar](10),
CONSTRAINT [PK_wangge] PRIMARY KEY ([网格ID]))
GO
insert into [任务表]([组级别], [考核人])values('1', '李四')
insert into [任务表]([组级别], [考核人])values('2', '张三')
insert into [任务表]([组级别], [考核人])values('2', '王二')
insert into [任务表]([组级别], [考核人])values('3', '刘大')
insert into [任务表]([组级别], [考核人])values('3', '王二')
insert into [任务表]([组级别], [考核人])values('3', '刘大')
insert into [任务表]([组级别], [考核人])values('3', '刘大')
insert into [任务表]([组级别], [考核人])values('3', '王二')insert into [审核表]([任务Id], [任务审核])values(1, 4)
insert into [审核表]([任务Id], [任务审核])values(2, 1)
insert into [审核表]([任务Id], [任务审核])values(3, 1)
insert into [审核表]([任务Id], [任务审核])values(4, 2)
insert into [审核表]([任务Id], [任务审核])values(5, 1)
insert into [审核表]([任务Id], [任务审核])values(6, 3)
insert into [审核表]([任务Id], [任务审核])values(7, 1)
insert into [审核表]([任务Id], [任务审核])values(8, 2)
Go
Go
SELECT * FROM 任务表
SELECT * FROM 审核表
SELECT * FROM 网格表
GO
DROP TABLE 任务表
DROP TABLE 审核表
DROP TABLE 网格表
Update:平均值=(平均值×10)+新的平均值
Declare @i int
Set @i=0 While @i<5
begin
insert into [网格表]([考核人],[级别],[平均值],[考核日期])
Select 考核人,@i as '级别',avg(任务审核) as '平均值',
convert(varchar(10),getdate(),120)
from 任务表,审核表
Where 任务表.任务ID=审核表.任务ID
And 任务表.组级别=@i
and not exists (
select 1 from [网格表] where [考核人]=任务表.[考核人]
and [考核日期]=convert(varchar(10),getdate(),120)
)
Group by 考核人
update
set 平均值=(a.平均值×10)+t.平均值
from [网格表] a,(
Select 考核人,avg(任务审核) as '平均值',
convert(varchar(10),getdate(),120) as [考核日期]
from 任务表,审核表
Where 任务表.任务ID=审核表.任务ID
And 任务表.组级别=@i
and exists (
select 1 from [网格表] where [考核人]=任务表.[考核人]
and [考核日期]=convert(varchar(10),getdate(),120)
)
Group by 考核人
) as t
where a.考核人=t.考核人 and a.[考核日期]=t.[考核日期]
Set @i=@i+1
end
需要判断级别的,级别是1的肯定是Insert 只有>1的才会可能Update,然后就是上面的全角字符都更换了,还是报上面的代码编译不通过
服务器: 消息 156,级别 15,状态 1,行 19
在关键字 'set' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 31
在关键字 'as' 附近有语法错误。
Set @i=0 While @i<5
begin
update a
set 平均值=(a.平均值×10)+t.平均值,级别=@i
from [网格表] a,(
Select 考核人,avg(任务审核) as '平均值',
convert(varchar(10),getdate(),120) as [考核日期]
from 任务表,审核表
Where 任务表.任务ID=审核表.任务ID
And 任务表.组级别=@i
and exists (
select 1 from [网格表] where [考核人]=任务表.[考核人]
and [考核日期]=convert(varchar(10),getdate(),120)
)
Group by 考核人
) as t
where a.考核人=t.考核人 and a.[考核日期]=t.[考核日期] insert into [网格表]([考核人],[级别],[平均值],[考核日期])
Select 考核人,@i as '级别',avg(任务审核) as '平均值',
convert(varchar(10),getdate(),120)
from 任务表,审核表
Where 任务表.任务ID=审核表.任务ID
And 任务表.组级别=@i
and not exists (
select 1 from [网格表] where [考核人]=任务表.[考核人]
and [考核日期]=convert(varchar(10),getdate(),120)
)
Group by 考核人 Set @i=@i+1
end
Set @i=0 While @i<5
begin
update a
set 平均值=(a.平均值*10)+t.平均值,级别=@i
from [网格表] a,(
Select 考核人,avg(任务审核) as '平均值',
convert(varchar(10),getdate(),120) as [考核日期]
from 任务表,审核表
Where 任务表.任务ID=审核表.任务ID
And 任务表.组级别=@i
and exists (
select 1 from [网格表] where [考核人]=任务表.[考核人]
and [考核日期]=convert(varchar(10),getdate(),120)
)
Group by 考核人
) as t
where a.考核人=t.考核人 and a.[考核日期]=t.[考核日期] insert into [网格表]([考核人],[级别],[平均值],[考核日期])
Select 考核人,@i as '级别',avg(任务审核) as '平均值',
convert(varchar(10),getdate(),120)
from 任务表,审核表
Where 任务表.任务ID=审核表.任务ID
And 任务表.组级别=@i
and not exists (
select 1 from [网格表] where [考核人]=任务表.[考核人]
and [考核日期]=convert(varchar(10),getdate(),120)
)
Group by 考核人 Set @i=@i+1
end
分是你的了... :)