110,566
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tempdb..#xxdm') is not null
drop table #xxdm
create table #xxdm
(
dm nvarchar(8),
mc nvarchar(2)
)
if OBJECT_ID('tempdb..#cbmxx') is not null
drop table #cbmxx
create table #cbmxx
(
cbh nvarchar(12),
clqxx nvarchar(3)
)
insert #xxdm values ('10010101', '小学')
insert #xxdm values ('10010102', '中学')
insert #xxdm values ('10010103', '大学')
select * from #xxdm
insert #cbmxx values ('100101010001', '录用')
insert #cbmxx values ('100101010002', '录用')
insert #cbmxx values ('100101010003', '不合格')
insert #cbmxx values ('100101020001', '不合格')
insert #cbmxx values ('100101020001', '不合格')
select * from #cbmxx
select x.mc as '学校',COUNT(1) as '报名人数',SUM(case when clqxx='录用' then 1 else 0 end) as '录用人数' from #xxdm x join #cbmxx c on x.dm=LEFT(c.cbh,8) group by x.mc
--> 测试数据:[xxdm]
if object_id('[xxdm]') is not null drop table [xxdm]
create table [xxdm]([dm] int,[mc] varchar(4))
insert [xxdm]
select 10010101,'小学' union all
select 10010102,'中学' union all
select 10010103,'大学'
--> 测试数据:[cbmxx]
if object_id('[cbmxx]') is not null drop table [cbmxx]
create table [cbmxx]([cbh] bigint,[clqxx] varchar(6))
insert [cbmxx]
select 100101010001,'录用' union all
select 100101010002,'录用' union all
select 100101010003,'不合格' union all
select 100101020001,'不合格' union all
select 100101020001,'不合格'
select mc,
报名人数=count(1),
录用人数=sum(case when clqxx='录用' then 1 else 0 end)
from [xxdm] A join [cbmxx] B
on charindex(rtrim(A.dm),rtrim(B.cbh))>0
group by mc
/*
mc 报名人数 录用人数
---- ----------- -----------
小学 3 2
中学 2 0
(2 行受影响)
*/
drop table [xxdm]
drop table [cbmxx]
Select A.mc,count(B.cbh) as 报名人数,count(B.clqxx) as 录用人数 from xxdm A,cbmxx B,where A.dm=B.cbh