一、select mp_teemcode,
sum(case when pc_class = 'a类' then 1 else 0 end) A,
sum(case when pc_class = 'b类' then 1 else 0 end) B,
count(1) 完成
from jbb group by mp_teemcode
查出 mp_teemcode A B 完成
王 15 5 20
张 20 10 30
二、select inspect_name,
sum(case when uplj = 'Y' then 1 else 0 end) 包保,
count(1) 检查次数
from jcxs group by inspect_name
查出 inspect_name Y 检查次数
王 3 4
张 5 5
我想查出inspect_name A B Y 检查次数 完成
王 15 5 3 4 20
张 20 10 5 5 30
sum(case when pc_class = 'a类' then 1 else 0 end) A,
sum(case when pc_class = 'b类' then 1 else 0 end) B,
count(1) 完成
from jbb group by mp_teemcode
查出 mp_teemcode A B 完成
王 15 5 20
张 20 10 30
二、select inspect_name,
sum(case when uplj = 'Y' then 1 else 0 end) 包保,
count(1) 检查次数
from jcxs group by inspect_name
查出 inspect_name Y 检查次数
王 3 4
张 5 5
我想查出inspect_name A B Y 检查次数 完成
王 15 5 3 4 20
张 20 10 5 5 30
m.A, --如果为空需要显示为0的话,isnull(m.A,0) A ,其他类似
m.B,
n.Y,
n.检查次数,
m.完成
from
(
select mp_teemcode,
sum(case when pc_class = 'a类' then 1 else 0 end) A,
sum(case when pc_class = 'b类' then 1 else 0 end) B,
count(1) 完成
from jbb group by mp_teemcode
) m
full join
(
select inspect_name,
sum(case when uplj = 'Y' then 1 else 0 end) 包保,
count(1) 检查次数
from jcxs group by inspect_name
)
on m.mp_teemcode,n.inspect_name
m.A, --如果为空需要显示为0的话,isnull(m.A,0) A ,其他类似
m.B,
n.Y,
n.检查次数,
m.完成
from
(
select mp_teemcode,
sum(case when pc_class = 'a类' then 1 else 0 end) A,
sum(case when pc_class = 'b类' then 1 else 0 end) B,
count(1) 完成
from jbb group by mp_teemcode
) m
full join
(
select inspect_name,
sum(case when uplj = 'Y' then 1 else 0 end) 包保,
count(1) 检查次数
from jcxs group by inspect_name
) n
on m.mp_teemcode = n.inspect_name
我要查jcxs表为主
大致写法如下:
select m.* , n.某些字段 from m full join n on m.关键字 = n.关键字
select m.* , n.某些字段 from m left join n on m.关键字 = n.关键字其中 m, n 也可以使用子查询.
select isnull(m.mp_teemcode,n.inspect_name) inspect_name ,
isnull(m.A,0), --如果为空需要显示为0的话,isnull(m.A,0) A ,其他类似
isnull(m.B,0),
n.Y,
n.检查次数,
m.完成
from
(
select mp_teemcode,
sum(case when pc_class = 'a类' then 1 else 0 end) A,
sum(case when pc_class = 'b类' then 1 else 0 end) B,
count(1) 完成
from jbb group by mp_teemcode
) m
full join
(
select inspect_name,
sum(case when uplj = 'Y' then 1 else 0 end) 包保,
count(1) 检查次数
from jcxs group by inspect_name
) n
on m.mp_teemcode = n.inspect_name