有个表A字段如:
编号 类型 工序1开始时间 工序1结束时间 工序2开始时间 工序2结束时间.....
001 A0101 2005-1-1 17:00 2005-1-4 18:00 2005-1-4 19:00 2005-1-10 8:00
002 A0201 2005-1-1 13:00 NULL 2005-1-4 10:00 NULL
003 A0101 2005-1-1 10:00 NULL NULL NULL当表A记录(即时间被修改,插入后)触发,在B表上自动修改或写入新的统计结果另外一个表B结构
日期 类型 工序1开始数目 工序1结束数目 工序2开始数目 工序2结束数目
2005-1-1 A0101 2 1 0 0
2005-1-1 A0201 1 0 0
编号 类型 工序1开始时间 工序1结束时间 工序2开始时间 工序2结束时间.....
001 A0101 2005-1-1 17:00 2005-1-4 18:00 2005-1-4 19:00 2005-1-10 8:00
002 A0201 2005-1-1 13:00 NULL 2005-1-4 10:00 NULL
003 A0101 2005-1-1 10:00 NULL NULL NULL当表A记录(即时间被修改,插入后)触发,在B表上自动修改或写入新的统计结果另外一个表B结构
日期 类型 工序1开始数目 工序1结束数目 工序2开始数目 工序2结束数目
2005-1-1 A0101 2 1 0 0
2005-1-1 A0201 1 0 0
如A0101 在2005-1-1日工序1开始了2台, 完成了为0
日期 类型 工序1开始数目 工序1结束数目 工序2开始数目 工序2结束数目
2005-1-1 A0101 2 0 0 0
如非用触发器不可,语句的更新同视图一样。
只是加个判断,tb表有月份和类型更新,没有就插入.
create table ta(编号 varchar(3),类型 varchar(5),工序1开始时间 datetime, 工序1结束时间 datetime,
工序2开始时间 datetime , 工序2结束时间 datetime)
insert ta
select '001', 'A0101', '2005-1-1 17:00', '2005-1-4 18:00', '2005-1-4 19:00', '2005-1-10 8:00' union all
select '002', 'A0201', '2005-1-1 13:00', NULL, '2005-1-4 10:00', NULL union all
select '003', 'A0101', '2005-1-1 10:00', NULL, NULL , NULLcreate view tb
as
select 编号=convert(varchar(10),工序1开始时间,120),
类型,
工序1开始时间=count(convert(varchar(10),工序1开始时间,120)),
工序1结束时间=count(convert(varchar(10),工序1结束时间,120)),
工序2开始时间=count(convert(varchar(10),工序2开始时间,120)),
工序2结束时间=count(convert(varchar(10),工序2结束时间,120))
from ta
group by convert(varchar(10),工序1开始时间,120),类型编号 类型 工序1开始时间 工序1结束时间 工序2开始时间 工序2结束时间
---------- ----- ----------- ----------- ----------- -----------
2005-01-01 A0101 2 1 1 1
2005-01-01 A0201 1 0 1 0(所影响的行数为 2 行)警告: 聚合或其它 SET 操作消除了空值。
如果inserted表里面的编号在B里面存在则执行UPDATE
否则执行INSERT
我要求的:编号 类型 工序1开始时间 工序1结束时间 工序2开始时间 工序2结束时间.....
001 A0101 2005-1-1 17:00 2005-1-4 18:00 2005-1-4 19:00 2005-1-10 8:00
002 A0201 2005-1-1 13:00 NULL 2005-1-4 10:00 NULL
003 A0101 2005-1-1 10:00 NULL NULL NULL你的结果:
编号 类型 工序1开始时间 工序1结束时间 工序2开始时间 工序2结束时间
---------- ----- ----------- ----------- ----------- -----------
2005-01-01 A0101 2 1 1 1
2005-01-01 A0201 1 0 1 0 因为: 工序1在2005-01-01结束一个都没有,应为0呀应该为
编号 类型 工序1开始时间 工序1结束时间 工序2开始时间 工序2结束时间
---------- ----- ----------- ----------- ----------- -----------
2005-01-01 A0101 2 0 0 0
2005-01-01 A0201 1 0 1 0
2005-01-04 A0101 0 1 0 0
2005-01-04 A0201 0 0 1 0
2005-01-10 A0101 0 0 0 1也就是我要求所有的开始时间按时间排,自动生成如2005-01-01有哪个工序开始了几个?哪个工序结束了几个?如果那天在任一个工序都没有时间相同,则不生成(如星期六;天).
on a
for insert,delete,update
as
update b
set
from b,(
select 日期,类型,
sum(工序1开始数目) as 工序1开始数目,
sum(工序1结束数目) as 工序1结束数目,
sum(工序1开始数目) as 工序2开始数目,
sum(工序1结束数目) as 工序2结束数目
from (
select convert(varchar(10),工序1开始时间,120) as 日期,类型,
1 as 工序1开始数目,
0 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序1开始时间 is not null
union all
select convert(varchar(10),工序1结束时间,120) as 日期,类型,
0 as 工序1开始时间,
1 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序1结束时间 is not null
union all
select convert(varchar(10),工序2开始时间,120) as 日期,类型,
0 as 工序1开始数目,
0 as 工序1结束数目,
1 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序2开始时间 is not null
union all
select convert(varchar(10),工序2结束时间,120) as 日期,类型,
0 as 工序1开始时间,
0 as 工序1结束数目,
0 as 工序2开始数目,
1 as 工序2结束数目
from inserted
where 工序2结束时间 is not null
union all
select convert(varchar(10),工序1开始时间,120) as 日期,类型,
-1 as 工序1开始数目,
0 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from deleted
where 工序1开始时间 is not null
union all
select convert(varchar(10),工序1结束时间,120) as 日期,类型,
0 as 工序1开始时间,
-1 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from dleted
where 工序1结束时间 is not null
union all
select convert(varchar(10),工序2开始时间,120) as 日期,类型,
0 as 工序1开始数目,
0 as 工序1结束数目,
-1 as 工序2开始数目,
0 as 工序2结束数目
from deleted
where 工序2开始时间 is not null
union all
select convert(varchar(10),工序2结束时间,120) as 日期,类型,
0 as 工序1开始时间,
0 as 工序1结束数目,
0 as 工序2开始数目,
-1 as 工序2结束数目
from deleted
where 工序2结束时间 is not null
) as t1
group by 日期,类型
) as t
where b.日期=t.日期
and b.类型=t.类型
insert into b(
日期,类型,
工序1开始数目,
工序1结束数目,
工序2开始数目,
工序2结束数目
)
select 日期,类型,
sum(工序1开始数目) as 工序1开始数目,
sum(工序1结束数目) as 工序1结束数目,
sum(工序1开始数目) as 工序2开始数目,
sum(工序1结束数目) as 工序2结束数目
from (
select convert(varchar(10),工序1开始时间,120) as 日期,类型,
1 as 工序1开始数目,
0 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序1开始时间 is not null
union all
select convert(varchar(10),工序1结束时间,120) as 日期,类型,
0 as 工序1开始时间,
1 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序1结束时间 is not null
union all
select convert(varchar(10),工序2开始时间,120) as 日期,类型,
0 as 工序1开始数目,
0 as 工序1结束数目,
1 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序2开始时间 is not null
union all
select convert(varchar(10),工序2结束时间,120) as 日期,类型,
0 as 工序1开始时间,
0 as 工序1结束数目,
0 as 工序2开始数目,
1 as 工序2结束数目
from inserted
where 工序2结束时间 is not null
union all
select convert(varchar(10),工序1开始时间,120) as 日期,类型,
-1 as 工序1开始数目,
0 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from deleted
where 工序1开始时间 is not null
union all
select convert(varchar(10),工序1结束时间,120) as 日期,类型,
0 as 工序1开始时间,
-1 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from dleted
where 工序1结束时间 is not null
union all
select convert(varchar(10),工序2开始时间,120) as 日期,类型,
0 as 工序1开始数目,
0 as 工序1结束数目,
-1 as 工序2开始数目,
0 as 工序2结束数目
from deleted
where 工序2开始时间 is not null
union all
select convert(varchar(10),工序2结束时间,120) as 日期,类型,
0 as 工序1开始时间,
0 as 工序1结束数目,
0 as 工序2开始数目,
-1 as 工序2结束数目
from deleted
where 工序2结束时间 is not null
) as t1
where not exists (
select 1 from b
where b.日期=t.日期
and b.类型=t.类型
)
group by 日期,类型go--字段有点复杂,未测试,有问题随时提出
on a
for insert,delete,update
as
update b
set
工序1开始数目=b.工序1开始数目+t.工序1开始数目,
工序1结束数目=b.工序1结束数目+t.工序1结束数目,
工序2开始数目=b.工序2开始数目+t.工序2开始数目,
工序2结束数目=b.工序2结束数目+t.工序2结束数目
from b,(
select 日期,类型,
sum(工序1开始数目) as 工序1开始数目,
sum(工序1结束数目) as 工序1结束数目,
sum(工序1开始数目) as 工序2开始数目,
sum(工序1结束数目) as 工序2结束数目
from (
select convert(varchar(10),工序1开始时间,120) as 日期,类型,
1 as 工序1开始数目,
0 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序1开始时间 is not null
union all
select convert(varchar(10),工序1结束时间,120) as 日期,类型,
0 as 工序1开始时间,
1 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序1结束时间 is not null
union all
select convert(varchar(10),工序2开始时间,120) as 日期,类型,
0 as 工序1开始数目,
0 as 工序1结束数目,
1 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序2开始时间 is not null
union all
select convert(varchar(10),工序2结束时间,120) as 日期,类型,
0 as 工序1开始时间,
0 as 工序1结束数目,
0 as 工序2开始数目,
1 as 工序2结束数目
from inserted
where 工序2结束时间 is not null
union all
select convert(varchar(10),工序1开始时间,120) as 日期,类型,
-1 as 工序1开始数目,
0 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from deleted
where 工序1开始时间 is not null
union all
select convert(varchar(10),工序1结束时间,120) as 日期,类型,
0 as 工序1开始时间,
-1 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from dleted
where 工序1结束时间 is not null
union all
select convert(varchar(10),工序2开始时间,120) as 日期,类型,
0 as 工序1开始数目,
0 as 工序1结束数目,
-1 as 工序2开始数目,
0 as 工序2结束数目
from deleted
where 工序2开始时间 is not null
union all
select convert(varchar(10),工序2结束时间,120) as 日期,类型,
0 as 工序1开始时间,
0 as 工序1结束数目,
0 as 工序2开始数目,
-1 as 工序2结束数目
from deleted
where 工序2结束时间 is not null
) as t1
group by 日期,类型
) as t
where b.日期=t.日期
and b.类型=t.类型
insert into b(
日期,类型,
工序1开始数目,
工序1结束数目,
工序2开始数目,
工序2结束数目
)
select 日期,类型,
sum(工序1开始数目) as 工序1开始数目,
sum(工序1结束数目) as 工序1结束数目,
sum(工序1开始数目) as 工序2开始数目,
sum(工序1结束数目) as 工序2结束数目
from (
select convert(varchar(10),工序1开始时间,120) as 日期,类型,
1 as 工序1开始数目,
0 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序1开始时间 is not null
union all
select convert(varchar(10),工序1结束时间,120) as 日期,类型,
0 as 工序1开始时间,
1 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序1结束时间 is not null
union all
select convert(varchar(10),工序2开始时间,120) as 日期,类型,
0 as 工序1开始数目,
0 as 工序1结束数目,
1 as 工序2开始数目,
0 as 工序2结束数目
from inserted
where 工序2开始时间 is not null
union all
select convert(varchar(10),工序2结束时间,120) as 日期,类型,
0 as 工序1开始时间,
0 as 工序1结束数目,
0 as 工序2开始数目,
1 as 工序2结束数目
from inserted
where 工序2结束时间 is not null
union all
select convert(varchar(10),工序1开始时间,120) as 日期,类型,
-1 as 工序1开始数目,
0 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from deleted
where 工序1开始时间 is not null
union all
select convert(varchar(10),工序1结束时间,120) as 日期,类型,
0 as 工序1开始时间,
-1 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from dleted
where 工序1结束时间 is not null
union all
select convert(varchar(10),工序2开始时间,120) as 日期,类型,
0 as 工序1开始数目,
0 as 工序1结束数目,
-1 as 工序2开始数目,
0 as 工序2结束数目
from deleted
where 工序2开始时间 is not null
union all
select convert(varchar(10),工序2结束时间,120) as 日期,类型,
0 as 工序1开始时间,
0 as 工序1结束数目,
0 as 工序2开始数目,
-1 as 工序2结束数目
from deleted
where 工序2结束时间 is not null
) as t1
where not exists (
select 1 from b
where b.日期=t.日期
and b.类型=t.类型
)
group by 日期,类型go--字段有点复杂,未测试,有问题随时提出
什么时候把裤衩脱掉了?也不散点分,真不HD~
asselect 日期,类型,
sum(工序1开始数目) as 工序1开始数目,
sum(工序1结束数目) as 工序1结束数目,
sum(工序1开始数目) as 工序2开始数目,
sum(工序1结束数目) as 工序2结束数目
from (
select convert(varchar(10),工序1开始时间,120) as 日期,类型,
1 as 工序1开始数目,
0 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from test
where 工序1开始时间 is not null
union all
select convert(varchar(10),工序1结束时间,120) as 日期,类型,
0 as 工序1开始时间,
1 as 工序1结束数目,
0 as 工序2开始数目,
0 as 工序2结束数目
from test
where 工序1结束时间 is not null
union all
select convert(varchar(10),工序2开始时间,120) as 日期,类型,
0 as 工序1开始数目,
0 as 工序1结束数目,
1 as 工序2开始数目,
0 as 工序2结束数目
from test
where 工序2开始时间 is not null
union all
select convert(varchar(10),工序2结束时间,120) as 日期,类型,
0 as 工序1开始时间,
0 as 工序1结束数目,
0 as 工序2开始数目,
1 as 工序2结束数目
from test
where 工序2结束时间 is not null
) as t
group by 日期,类型go
--哦,看错了,只有原来的1/4长度
测试结果如下
2005-01-01 A0101 2 0 2 0
2005-01-04 A0101 0 1 0 1
2005-01-10 A0101 0 0 0 0
2005-01-01 A0201 1 0 1 0
2005-01-04 A0201 0 0 0 0
实际应该是
编号 类型 工序1开始时间 工序1结束时间 工序2开始时间 工序2结束时间
---------- ----- ----------- ----------- ----------- -----------
2005-01-01 A0101 2 0 0 0
2005-01-01 A0201 1 0 1 0
2005-01-04 A0101 0 1 0 0
2005-01-04 A0201 0 0 1 0
2005-01-10 A0101 0 0 0 1
sum(工序1开始数目) as 工序1开始数目,
sum(工序1结束数目) as 工序1结束数目,
sum(工序1开始数目) as 工序2开始数目,
sum(工序1结束数目) as 工序2结束数目应改为
select 日期,类型,
sum(工序1开始数目) as 工序1开始数目,
sum(工序1结束数目) as 工序1结束数目,
sum(工序2开始数目) as 工序2开始数目,
sum(工序2结束数目) as 工序2结束数目to:
libin_ftsate(冒牌子陌红尘:当libin告别ftsafe V0.1)
基本正确,真是高手呀!!!
---------- ----- ----------- ----------- ----------- -----------
2005-01-01 A0101 2 0 0 0
2005-01-01 A0201 1 0 1 0
2005-01-04 A0101 0 1 0 0
2005-01-04 A0201 0 0 1 0
2005-01-10 A0101 0 0 0 1
如果要加入一个以前的一个余收如何编写(加一个字段:工序1上期余额)
如 2005-01-04 之前的开始时间数目-工序1结束的数目怎么搞?
视图:
create View V_ta
as
select b.日期,b.类型,max(b.工序1开始数目) 工序1开始数目,
max(b.工序1结束数目) 工序1结束数目,
max(b.工序2开始数目) 工序2开始数目,max(b.工序2结束数目) 工序2结束数目
from (
select a.类型,Convert(varchar(10),a.日期,120) as 日期,
case when a.工序='工序1开始数目' then count(a.日期) else 0 end as [工序1开始数目],
case when a.工序='工序1结束数目' then count(a.日期) else 0 end as [工序1结束数目],
case when a.工序='工序2开始数目' then count(a.日期) else 0 end as [工序2开始数目],
case when a.工序='工序2结束数目' then count(a.日期) else 0 end as [工序2结束数目]
from (
select 类型,工序1开始时间 as 日期,'工序1开始数目' as 工序 from ta
union all
select 类型,工序1结束时间 as 日期,'工序1结束数目' as 工序 from ta
union all
select 类型,工序2开始时间 as 日期,'工序2开始数目' as 工序 from ta
union all
select 类型,工序2结束时间 as 日期,'工序2结束数目' as 工序 from ta
) a where a.日期 is not null
group by a.类型,Convert(varchar(10),a.日期,120),a.工序
) b
group by b.类型,b.日期go
select * from V_ta
order by 类型
你的写法可以只写两层的,多一层数据量大灰慢很多的create View V_ta
as
select a.类型,Convert(varchar(10),a.日期,120) as 日期,
count(case when a.工序='工序1开始数目' then a.日期 else null end) as [工序1开始数目],
count(case when a.工序='工序1结束数目' then a.日期 else null end) as [工序1结束数目],
count(case when a.工序='工序2开始数目' then a.日期 else null end) as [工序2开始数目],
count(case when a.工序='工序2结束数目' then a.日期 else null end) as [工序2结束数目]
from (
select 类型,工序1开始时间 as 日期,'工序1开始数目' as 工序 from ta
where 工序1开始时间 is not null
union all
select 类型,工序1结束时间 as 日期,'工序1结束数目' as 工序 from ta
where 工序1结束时间 is not null
union all
select 类型,工序2开始时间 as 日期,'工序2开始数目' as 工序 from ta
where 工序2开始时间 is not null
union all
select 类型,工序2结束时间 as 日期,'工序2结束数目' as 工序 from ta
where 工序2结束时间 is not null
) a
group by a.类型,a.日期go
select * from V_ta
order by 类型