select 医疗证号 , 户主姓名,count(姓名) as 户人口数 from tb group by 医疗证号 , 户主姓名
select a.*,b.[户主姓名] from ( select [医疗证号],count(*) as 户人口数 from [表名] group by [医疗证号] ) a inner join ( select * from [表名] where [户主姓名] is not null ) b on a.[医疗证号]= b.[医疗证号]
1、建表CREATE TABLE [dbo].[Table_3]( [id] [int] NULL, [ylzh] [varchar](50) NULL, [hzxm] [varchar](50) NULL, [xm] [varchar](50) NULL, [yhzgx] [varchar](50) NULL, [xb] [varchar](50) NULL ) ON [PRIMARY]GO 2、插入数据 [code=sql] /****** Object: Database [tempdb1] Script Date: 2013/12/26 14:41:56 ******/ /****** Object: Tables [Table_3] ******/ USE [tempdb1]/****** Object: Table [Table_3] Begin ******/ INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(1,'02140001','金百忠','金百忠','本人或户主','男性') INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(2,'02140001',NULL,'金叶','女','女性') INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(3,'02140002','高明','高明','本人或户主','男性') INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(4,'02140003','马素琴','马素琴','本人或户主','女性') INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(5,'02140003',NULL,'金百刚','子','男性') GO /****** Object: Table [Table_3] End ******/3、执行select t1.ylzh,t2.hzxm,t1.num from (select ylzh,count(*) as num from table_3 group by ylzh) t1 inner join (select ylzh,hzxm from table_3 where hzxm is not null and hzxm!='') t2 on t1.ylzh=t2.ylzh
INSERT INTO @tb SELECT 1,'02140001','金百忠','金百忠','本人或户主','男性' UNION ALL SELECT 2,'02140001','','金业','女','女性' UNION ALL SELECT 2,'02140001','','孙秀斌','配偶','女性' UNION ALL SELECT 3,'02140002','高明','高明','本人或户主','男性' UNION ALL SELECT 4,'02140003','马素琴','马素琴','本人或户主','女性' UNION ALL SELECT 5,'02140003','','金百刚','子','男性' --SELECT * FROM @tbSELECT aa.医疗证号,bb.户主姓名,aa.户口人数 FROM ( SELECT 医疗证号,COUNT(姓名)户口人数 FROM @tb GROUP BY 医疗证号 )aa, (SELECT 医疗证号,户主姓名 FROM @tb WHERE 户主姓名<>'')bb WHERE aa.医疗证号=bb.医疗证号
select a.医疗证号,a.户主姓名,b.户口人数 from (select 医疗证号,户主姓名 from table where 户主姓名 is not null) a join (select 医疗证号 count(姓名) 户口人数 from table group by 医疗证号) b on a.医疗证号=b.医疗证号
from tb
group by 医疗证号 , 户主姓名
select a.*,b.[户主姓名] from
(
select [医疗证号],count(*) as 户人口数
from [表名]
group by [医疗证号]
) a inner join
(
select *
from [表名] where [户主姓名] is not null
) b on a.[医疗证号]= b.[医疗证号]
[id] [int] NULL,
[ylzh] [varchar](50) NULL,
[hzxm] [varchar](50) NULL,
[xm] [varchar](50) NULL,
[yhzgx] [varchar](50) NULL,
[xb] [varchar](50) NULL
) ON [PRIMARY]GO
2、插入数据
[code=sql]
/****** Object: Database [tempdb1] Script Date: 2013/12/26 14:41:56 ******/
/****** Object: Tables [Table_3] ******/
USE [tempdb1]/****** Object: Table [Table_3] Begin ******/
INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(1,'02140001','金百忠','金百忠','本人或户主','男性')
INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(2,'02140001',NULL,'金叶','女','女性')
INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(3,'02140002','高明','高明','本人或户主','男性')
INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(4,'02140003','马素琴','马素琴','本人或户主','女性')
INSERT INTO[Table_3]([id],[ylzh],[hzxm],[xm],[yhzgx],[xb])VALUES(5,'02140003',NULL,'金百刚','子','男性')
GO
/****** Object: Table [Table_3] End ******/3、执行select t1.ylzh,t2.hzxm,t1.num from
(select ylzh,count(*) as num
from table_3
group by ylzh) t1 inner join (select ylzh,hzxm from table_3 where hzxm is not null and hzxm!='') t2
on t1.ylzh=t2.ylzh
医疗证号 varchar(20),
户主姓名 varchar(20),
姓名 varchar(20),
与户主关系 varchar(20),
性别 varchar(20))
INSERT INTO @tb
SELECT 1,'02140001','金百忠','金百忠','本人或户主','男性' UNION ALL
SELECT 2,'02140001','','金业','女','女性' UNION ALL
SELECT 2,'02140001','','孙秀斌','配偶','女性' UNION ALL
SELECT 3,'02140002','高明','高明','本人或户主','男性' UNION ALL
SELECT 4,'02140003','马素琴','马素琴','本人或户主','女性' UNION ALL
SELECT 5,'02140003','','金百刚','子','男性'
--SELECT * FROM @tbSELECT aa.医疗证号,bb.户主姓名,aa.户口人数
FROM
(
SELECT 医疗证号,COUNT(姓名)户口人数
FROM @tb
GROUP BY 医疗证号
)aa,
(SELECT 医疗证号,户主姓名 FROM @tb WHERE 户主姓名<>'')bb
WHERE aa.医疗证号=bb.医疗证号
from
(select 医疗证号,户主姓名 from table where 户主姓名 is not null) a
join (select 医疗证号 count(姓名) 户口人数 from table group by 医疗证号) b
on a.医疗证号=b.医疗证号