望各位高手打救一下,先谢了。--规则:
--同一个id流程下,n为步骤数(递增且连续),users为用户,choice为选项,task为任务名,type1为类型1,type2为类型2(类型在这里只用于区分)
--task流程为:start->task1->task2->review->review2(可能没)->end (task2的上一任务为task1,review的上一任务为task2)
--每个task都有不同的用户,如果choice选择'拒绝'那么上一任务的人就被拒绝了,任务流程返回上一任务
--任务流程中可能换人,如果user3提交的任务被拒绝了,但退回给user7,该拒绝数算user3的
--注意:只能拒绝上一任务,不能拒绝比自己后的任务,如review2拒绝了review后,review再选拒绝,那么此时被拒绝的是task2的人--原始数据
declare @tb table (id nvarchar(20),type1 nvarchar(20),type2 nvarchar(20),n int,users nvarchar(20),choice nvarchar(20),task nvarchar(20))
insert into @tb
select '1','a','s1','1','user1',null,'start' union all
select '1','a','s1','2','user3','通过','task1' union all
select '1','a','s1','3','user4','拒绝','task2' union all
select '1','a','s1','4','user3','通过','task1' union all
select '1','a','s1','5','user4','拒绝','task2' union all
select '1','a','s1','6','user3','通过','task1' union all
select '1','a','s1','7','user4','通过','task2' union all
select '1','a','s1','8','user2','拒绝','review' union all
select '1','a','s1','9','user4','拒绝','task2' union all
select '1','a','s1','10','user3','通过','task1' union all
select '1','a','s1','11','user4','通过','task2' union all
select '1','a','s1','12','user2','通过','review' union all
select '1','a','s1','13',null,null,'end' union allselect '1','a','s2','1','user1',null,'start' union all
select '1','a','s2','2','user3','通过','task1' union all
select '2','a','s2','3','user4','拒绝','task2' union all
select '2','a','s2','4','user7','通过','task1' union all--换人了
select '2','a','s2','5','user8','拒绝','task2' union all--换人了
select '2','a','s2','6','user7','通过','task1' union all
select '2','a','s2','7','user8','通过','task2' union all
select '2','a','s2','8','user6','拒绝','review' union all
select '2','a','s2','9','user4','通过','task2' union all--换人了 union all
select '2','a','s2','10','user6','拒绝','review' union all
select '2','a','s2','11','user4','通过','task2' union all
select '2','a','s2','12','user6','通过','review' union all
select '2','a','s2','13',null,null,'end' union allselect '3','a','s1','1','user1',null,'start' union all
select '3','a','s1','2','user3','通过','task1' union all
select '3','a','s1','3','user4','通过','task2' union all
select '3','a','s1','4','user5','通过','review' union all
select '3','a','s1','5','user1','拒绝','review2' union all--review2
select '3','a','s1','6','user5','通过','review' union all
select '3','a','s1','7','user1','通过','review2' union all--review2
select '3','a','s1','8',null,null,'end' union allselect '4','b','s2','1','user5',null,'start' union all
select '4','b','s2','2','user7','通过','task1' union all
select '4','b','s2','3','user8','通过','task2' union all
select '4','b','s2','4','user6','通过','review' union all
select '4','b','s2','5','user5','拒绝','review2' union all--review2
select '4','b','s2','6','user6','通过','review' union all
select '4','b','s2','7','user5','通过','review2' union all--review2
select '4','b','s2','8',null,null,'end'select * from @tb-----------------------
--需要得到的目标数据
--t1user为task1中被拒绝的用户,t1n为该用户被拒绝次数
--同理t2为task2,t3为review(review2不作统计)
--按type1,type2,t1user,t2user,t3user划分出不同的行,也就是group by
declare @tb_result table (type1 nvarchar(20), type2 nvarchar(20),t1user nvarchar(20), t1n int, t2user nvarchar(20),t2n int,t3 nvarchar(20), t3n int)
insert into @tb_result
select 'a','s1','user3','1','user4','1','user2','1' union all
select 'a','s2','user3','1','user4','0','user6','0' union all
select 'a','s2','user3','1','user8','1','user6','0' union all
select 'a','s2','user7','1','user4','0','user6','0' union all
select 'a','s2','user7','1','user8','1','user6','0' union all
select 'b','s2','user7','0','user8','0','user6','1'
select * from @tb_result
--同一个id流程下,n为步骤数(递增且连续),users为用户,choice为选项,task为任务名,type1为类型1,type2为类型2(类型在这里只用于区分)
--task流程为:start->task1->task2->review->review2(可能没)->end (task2的上一任务为task1,review的上一任务为task2)
--每个task都有不同的用户,如果choice选择'拒绝'那么上一任务的人就被拒绝了,任务流程返回上一任务
--任务流程中可能换人,如果user3提交的任务被拒绝了,但退回给user7,该拒绝数算user3的
--注意:只能拒绝上一任务,不能拒绝比自己后的任务,如review2拒绝了review后,review再选拒绝,那么此时被拒绝的是task2的人--原始数据
declare @tb table (id nvarchar(20),type1 nvarchar(20),type2 nvarchar(20),n int,users nvarchar(20),choice nvarchar(20),task nvarchar(20))
insert into @tb
select '1','a','s1','1','user1',null,'start' union all
select '1','a','s1','2','user3','通过','task1' union all
select '1','a','s1','3','user4','拒绝','task2' union all
select '1','a','s1','4','user3','通过','task1' union all
select '1','a','s1','5','user4','拒绝','task2' union all
select '1','a','s1','6','user3','通过','task1' union all
select '1','a','s1','7','user4','通过','task2' union all
select '1','a','s1','8','user2','拒绝','review' union all
select '1','a','s1','9','user4','拒绝','task2' union all
select '1','a','s1','10','user3','通过','task1' union all
select '1','a','s1','11','user4','通过','task2' union all
select '1','a','s1','12','user2','通过','review' union all
select '1','a','s1','13',null,null,'end' union allselect '1','a','s2','1','user1',null,'start' union all
select '1','a','s2','2','user3','通过','task1' union all
select '2','a','s2','3','user4','拒绝','task2' union all
select '2','a','s2','4','user7','通过','task1' union all--换人了
select '2','a','s2','5','user8','拒绝','task2' union all--换人了
select '2','a','s2','6','user7','通过','task1' union all
select '2','a','s2','7','user8','通过','task2' union all
select '2','a','s2','8','user6','拒绝','review' union all
select '2','a','s2','9','user4','通过','task2' union all--换人了 union all
select '2','a','s2','10','user6','拒绝','review' union all
select '2','a','s2','11','user4','通过','task2' union all
select '2','a','s2','12','user6','通过','review' union all
select '2','a','s2','13',null,null,'end' union allselect '3','a','s1','1','user1',null,'start' union all
select '3','a','s1','2','user3','通过','task1' union all
select '3','a','s1','3','user4','通过','task2' union all
select '3','a','s1','4','user5','通过','review' union all
select '3','a','s1','5','user1','拒绝','review2' union all--review2
select '3','a','s1','6','user5','通过','review' union all
select '3','a','s1','7','user1','通过','review2' union all--review2
select '3','a','s1','8',null,null,'end' union allselect '4','b','s2','1','user5',null,'start' union all
select '4','b','s2','2','user7','通过','task1' union all
select '4','b','s2','3','user8','通过','task2' union all
select '4','b','s2','4','user6','通过','review' union all
select '4','b','s2','5','user5','拒绝','review2' union all--review2
select '4','b','s2','6','user6','通过','review' union all
select '4','b','s2','7','user5','通过','review2' union all--review2
select '4','b','s2','8',null,null,'end'select * from @tb-----------------------
--需要得到的目标数据
--t1user为task1中被拒绝的用户,t1n为该用户被拒绝次数
--同理t2为task2,t3为review(review2不作统计)
--按type1,type2,t1user,t2user,t3user划分出不同的行,也就是group by
declare @tb_result table (type1 nvarchar(20), type2 nvarchar(20),t1user nvarchar(20), t1n int, t2user nvarchar(20),t2n int,t3 nvarchar(20), t3n int)
insert into @tb_result
select 'a','s1','user3','1','user4','1','user2','1' union all
select 'a','s2','user3','1','user4','0','user6','0' union all
select 'a','s2','user3','1','user8','1','user6','0' union all
select 'a','s2','user7','1','user4','0','user6','0' union all
select 'a','s2','user7','1','user8','1','user6','0' union all
select 'b','s2','user7','0','user8','0','user6','1'
select * from @tb_result
--同一个id流程下,n为步骤数(递增且连续),users为用户,choice为选项,task为任务名,type1,type2为类型(类型在这里只用于区分)
--task流程为:start->task1->task2->review->review2(不一定每个流程都有review2)->end
--例如:task2的上一任务为task1,review的上一任务为task2
--每个task都有不同的用户去处理,如果choice选择'拒绝'那么上一任务的人就被拒绝了
--被拒绝后任务流程返回上一任务,如review选择了拒绝,下一个要处理的任务为task2
--任务流程中可能换人,如果user3提交的任务被拒绝了,但退回给user7,该拒绝数算user3的
--注意:只能拒绝上一任务,不能拒绝比自己后的任务,如review2拒绝了review后,review再选拒绝,那么此时被拒绝的是task2的人--原始数据
declare @tb table (id nvarchar(20),type1 nvarchar(20),type2 nvarchar(20),n int,users nvarchar(20),choice nvarchar(20),task nvarchar(20))
insert into @tb
select '1','a','s1','1','user1',null,'start' union all
select '1','a','s1','2','user3','通过','task1' union all
select '1','a','s1','3','user4','拒绝','task2' union all
select '1','a','s1','4','user3','通过','task1' union all
select '1','a','s1','5','user4','拒绝','task2' union all
select '1','a','s1','6','user3','通过','task1' union all
select '1','a','s1','7','user4','通过','task2' union all
select '1','a','s1','8','user2','拒绝','review' union all
select '1','a','s1','9','user4','拒绝','task2' union all
select '1','a','s1','10','user3','通过','task1' union all
select '1','a','s1','11','user4','通过','task2' union all
select '1','a','s1','12','user2','通过','review' union all
select '1','a','s1','13',null,null,'end' union allselect '1','a','s2','1','user1',null,'start' union all
select '1','a','s2','2','user3','通过','task1' union all
select '2','a','s2','3','user4','拒绝','task2' union all
select '2','a','s2','4','user7','通过','task1' union all--换人了
select '2','a','s2','5','user8','拒绝','task2' union all--换人了
select '2','a','s2','6','user7','通过','task1' union all
select '2','a','s2','7','user8','通过','task2' union all
select '2','a','s2','8','user6','拒绝','review' union all
select '2','a','s2','9','user4','通过','task2' union all--换人了 union all
select '2','a','s2','10','user6','拒绝','review' union all
select '2','a','s2','11','user4','通过','task2' union all
select '2','a','s2','12','user6','通过','review' union all
select '2','a','s2','13',null,null,'end' union allselect '3','a','s1','1','user1',null,'start' union all
select '3','a','s1','2','user3','通过','task1' union all
select '3','a','s1','3','user4','通过','task2' union all
select '3','a','s1','4','user5','通过','review' union all
select '3','a','s1','5','user1','拒绝','review2' union all--review2
select '3','a','s1','6','user5','通过','review' union all
select '3','a','s1','7','user1','通过','review2' union all--review2
select '3','a','s1','8',null,null,'end' union allselect '4','b','s2','1','user5',null,'start' union all
select '4','b','s2','2','user7','通过','task1' union all
select '4','b','s2','3','user8','通过','task2' union all
select '4','b','s2','4','user6','通过','review' union all
select '4','b','s2','5','user5','拒绝','review2' union all--review2
select '4','b','s2','6','user6','通过','review' union all
select '4','b','s2','7','user5','通过','review2' union all--review2
select '4','b','s2','8',null,null,'end'select * from @tb-----------------------
--需要得到的目标数据
--t1user为task1中被拒绝的用户,t1n为该用户被拒绝次数(指被task2的人拒绝)
--t2user为task2中被拒绝的用户,t2n为该用户被拒绝次数(指被review的人拒绝)
--t3user为review中被拒绝的用户,t3n为该用户被拒绝次数(指被review2的人拒绝)
--按type1,type2,t1user,t2user,t3user划分出不同的行,也就是group by
declare @tb_result table (type1 nvarchar(20), type2 nvarchar(20),t1user nvarchar(20), t1n int, t2user nvarchar(20),t2n int,t3user nvarchar(20), t3n int)
insert into @tb_result
select 'a','s1','user3','1','user4','1','user2','1' union all
select 'a','s2','user3','1','user4','0','user6','0' union all
select 'a','s2','user3','1','user8','1','user6','0' union all
select 'a','s2','user7','1','user4','0','user6','0' union all
select 'a','s2','user7','1','user8','1','user6','0' union all
select 'b','s2','user7','0','user8','0','user6','1'
select * from @tb_result
很客观的说,我近呼神级的逻辑分析能力, 都看不懂你说的是什么.
select '1','a','s1','1','user1',null,'start' union all
select '1','a','s1','2','user3','通过','task1' union all
select '1','a','s1','3','user4','拒绝','task2' union all
第三条记录,是user4拒绝了user3的task1, 还是user4的task2被别人拒绝了? 这么重要的说明都没有。
从你的数据结果来看,我倾向于前面的理解方法,即user4拒绝了user3的task1
按start->task1->task2->review->review2->end的顺序执行,中途可能通过也可能拒绝,choice就是存储通过或拒绝的这个选项,users为此时提交的人,不管通过或拒绝n都加1,就是这样产生了一条新的数据,多次后变形成了原始的数据表。而现在要的是
目标表:
type1,type2,task1中被拒绝的用户,被拒绝次数,task2中被拒绝的用户,被拒绝次数,review
a,s1,user3,1,user4,1,user2,0
...
--原始数据
declare @tb table (id nvarchar(20),type1 nvarchar(20),type2 nvarchar(20),n int,users nvarchar(20),choice nvarchar(20),task nvarchar(20))
insert into @tb
select '1','a','s1','1','user1',null,'start' union all--任务开始
select '1','a','s1','2','user3','通过','task1' union all--user3提交任务
select '1','a','s1','3','user4','拒绝','task2' union all--user3被task2中的user4拒绝
select '1','a','s1','4','user3','通过','task1' union all--user3再次提交
select '1','a','s1','5','user4','拒绝','task2' union all--user3再次被user4拒绝
select '1','a','s1','6','user3','通过','task1' union all--user3再次提交
select '1','a','s1','7','user4','通过','task2' union all--user4满意,选择了通过
select '1','a','s1','8','user2','拒绝','review' union all--user4被user2拒绝了,返回task2
select '1','a','s1','9','user4','拒绝','task2' union all--user4被老大拒绝后,认为是task1的人的错,再次拒绝,此时被拒绝的是最初的user3
select '1','a','s1','10','user3','通过','task1' union all--user3再次提交
select '1','a','s1','11','user4','通过','task2' union all--user4满意,通过
select '1','a','s1','12','user2','通过','review' union all--user2满意,通过
select '1','a','s1','13',null,null,'end' union all--结束
...
select '2','a','s2','2','user3','通过','task1' union all--user3努力的做完后,提交
select '2','a','s2','3','user4','拒绝','task2' union all--user3被user4狠狠的拒绝了
select '2','a','s2','4','user7','通过','task1' union all--user3无法胜任工作,task1的工作改为user7进行,user7做完后提交了
select '2','a','s2','5','user8','拒绝','task2' union all--当初task2的user4出差了,改为user8来审核,可惜不通过,所以user7被user8拒绝了
select '2','a','s2','6','user7','通过','task1' union all--user7再次提交
select '2','a','s2','7','user8','通过','task2' union all--这次user8满意,通过了
select '2','a','s2','8','user6','拒绝','review' union all--user8被老大user6狠狠拒绝了
select '2','a','s2','9','user4','通过','task2' union all--user8不干了,改由user4继续task2的任务,user4稍微改了下,提交
select '2','a','s2','10','user6','拒绝','review' union all--不幸user4也被user6拒绝了
select '2','a','s2','11','user4','通过','task2' union all--user4再次提交
select '2','a','s2','12','user6','通过','review' union all--user6终于满意,通过
select '2','a','s2','13',null,null,'end' union all--任务结束
select t.ID, t.type1, t.type2, t1.t1user as 't1user', t1.t1n as 't1n'
from tb t
right join(
select user as t1user, count(*) as t1n
from tb
where task = task2 and choice = '拒绝'
group by user
)t1
on t.user = t1.t1user
right join(
select user as t2user, count(*) as t2n
from tb
where task = review and choice = '拒绝'
)t2
on t.user = t2.t2user
right join(
select user as t3user, count(*) as t3n
from tb
where task = review2 and choice = '拒绝'
)t3
on t.user = t3.t3user
from tb t
right join(
select user as t1user, count(*) as t1n
from tb
where task = task2 and choice = '拒绝'
group by user
)t1
on t.user = t1.t1user
right join(
select user as t2user, count(*) as t2n
from tb
where task = review and choice = '拒绝'
)t2
on t.user = t2.t2user
right join(
select user as t3user, count(*) as t3n
from tb
where task = review2 and choice = '拒绝'
)t3
on t.user = t3.t3user没有装SQL Server无法验证结果,只能看你的结果了
from tb t
right join(
select ID, user as t1user, count(*) as t1n
from tb
where task = task2 and choice = '拒绝'
group by ID, user
)t1
on t.ID= t1.ID
right join(
select ID, user as t2user, count(*) as t2n
from tb
where task = review and choice = '拒绝'
)t2
on t.ID = t2.ID
right join(
select ID, user as t3user, count(*) as t3n
from tb
where task = review2 and choice = '拒绝'
)t3
on t.ID = t3.ID
select c.type1,c.type2,c.[1],sum(d.[1]),c.[2],sum(d.[2]),c.[3],sum(d.[3]) from
(select cid,type1,type2,[1],[2],[3]
from
(select NTILE(1) over (order by getdate()) as cid,type1,type2,users,task from temp where ID=5) a
pivot (max(users) for task in ([1],[2],[3])) b) c
join
(select cid,type1,type2,[1],[2],[3]
from (select NTILE(1) over (order by getdate()) as cid,type1,type2,cou,task from temp where ID=5) a
pivot (max(cou) for task in ([1],[2],[3])) b) d
on c.cid=d.cid
group by c.type1,c.type2,c.[1],c.[2],c.[3]id=1的时候type1 type2 1 2 3
-------------------- -------------------- -------------------- ----------- -------------------- ----------- -------------------- -----------
a s1 user3 1 user4 1 user2 0(1 行受影响)id=2的时候
type1 type2 1 2 3
-------------------- -------------------- -------------------- ----------- -------------------- ----------- -------------------- -----------
a s2 user3 1 user4 0 user6 0
a s2 user7 1 user8 1 user6 0(2 行受影响)id=3
type1 type2 1 2 3
-------------------- -------------------- -------------------- ----------- -------------------- ----------- -------------------- -----------
a s1 user3 0 user4 0 user5 1(1 行受影响)id=4
type1 type2 1 2 3
-------------------- -------------------- -------------------- ----------- -------------------- ----------- -------------------- -----------
b s2 user7 0 user8 0 user6 1(1 行受影响)id=5type1 type2 1 2 3
-------------------- -------------------- -------------------- ----------- -------------------- ----------- -------------------- -----------
a s1 user3 1 user4 0 user5 0(1 行受影响)