select a.*,b.male,b.female .. from 调入调出 a full out join 性别 b on a.dept=b.dept full out join ...
如果你用的是SQL Server 2000,你可以将你想要的作成一个视图,以各个表的dept字段相连,查看自动生成的SQL语句不就行了吗?那可是最标准的。
--这样嵌套下去就行了:select dtept=isnull(a.dept,b.dept) ,a.[in],a.[out],a.male,a.female ,b.本科,b.研究生,b.20以下,b.[25],b.[30] from( select dtept=isnull(a.dept,b.dept) ,a.[in],a.[out],b.male,b.female from 调入调出 a full join 性别 b on a.dept=b.dept )a full join( select dtept=isnull(a.dept,b.dept) ,a.本科,a.研究生,b.20以下,b.[25],b.[30] from 学历 a full join 年龄 b on a.dept=b.dept ) b on a.dept=b.dept
--再加上部门的部分,注意,上面的语句中:将所有的:b.20以下,改为:b.[20以下]select a.deptid,a.deptname ,b.[in],b.[out],b.male,b.female ,b.本科,b.研究生,b.[20以下],b.[25],b.[30] from dept a left join( select dtept=isnull(a.dept,b.dept) ,a.[in],a.[out],a.male,a.female ,b.本科,b.研究生,b.[20以下],b.[25],b.[30] from( select dtept=isnull(a.dept,b.dept) ,a.[in],a.[out],b.male,b.female from 调入调出 a full join 性别 b on a.dept=b.dept )a full join( select dtept=isnull(a.dept,b.dept) ,a.本科,a.研究生,b.[20以下],b.[25],b.[30] from 学历 a full join 年龄 b on a.dept=b.dept ) b on a.dept=b.dept ) b on a.deptid=b.dept
不必先统计,再合并。直接以dept分组统计就行了。形式如下:select dept, sum(case when io='in' then 1 else 0 end) as [in], sum(case when io='out' then 1 else 0 end) as [out], sum(case when sex=0 then 1 else 0 end) as male, sum(case when sex=0 then 0 else 1 end) as female, ... from tablename group by dept
,a.[in],a.[out],a.male,a.female
,b.本科,b.研究生,b.20以下,b.[25],b.[30]
from(
select dtept=isnull(a.dept,b.dept)
,a.[in],a.[out],b.male,b.female
from 调入调出 a full join 性别 b on a.dept=b.dept
)a full join(
select dtept=isnull(a.dept,b.dept)
,a.本科,a.研究生,b.20以下,b.[25],b.[30]
from 学历 a full join 年龄 b on a.dept=b.dept
) b on a.dept=b.dept
,b.[in],b.[out],b.male,b.female
,b.本科,b.研究生,b.[20以下],b.[25],b.[30]
from dept a left join(
select dtept=isnull(a.dept,b.dept)
,a.[in],a.[out],a.male,a.female
,b.本科,b.研究生,b.[20以下],b.[25],b.[30]
from(
select dtept=isnull(a.dept,b.dept)
,a.[in],a.[out],b.male,b.female
from 调入调出 a full join 性别 b on a.dept=b.dept
)a full join(
select dtept=isnull(a.dept,b.dept)
,a.本科,a.研究生,b.[20以下],b.[25],b.[30]
from 学历 a full join 年龄 b on a.dept=b.dept
) b on a.dept=b.dept
) b on a.deptid=b.dept
换个角度。我做部门和其它所有的统计数据进行outer join感觉会更方便。
因为这样嵌套层数太多了。不知道 zjcxc(邹建)您怎么看。
sum(case when io='in' then 1 else 0 end) as [in],
sum(case when io='out' then 1 else 0 end) as [out],
sum(case when sex=0 then 1 else 0 end) as male,
sum(case when sex=0 then 0 else 1 end) as female,
...
from tablename
group by dept