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和名字重复或对不上
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都一定才认为是同一个人 对于配偶,认定,夫妇都同名同姓的机率比较低,所以夫妇姓名一样的,认为是同人如果连这点约束都没有,那确实是死节了,请考虑更新维护一下数据
,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和名字重复或对不上
,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都一定才认为是同一个人
对于配偶,认定,夫妇都同名同姓的机率比较低,所以夫妇姓名一样的,认为是同人如果连这点约束都没有,那确实是死节了,请考虑更新维护一下数据