还是没搞明白,看看 下面的适合不: /* 把a表中a.pass=0和b表中b.pass=0所对应的a.receiver和b.receiver 一起存入到C表c.receiver中 */declare @a table(receiver char,pass int ,reportNo int) declare @b table(receiver char,pass int ,reportNo int) declare @c table(receiver char(2),pass int )insert @a select 'a',0,2 union all select 'c',0,2 union all select 'd',1,3 union all select 'f',0,3 union all select 'c',0,5 select * from @a ---表@a内容insert @b select 'b',1,1 union all select 'a',0,2 union all select 'e',0,3 union all select 'c',0,4 union all select 'd',1,5select * from @b ---表@b内容insert @c select a.receiver+b.receiver,count(b.pass)+count(b.pass) from @a a left join @b b on b.pass=a.pass where a.pass=0 group by a.receiver,b.receiverselect distinct * from @c --表@c内容 --结果(所影响的行数为 5 行)receiver pass reportNo -------- ----------- ----------- a 0 2 c 0 2 d 1 3 f 0 3 c 0 5(所影响的行数为 5 行) (所影响的行数为 5 行)receiver pass reportNo -------- ----------- ----------- b 1 1 a 0 2 e 0 3 c 0 4 d 1 5(所影响的行数为 5 行) (所影响的行数为 9 行)receiver pass -------- ----------- aa 2 ac 2 ae 2 ca 4 cc 4 ce 4 fa 2 fc 2 fe 2(所影响的行数为 9 行)
insert into c(reveiver,pass) ( select reveiver,pass from a where pass=0 join all select reveiver,pass from b where pass=0)update c set pass=b.pass from c,(select reveiver,count(reveiver) as pass from c group by reveiver) b where c.reveiver=b.revevier
并且把每个c.receiver所对应的pass=0的总数存到c.pass中去-------------------------------------------------------这个总数指什么?
总数就是指distinct receiver后receiver所对应pass=0有几个--------------------------------------------------------还是没有说清楚啊,是a表还是b表的行数,还是两表加起来计算?
a.receiver,a.pass,a.reportNo b.receiver,b.pass,b.reportNo
a 0 1 b 1 1
c 0 2 a 0 2
d 1 3 e 0 3
f 0 4 c 0 4
c 0 5 d 1 5
/*
把a表中a.pass=0和b表中b.pass=0所对应的a.receiver和b.receiver 一起存入到C表c.receiver中
*/declare @a table(receiver char,pass int ,reportNo int)
declare @b table(receiver char,pass int ,reportNo int)
declare @c table(receiver char(2),pass int )insert @a
select 'a',0,2
union all
select 'c',0,2
union all
select 'd',1,3
union all
select 'f',0,3
union all
select 'c',0,5
select * from @a ---表@a内容insert @b
select 'b',1,1
union all
select 'a',0,2
union all
select 'e',0,3
union all
select 'c',0,4
union all
select 'd',1,5select * from @b ---表@b内容insert @c
select a.receiver+b.receiver,count(b.pass)+count(b.pass) from @a a left join @b b on b.pass=a.pass
where a.pass=0
group by a.receiver,b.receiverselect distinct * from @c --表@c内容
--结果(所影响的行数为 5 行)receiver pass reportNo
-------- ----------- -----------
a 0 2
c 0 2
d 1 3
f 0 3
c 0 5(所影响的行数为 5 行)
(所影响的行数为 5 行)receiver pass reportNo
-------- ----------- -----------
b 1 1
a 0 2
e 0 3
c 0 4
d 1 5(所影响的行数为 5 行)
(所影响的行数为 9 行)receiver pass
-------- -----------
aa 2
ac 2
ae 2
ca 4
cc 4
ce 4
fa 2
fc 2
fe 2(所影响的行数为 9 行)
select reveiver,pass from a where pass=0
join all
select reveiver,pass from b where pass=0)update c set pass=b.pass from c,(select reveiver,count(reveiver) as pass from c group by reveiver) b where c.reveiver=b.revevier
a、b表中的receiver分别代表审核人、审批人,pass=0(可取:pass=0或pass=1或pass=2)分别代表的是待审核、待审批的报告,reportNo都代表的是报告代码。
c表数据库中还没有。
我想要做的是设计一种方法把审核人审批人放入一列(当然要不同名)c.receiver,把该人的待审核的报告总数和待审批的报告总数列为两列:a,b
a.reportNo代表报告代码
b.receiver代表审批准书人,b.pass报告状态(当b.pass=0为待审批,当b.pass=1为审批通过)
b.reportNo代表报告代码
设计一张C表有三列:
1.c.receiver=(distinct(a.receiver+b.receiver) 2.这个人所要待审核的报告总数 3.这个人所要待审批的报告总数。
into c
from a,b
where a.reportno = b.reportno
and a.pass = 0
and b.pass = 0