if OBJECT_ID ('tb') is not null
drop table tb
go
if OBJECT_ID ('tb1') is not null
drop table tb1
go
create table tb (worker_name varchar(10), job_name varchar(10))
insert into tb
select '张三','扫地' union all
select '李四','擦桌子' union all
select '张三','扫地'
go
create table tb1 (worker_name varchar(10), job_name varchar(10))
insert into tb1
select '张三','擦桌子' union all
select '李四','扫地' union all
select '王五','擦桌子' union all
select '李四','擦桌'go
select worker_name,
SUM(case when 是否完成=1 then 1 else 0 end ) as [已完成],
SUM(case when 是否完成=0 then 1 else 0 end ) as [未完成]
from
(
select worker_name,job_name ,'0' as 是否完成 from tb
union all
select worker_name,job_name ,'1' as 是否完成 from tb1 )t group by worker_name
--worker_name 已完成 未完成
--李四 2 1
--王五 1 0
--张三 1 2
drop table tb
go
if OBJECT_ID ('tb1') is not null
drop table tb1
go
create table tb (worker_name varchar(10), job_name varchar(10))
insert into tb
select '张三','扫地' union all
select '李四','擦桌子' union all
select '张三','扫地'
go
create table tb1 (worker_name varchar(10), job_name varchar(10))
insert into tb1
select '张三','擦桌子' union all
select '李四','扫地' union all
select '王五','擦桌子' union all
select '李四','擦桌'go
select worker_name,
SUM(case when 是否完成=1 then 1 else 0 end ) as [已完成],
SUM(case when 是否完成=0 then 1 else 0 end ) as [未完成]
from
(
select worker_name,job_name ,'0' as 是否完成 from tb
union all
select worker_name,job_name ,'1' as 是否完成 from tb1 )t group by worker_name
--worker_name 已完成 未完成
--李四 2 1
--王五 1 0
--张三 1 2
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货