• 主页
• 基础类
• 应用实例
• 新技术前沿

# 交叉数据表的数据更新问题,有代码,高手请进!

stwx 2003-12-14 02:32:38
create table #Ta (工号 tinyint,日期 datetime,数量 tinyint)
insert into #ta(工号,日期,数量) values(10,'2003-12-1',1)
insert into #ta(工号,日期,数量) values(10,'2003-12-2',1)
insert into #ta(工号,日期,数量) values(10,'2003-12-3',0)
insert into #ta(工号,日期,数量) values(10,'2003-12-4',2)
insert into #ta(工号,日期,数量) values(11,'2003-12-1',0)
insert into #ta(工号,日期,数量) values(11,'2003-12-2',2)
insert into #ta(工号,日期,数量) values(11,'2003-12-3',1)
insert into #ta(工号,日期,数量) values(11,'2003-12-4',2)
select * from #ta

--#ta 的交叉数据表 #tb
create table #tb(工号 tinyint,
[2003-12-1] tinyint,
[2003-12-2] tinyint,
[2003-12-3] tinyint,
[2003-12-4] tinyint,
)
insert into #tb (工号,[2003-12-1],[2003-12-2],[2003-12-3],[2003-12-4])
values(10,1,1,0,2)
insert into #tb (工号,[2003-12-1],[2003-12-2],[2003-12-3],[2003-12-4])
values(11,0,2,1,2)

select * from #tb

/* 问题:

*/
drop table #tb
drop table #ta

...全文
8 点赞 收藏 5

5 条回复

--那就是用触发器

--下面是例子:

--创建表
create table ta (工号 tinyint,日期 datetime,数量 tinyint)
insert into ta(工号,日期,数量)
select 10,'2003-12-1',1
union all select 10,'2003-12-2',1
union all select 10,'2003-12-3',0
union all select 10,'2003-12-4',2
union all select 11,'2003-12-1',0
union all select 11,'2003-12-2',2
union all select 11,'2003-12-3',1
union all select 11,'2003-12-4',2

create table tb(工号 tinyint,
[2003-12-1] tinyint,[2003-12-2] tinyint,
[2003-12-3] tinyint,[2003-12-4] tinyint,)
insert into tb(工号,[2003-12-1],[2003-12-2],[2003-12-3],[2003-12-4])
select 10,1,1,0,2
union all select 11,0,2,1,2
go

--创建更新交叉表时,自动更新ta表的触发器
create trigger t_process on tb
for insert,delete,update
as
delete ta
from ta a join deleted b on a.工号=b.工号

insert into ta
select 工号,'2003-12-1',[2003-12-1] from inserted
union all
select 工号,'2003-12-2',[2003-12-2] from inserted
union all
select 工号,'2003-12-3',[2003-12-3] from inserted
union all
select 工号,'2003-12-4',[2003-12-4] from inserted
go

--修改tb表测试
update tb set [2003-12-2]=100

--查询结果
select * from ta

go
drop table ta,tb

/*--测试结果

---- ------------------------------------------------------ ----
10 2003-12-01 00:00:00.000 1
11 2003-12-01 00:00:00.000 0
10 2003-12-02 00:00:00.000 100
11 2003-12-02 00:00:00.000 100
10 2003-12-03 00:00:00.000 0
11 2003-12-03 00:00:00.000 1
10 2003-12-04 00:00:00.000 2
11 2003-12-04 00:00:00.000 2

（所影响的行数为 8 行）
--*/

--如果你的日期字段值是固定的,可以将tb改为视图,这样也可以达到你的目的.
create view tb
as
select 工号
,[2003-12-01]=sum(case 日期 when '2003-12-01' then 数量 else 0 end)
,[2003-12-02]=sum(case 日期 when '2003-12-02' then 数量 else 0 end)
,[2003-12-03]=sum(case 日期 when '2003-12-03' then 数量 else 0 end)
,[2003-12-04]=sum(case 日期 when '2003-12-04' then 数量 else 0 end)
from ta group by 工号

--将交叉表改为存储过程自动生成就行了.

--例如:
--创建原表:ta
create table Ta(工号 tinyint,日期 datetime,数量 tinyint)
insert into Ta
select 10,'2003-12-1',1
union all select 10,'2003-12-2',1
union all select 10,'2003-12-3',0
union all select 10,'2003-12-4',2
union all select 11,'2003-12-1',0
union all select 11,'2003-12-2',2
union all select 11,'2003-12-3',1
union all select 11,'2003-12-4',2
go

--创建生成交叉表的存储过程
create proc p_qry
as
set nocount on
declare @s varchar(8000)
set @s=''
select @s=@s+',['+日期+']=sum(case 日期 when '''+日期+''' then 数量 else 0 end)'
from(select distinct 日期=convert(varchar(10),日期,120) from ta) a
exec('select 工号'+@s+' from ta group by 工号')
set nocount off
go

--调用存储过程,得到交叉表tb
exec p_qry
go

--删除测试环境
drop table ta
drop proc p_qry

/*--测试结果

---- ----------- ----------- ----------- -----------
10 1 1 0 2
11 0 2 1 2
--*/

stwx 2003-12-15
zjcxc(邹建):

wzh1215 2003-12-14

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区