select a.bm,
(case when b.lb='x' then sl else 0 end) as x,
(case when b.lb='y' then sl else 0 end) as y,
(case when b.lb='z' then sl else 0 end) as x
from table1 left join
(select bm,lb,sl from table2 union all
select bm,lb,sl from table3 ) as b on a.bm=b.bm
(case when b.lb='x' then sl else 0 end) as x,
(case when b.lb='y' then sl else 0 end) as y,
(case when b.lb='z' then sl else 0 end) as x
from table1 left join
(select bm,lb,sl from table2 union all
select bm,lb,sl from table3 ) as b on a.bm=b.bm
sum(case when b.lb='x' then sl else 0 end) as x,
sum(case when b.lb='y' then sl else 0 end) as y,
sum(case when b.lb='z' then sl else 0 end) as x
from table1 left join
(select bm,lb,sl from table2 union all
select bm,lb,sl from table3 ) as b on a.bm=b.bm
group by bm
isnull(sum(case when type='y' then num end),0) 'y',
isnull(sum(case when type='z' then num end),0) 'z'
(select a.部门,b.类别 type,b.数量+c.数量 num
from table1 a left join table2 b on a.部门=b.部门 left join table3 c on a.部门=c.部门) d
group by 部门
isnull(sum(case when type='y' then num end),0) 'y',
isnull(sum(case when type='z' then num end),0) 'z'
fromm (select a.部门,b.类别 type,b.数量+c.数量 num
from table1 a left join table2 b on a.部门=b.部门 left join table3 c on a.部门=c.部门) d
group by 部门
select 部门,sum(case when type='x' then num end) 'x',
(sum(case when type='y' then num end) 'y',
(sum(case when type='z' then num end) 'z'
FROM (select a.部门,b.类别 type,b.数量+c.数量 num
from table1 a left join table2 b on a.部门=b.部门 left join table3 c on a.部门=c.部门) d
group by 部门
sum(case when b.lb='x' then sl else 0 end) as x,
sum(case when b.lb='y' then sl else 0 end) as y,
sum(case when b.lb='z' then sl else 0 end) as x
from table1 a left join
(select bm,lb,sl from table2 union all
select bm,lb,sl from table3 ) as b on a.bm=b.bm
group by a.bm
全用JOIN的写法(没有必要)select a.部门,
isnull(sum(case when b.类别='x' then b.数量 end),0)+isnull(sum(case when c.类别='x' then c.数量 end),0) 'x',
isnull(sum(case when b.类别='y' then b.数量 end),0)+isnull(sum(case when c.类别='y' then c.数量 end),0) 'y',
isnull(sum(case when b.类别='z' then b.数量 end),0)+isnull(sum(case when c.类别='z' then c.数量 end),0) 'z'
from table1 a left join table2 b on a.部门=b.部门 left join table3 c on a.部门=c.部门
group by a.部门2 标准写法,如j9988select a.部门,
ISNULL(sum(case when 类别='x' then 数量 end),0) 'x',
ISNULL(sum(case when 类别='y' then 数量 end),0) 'y',
ISNULL(sum(case when 类别='z' then 数量 end),0) 'z'
from table1 a left join (select * from table2 union all select * from table3 ) b on a.部门=b.部门
group by a.部门3 子查询写法 1select a.部门,
(select isnull(sum(数量),0) from table2 where 部门=a.部门 and 类别='x')+(select isnull(sum(数量),0) from table3 where 部门=a.部门 and 类别='x') 'x',
(select isnull(sum(数量),0) from table2 where 部门=a.部门 and 类别='y')+(select isnull(sum(数量),0) from table3 where 部门=a.部门 and 类别='y') 'y',
(select isnull(sum(数量),0) from table2 where 部门=a.部门 and 类别='z')+(select isnull(sum(数量),0) from table3 where 部门=a.部门 and 类别='z') 'z'
from table1 a4 子查询写法 2select a.部门,
(select isnull(sum(数量),0) from (select * from table2 union all select * from table3) b where 部门=a.部门 and 类别='x') 'x',
(select isnull(sum(数量),0) from (select * from table2 union all select * from table3) b where 部门=a.部门 and 类别='y') 'y',
(select isnull(sum(数量),0) from (select * from table2 union all select * from table3) b where 部门=a.部门 and 类别='z') 'z'
from table1 a