select count(1) from (select * from 表 where 书号=1000)a group by 姓名 having count(1)=1
select count(*) from (select 姓名 from 表 where 姓名 in(select 姓名 from 表 where 书号=1000) group by 姓名 having count(*)=1)a
declare @table table (id int,col varchar(2)) insert into @table select 1000,'aa' union all select 1000,'bb' union all select 3000,'aa' union all select 4,'dd' union all select 1000,'ee' union all select 6,'ff' select count(1) from @table t1 inner join ( select col from @table group by col having count(1)=1 ) t2 on t1.col=t2.col where id=1000 --结果:2
--> 测试数据: @s declare @s table (书号 int,姓名 varchar(1)) insert into @s select 19,'a' union all select 3,'a' union all select 34,'b' union all select 1000,'c' union all select 54,'c' union all select 3344,'b' union all select 1000,'d' union all select 1000,'f' union all select 100,'h'select count(*) from (select a.姓名 from @s a,@s b where a.姓名=b.姓名 and b.书号=1000 group by a.姓名 having count(*)=1)a
create table #tb(姓名 varchar(10), 阅读文章 varchar(50)) insert into #tb values('张三' , 'Ajax基础教程') insert into #tb values('张三' , 'C# 3.0设计模式') insert into #tb values('张三' , 'C#高级编程(第6版)') insert into #tb values('张三' , '精通ASP') insert into #tb values('李四' , 'Visual C++音频') insert into #tb values('李四' , '1EJB 中文版') insert into #tb values('李四' , '精通ASP') insert into #tb values('王某' , '精通JavaScript+jQuery') insert into #tb values('王某' , '你的灯亮着吗') insert into #tb values('阿杜' , 'Visual C++音频') insert into #tb values('杜' , 'Visual C++音频') select b.* from #tb b join ( select 姓名 from ( select count(阅读文章) 书号,姓名 from #tb group by 姓名 ) aa where 书号=1 ) AA on b.姓名=AA.姓名 姓名 阅读文章 ---------- -------------------------------------------------- 阿杜 Visual C++音频 杜 Visual C++音频(2 行受影响)
if exists(select * from sysobjects where name='tb1') drop table tb1 create table tb1 (id int,name char(20))insert into tb1 select 1,'a' union all select 2,'a' union all select 1,'b' union all select 2,'c' union all select 1,'c'select t1.name,count(t1.id)id from tb1 t1,(select name from tb1 group by name having count(id)=1)t2 where t1.name=t2.name group by t1.name -------------------------------------- name id b 1
select count(*) from
(select 姓名 from 表
where 姓名 in(select 姓名 from 表 where 书号=1000) group by 姓名 having count(*)=1)a
declare @table table (id int,col varchar(2))
insert into @table
select 1000,'aa' union all
select 1000,'bb' union all
select 3000,'aa' union all
select 4,'dd' union all
select 1000,'ee' union all
select 6,'ff' select count(1) from @table t1
inner join (
select col from @table
group by col having count(1)=1
) t2 on t1.col=t2.col
where id=1000
--结果:2
declare @s table (书号 int,姓名 varchar(1))
insert into @s
select 19,'a' union all
select 3,'a' union all
select 34,'b' union all
select 1000,'c' union all
select 54,'c' union all
select 3344,'b' union all
select 1000,'d' union all
select 1000,'f' union all
select 100,'h'select count(*) from
(select a.姓名 from @s a,@s b where a.姓名=b.姓名 and b.书号=1000 group by a.姓名 having count(*)=1)a
insert into #tb values('张三' , 'Ajax基础教程')
insert into #tb values('张三' , 'C# 3.0设计模式')
insert into #tb values('张三' , 'C#高级编程(第6版)')
insert into #tb values('张三' , '精通ASP')
insert into #tb values('李四' , 'Visual C++音频')
insert into #tb values('李四' , '1EJB 中文版')
insert into #tb values('李四' , '精通ASP')
insert into #tb values('王某' , '精通JavaScript+jQuery')
insert into #tb values('王某' , '你的灯亮着吗')
insert into #tb values('阿杜' , 'Visual C++音频')
insert into #tb values('杜' , 'Visual C++音频')
select b.* from #tb b join
(
select 姓名 from
(
select count(阅读文章) 书号,姓名 from #tb group by 姓名
) aa
where 书号=1
) AA
on b.姓名=AA.姓名
姓名 阅读文章
---------- --------------------------------------------------
阿杜 Visual C++音频
杜 Visual C++音频(2 行受影响)
if exists(select * from sysobjects where name='tb1')
drop table tb1
create table tb1
(id int,name char(20))insert into tb1
select 1,'a'
union all
select 2,'a'
union all
select 1,'b'
union all
select 2,'c'
union all
select 1,'c'select t1.name,count(t1.id)id from tb1 t1,(select name from tb1 group by name having count(id)=1)t2
where t1.name=t2.name group by t1.name
--------------------------------------
name id
b 1