SQL中数据关联统计

[傲雪飞狐]

有一个登记房屋信息表，有如下信息，房子id，户主id，户主姓名，配偶id，配偶姓名

;with T as
(
select 100 as House ,'A' as [Address],68 as  OwnerId ,'孟子良' as OwnerName,213 as SpouseId,'吴秀' as  SpouseName
UNION ALL 
SELECT 101,'B',213,'吴秀',68,'孟子良'
UNION ALL 
SELECT 102,'C',68,'孟子良',0,''  
UNION ALL 
SELECT 103,'D',89,'程许兰',205,'农妮芬'
UNION ALL 
SELECT 104,'E',432,'粟子林',213,'武翠英'
UNION ALL 
SELECT 105,'F',57,'李世作',94,'宁应芳'
UNION ALL 
SELECT 106,'G',205,'农妮芬',0,''
UNION ALL 
SELECT 107,'H',213,'武翠英',68,'粟子林'
) SELECT * FROM T

现在要统计出个人名下和家庭拥有的房子的总数，sql该怎么写？
个人名下房产的我会，但是关联家庭我就不懂了。
各位麻烦能指点一下？

需要得到如下表

户主     地址   个人名下房产       家庭房产     房子id
孟子良    A    2            3        100      --户主登记有配偶，所以家庭房产连同配偶一起统计
吴秀     B    1            3        101
孟子良    C    2            2        102      --户主没有登记有配偶，所以家庭房产只统计自己名下的
程许兰    D    1            2        103
粟子林    E    1            2        104
李世作    F    1            1        105
农妮芬    G    1            1        106
武翠英    H    1            2        107

[还在加载中灬]

SELECT T1.OwnerName[户主],T1.[Address][地址]
	,COUNT(T1.House)OVER(PARTITION BY T1.OwnerId)[个人名下房产]
	,COUNT(T1.House)OVER(PARTITION BY T1.OwnerId)+ISNULL(T2.HouseCount,0)[家庭房产]
	,T1.House[房子id]
FROM T T1 LEFT JOIN
	(SELECT OwnerId,COUNT(House)HouseCount FROM T GROUP BY OwnerId)T2
		ON T1.SpouseId=T2.OwnerId
ORDER BY T1.House

你参考一下，不过你的有些ID和名字重复或对不上

[傲雪飞狐]

引用 1 楼 ky_min 的回复:

SELECT T1.OwnerName[户主],T1.[Address][地址]
	,COUNT(T1.House)OVER(PARTITION BY T1.OwnerId)[个人名下房产]
	,COUNT(T1.House)OVER(PARTITION BY T1.OwnerId)+ISNULL(T2.HouseCount,0)[家庭房产]
	,T1.House[房子id]
FROM T T1 LEFT JOIN
	(SELECT OwnerId,COUNT(House)HouseCount FROM T GROUP BY OwnerId)T2
		ON T1.SpouseId=T2.OwnerId
ORDER BY T1.House

你参考一下，不过你的有些ID和名字重复或对不上

谢谢，因为户主和配偶是两个表的数据，所以Id相同也没办法的，只能依靠姓名来区别了，但是同名的何其多。。。唉，该死的需求

[还在加载中灬]

SELECT T1.OwnerName[户主],T1.[Address][地址]
	,COUNT(T1.House)OVER(PARTITION BY T1.OwnerId,T1.OwnerName)[个人名下房产]
	,COUNT(T1.House)OVER(PARTITION BY T1.OwnerId,T1.OwnerName)+ISNULL(T2.HouseCount,0)[家庭房产]
	,T1.House[房子id]
FROM T T1 LEFT JOIN
	(SELECT OwnerId,OwnerName,MAX(SpouseName)SpouseName,COUNT(House)HouseCount
	 FROM T GROUP BY OwnerId,OwnerName)T2
		ON T1.SpouseName=T2.OwnerName AND T1.OwnerName=T2.SpouseName
ORDER BY T1.House

要我说，还没有打死结，不过有极限性 以上语句，认定OwnerId,OwnerName都一定才认为是同一个人 对于配偶，认定，夫妇都同名同姓的机率比较低，所以夫妇姓名一样的，认为是同人 如果连这点约束都没有，那确实是死节了，请考虑更新维护一下数据

[还在加载中灬]

引用 2 楼 aoxuefeihu 的回复:

[quote=引用 1 楼 ky_min 的回复:]

SELECT T1.OwnerName[户主],T1.[Address][地址]
	,COUNT(T1.House)OVER(PARTITION BY T1.OwnerId)[个人名下房产]
	,COUNT(T1.House)OVER(PARTITION BY T1.OwnerId)+ISNULL(T2.HouseCount,0)[家庭房产]
	,T1.House[房子id]
FROM T T1 LEFT JOIN
	(SELECT OwnerId,COUNT(House)HouseCount FROM T GROUP BY OwnerId)T2
		ON T1.SpouseId=T2.OwnerId
ORDER BY T1.House

你参考一下，不过你的有些ID和名字重复或对不上

谢谢，因为户主和配偶是两个表的数据，所以Id相同也没办法的，只能依靠姓名来区别了，但是同名的何其多。。。唉，该死的需求[/quote]参考以上意见