CREATE TABLE #tab
(
aa INT,
bb NVARCHAR(20),
province NVARCHAR(20),
city NVARCHAR(50)
)
INSERT INTO #tab
( aa, bb, province, city )
VALUES ( 11,'张' ,'浙江',' 杭州'),
( 22 ,'王','河南', '郑州'),
( 33 ,'李' ,'河南', '洛阳'),
( 44 ,'赵', '陕西' ,'汉中'),
( 22 ,'王', '河南', '郑州'),
( 55 ,'张', '浙江', '杭州'),
( 66, '王', '上海', '上海'),
(44, '赵', '福建', '厦门')
SELECT a.*,b.city FROM (
SELECT aa,bb,province FROM #tab GROUP BY aa,bb,province) a INNER JOIN
(SELECT aa,bb,city FROM #tab GROUP BY aa,bb,city) b ON a.aa=b.aa GROUP BY a.aa,a.bb
SELECT a.aa,a.bb,a.c cs,b.c bs FROM (
SELECT aa,bb,COUNT(a.province) c FROM (
SELECT aa,bb,province FROM #tab GROUP BY aa,bb,province) a GROUP BY aa,bb
)a
INNER JOIN (
SELECT aa,bb,COUNT(a.city) c FROM (
SELECT aa,bb,city FROM #tab GROUP BY aa,bb,city) a GROUP BY aa,bb
) b ON a.aa=b.aa
直接运行出结果!!!