sql server dependent1表中的reltionship是表示亲属的关系（=1表示spouse夫妻，=2表示Son儿子，=3表示女儿

显示全部楼层 · 2011-3-10 17:24:59

,>3表示继子女统计具体某个group(groupnumber)下
employee+spouse、employee+1 child、employee+spouse+1 child 、employee+childrens、employee+spouse+children的数量

千问 · 2011-3-10 17:24:59

首先简单关联统计, 然后再 CASE WHENSELECTemployee.name,SUM( CASE WHEN dependent1.reltionship = 1 THEN 1 ELSE 0 END )夫妻个数,SUM( CASE WHEN dependent1.reltionship = 2 THEN 1 ELSE 0 END )儿子个数,SUM( CASE WHEN dependent1.reltionship = 3 THEN 1 ELSE 0 END )女儿个数,SUM( CASE WHEN dependent1.reltionship > 3 THEN 1 ELSE 0 END )