select a.username,sum(case when b.operator='user1' then 1 else 0 end) as operator,sum(case when b.checker='user2' then 1 else 0 end) as checker from user a left join operate b on a.username=b.username group by a.username
调试欢乐多
isnull (a.username,b.username) as username,
isnull (a.operator,0) as operator,
isnull (b.checker,0) as checker
from
(select a.userid ,count(b.operator) as operator
from user a,operate b
where a.username=b.username
group by a.userid
)a full join
(
select a.userid ,count(b.checker) as checker
from user a,operate b
where a.username=b.username
group by a.userid
count(case when then 1 else 0 end)) b
on a.username=b.username
create table #user(
userid int identity(1,1),
username varchar(10)
)
create table #operate(
[id] int identity(1,1),
operator varchar(10),
checker varchar(10)
)
insert into #user select 'user1'
insert into #user select 'user2'insert into #operate select 'user1','user2'
insert into #operate select 'user2','user1'
insert into #operate select 'user1','user1'--select * from #user
select a.username,b.num as operator,c.num as checker from #user a
left join (select operator,count(*) num from #operate group by operator) b on a.username=b.operator
left join (select checker,count(*) num from #operate group by checker) c on a.username=c.checker
drop table #user
drop table #operate
insert into @user select
1 , 'user1' union all select
2 , 'user2'
declare @operate table(id int,operator varchar(10),checker varchar(10))
insert into @operate select 1 , 'user1', 'user2' union all select
2 , 'user2', 'user1' union all select
3 , 'user1' , 'user1'
select a.username,
(select count(*) from @operate where operator=a.username) as Operator,
(select count(*) from @operate where operator=a.username) as checker
from @user a
--测试结果
username Operator checker
---------- ----------- -----------
user1 2 2
user2 1 1(2 row(s) affected)
select
isnull (a.username,b.username) as username,
isnull (a.operator,0) as operator,
isnull (b.checker,0) as checker
from
(select a.userid ,count(b.operator) as operator
from user a,operate b
where a.username=b.username
group by a.userid
)a full join
(
select a.userid ,count(b.checker) as checker
from user a,operate b
where a.username=b.username
group by a.userid) bon a.username=b.username
insert into @user select
1 , 'user1' union all select
2 , 'user2'
declare @operate table(id int,operator varchar(10),checker varchar(10))
insert into @operate select 1 , 'user1', 'user2' union all select
2 , 'user2', 'user1' union all select
3 , 'user1' , 'user1'
select a.username,
(select count(*) from @operate where operator=a.username) as Operator,
(select count(*) from @operate where checker=a.username) as checker
from @user a
--测试结果
username Operator checker
---------- ----------- -----------
user1 2 2
user2 1 1(2 row(s) affected)
create table #user(
userid int identity(1,1),
username varchar(10)
)
create table #operate(
[id] int identity(1,1),
operator varchar(10),
checker varchar(10)
)
insert into #user select 'user1'
insert into #user select 'user2'
insert into #user select 'user3'
insert into #user select 'user4'
insert into #user select 'user5'insert into #operate select 'user1','user2'
insert into #operate select 'user2','user1'
insert into #operate select 'user1','user3'
insert into #operate select 'user1','user1'
insert into #operate select 'user2','user2'
insert into #operate select 'user3','user1'
insert into #operate select 'user4','user4'
insert into #operate select 'user3','user3'
insert into #operate select 'user4','user2'--select * from #user
select a.username,b.num as operator,c.num as checker from #user a
left join (select operator,count(*) num from #operate group by operator) b on a.username=b.operator
left join (select checker,count(*) num from #operate group by checker) c on a.username=c.checker
drop table #user
drop table #operate
/*结果是
username operator checker
---------- ----------- -----------
user1 3 3
user2 2 3
user3 2 2
user4 2 1
user5 NULL NULL
*/
,operator=isnull(b.operator,0)
,checker=isnull(c.checker,0)
from [user] a
left join(
select username,operator=count(*)
from operate
group by username
)b on a.username=b.username
left join(
select username,checker=count(*)
from operate
group by username
)c on a.username=c.username
select a.username
,operator=isnull(b.operator,0)
,checker=isnull(c.checker,0)
from [user] a
left join(
select username=operator,operator=count(*)
from operate
group by operator
)b on a.username=b.username
left join(
select username=checker,checker=count(*)
from operate
group by checker
)c on a.username=c.username
from [user] A
LEFT JOIN (select operator username,sum(1) opertator from operate group by operator) B
ON A.username=B.username
LEFT JOIN (select checker username,sum(1) checker from operate group by checker ) C
ON A.username=C.username
Insert #t(Username) select operator from operate group by operator
Insert #t(username) select checker from operate
where operate not in (select username from #t) group by checker
declare @max int, @Min int, @o int, @Username varchar(20)
select @max = Max(id), @min = min(id) from #t
while @min <= @max
begin
Select @Username = Username From #t where id = @min
select @o = count(operator) From operate Where operator = @Username
update #t set operator = @o where id = @min
Select @o = count(checker) From operate Where checker = @Username
update #t set operator = @o where id = @min
set @min = @min + 1
end
create table #user(
userid int identity(1,1),
username varchar(10)
)
create table #operate(
[id] int identity(1,1),
operator varchar(10),
checker varchar(10)
)
insert into #user select 'user1'
insert into #user select 'user2'
insert into #user select 'user3'
insert into #user select 'user4'
insert into #user select 'user5'insert into #operate select 'user1','user2'
insert into #operate select 'user2','user1'
insert into #operate select 'user1','user3'
insert into #operate select 'user1','user1'
insert into #operate select 'user2','user2'
insert into #operate select 'user3','user1'
insert into #operate select 'user4','user4'
insert into #operate select 'user3','user3'
insert into #operate select 'user4','user2'--查询
select a.username
,operator=isnull(b.operator,0)
,checker=isnull(c.checker,0)
from #user a
left join(
select username=operator,operator=count(*)
from #operate
group by operator
)b on a.username=b.username
left join(
select username=checker,checker=count(*)
from #operate
group by checker
)c on a.username=c.username
go--删除测试
drop table #user,#operate/*--测试结果username operator checker
---------- ----------- -----------
user1 3 3
user2 2 3
user3 2 2
user4 2 1
user5 0 0(所影响的行数为 5 行)
--*/
Insert #t(Username) select operator from operate group by operator
Insert #t(username) select checker from operate
where operate not in (select username from #t) group by checker
declare @max int, @Min int, @o int, @Username varchar(20)
select @max = Max(id), @min = min(id) from #t
while @min <= @max
begin
Select @Username = Username From #t where id = @min
select @o = count(operator) From operate Where operator = @Username
update #t set operator = @o where id = @min
Select @o = count(checker) From operate Where checker = @Username
update #t set checker= @o where id = @min
set @min = @min + 1
endSelect * From #t
drop table #t
Insert #t(Username) select operator from operate group by operator
Insert #t(username) select checker from operate
where checker not in (select username from #t) group by checker
declare @max int, @Min int, @o int, @Username varchar(20)
select @max = Max(id), @min = min(id) from #t
while @min <= @max
begin
Select @Username = Username From #t where id = @min
select @o = count(operator) From operate Where operator = @Username
update #t set operator = @o where id = @min
Select @o = count(checker) From operate Where checker = @Username
update #t set checker= @o where id = @min
set @min = @min + 1
endSelect * From #t
drop table #t老是写错。