表A:
a b
1 <NULL>
1 <NULL>
1 <NULL>
2 x
2 <NULL>
3 x
3 x
3 x
4 <NULL>请问怎样得到:
1)b全为<NULL>的a值,如这里的a=1或a=4;
2) b不全为<NULL>的a值,如这里的a=2;
3) b全有数据(NOT NULL)的a值,如这里的a=3;回答得好再加100分,酬谢!
a b
1 <NULL>
1 <NULL>
1 <NULL>
2 x
2 <NULL>
3 x
3 x
3 x
4 <NULL>请问怎样得到:
1)b全为<NULL>的a值,如这里的a=1或a=4;
2) b不全为<NULL>的a值,如这里的a=2;
3) b全有数据(NOT NULL)的a值,如这里的a=3;回答得好再加100分,酬谢!
select distinct t.a from A t where not exists(select 1 from A where a=t.a and b is not null)2、
select distinct t.a from A t where t.b is null and exists(select 1 from A where a=t.a and b is not null)3、
select distinct t.a from A t where not exists(select 1 from A where a=t.a and b is null)
insert into @A values(1,NULL)
insert into @A values(1,NULL)
insert into @A values(1,NULL)
insert into @A values(2,'x')
insert into @A values(2,NULL)
insert into @A values(3,'x')
insert into @A values(3,'x')
insert into @A values(3,'x')
insert into @A values(4,NULL) --1、
select distinct t.a from @A t where not exists(select 1 from @A where a=t.a and b is not null)
/*
a
-----------
1
4
*/
--2、
select distinct t.a from @A t where t.b is null and exists(select 1 from @A where a=t.a and b is not null)
/*
a
-----------
2
*/--3、
select distinct t.a from @A t where not exists(select 1 from @A where a=t.a and b is null)
/*
a
-----------
3
*/
select * from a where a not in (select distinct a from a where b is not null)2
select a from
(
select distinct a from a where b is not null
union all
select distinct a from a where b is null
) t
group by a having count(*) > 13
select * from a where a not in (select distinct a from a where b is null)
1>select DISTINCT(a) from a wherre b is null3>select DISTINCT(a) from a wherre b is not null
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select * from tb where a not in (select distinct a from tb where b is not null)
/*
a b
----------- ----------
1 NULL
1 NULL
1 NULL
4 NULL
*/--2
select a from
(
select distinct a from tb where b is not null
union all
select distinct a from tb where b is null
) t
group by a having count(*) > 1
/*
a
-----------
2
*/--3
select * from tb where a not in (select distinct a from tb where b is null)
/*
a b
----------- ----------
3 x
3 x
3 x
*/drop table tb
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select distinct a from tb where a not in (select distinct a from tb where b is not null)
/*
a
-----------
1
4(所影响的行数为 2 行)
*/--2
select distinct a from
(
select distinct a from tb where b is not null
union all
select distinct a from tb where b is null
) t
group by a having count(*) > 1
/*
a
-----------
2
*/--3
select distinct a from tb where a not in (select distinct a from tb where b is null)
/*
a
-----------
3(所影响的行数为 1 行)
*/drop table tb
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select distinct a from tb where a not in (select distinct a from tb where b is not null)
/*
a
-----------
1
4(所影响的行数为 2 行)
*/--2
select distinct a from
(
select distinct a from tb where b is not null
union all
select distinct a from tb where b is null
) t
group by a having count(*) > 1
/*
a
-----------
2
*/--3
select distinct a from tb where a not in (select distinct a from tb where b is null)
/*
a
-----------
3(所影响的行数为 1 行)
*/drop table tb
1、select distinct a from A where a not in( select a from A where b is not null);
2、select distinct a from A where a not in (select distinct a from A where a not in( select a from A where b is not null) union select distinct a from A where a not in (select a from A where b is null))
3、select distinct a from A where a not in (select a from A where b is null);
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select distinct a from tb where a not in (select distinct a from tb where b is not null)
/*
a
-----------
1
4(所影响的行数为 2 行)
*/--2
select distinct a from
(
select distinct a from tb where b is not null
union all
select distinct a from tb where b is null
) t
group by a having count(*) > 1
reate table tb(a int,b varchar(10))
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select distinct a from
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select distinct a from tb where a not in (select distinct a from tb where b is not null)
/*
a
-----------
1
4(所影响的行数为 2 行)
*/--2
select distinct a from
(
select distinct a from tb where b is not null
union all
select distinct a from tb where b is nullcreate table tb(a int,b varchar(10))
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select distinct a from tb where a not in (select distinct a from tb where b is not null)
/*
a
-----------
1
4(所影响的行数为 2 行)
*/--2
select distinct a from
(
select distinct a from tb where b is not null
union all
select distinct a from tb where b is null
) t
group by a having count(*) > 1
reate table tb(a int,b varchar(10))
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select distinct a from
) t
group by a having count(*) > 1
reate table tb(a int,b varchar(10))
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select distinct a from
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(1, NULL )
insert into tb values(2, 'x' )
insert into tb values(2, NULL )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(3, 'x' )
insert into tb values(4, NULL )
go--1
select distinct a from tb where a not in (select distinct a from tb where b is not null)
/*
a
-----------
1
4(所影响的行数为 2 行)
*/--2
select distinct a from
(
select distinct a from tb where b is not null
union all
select distinct a from tb where b is null
) t
group by a having count(*) > 1
/*
a
-----------
2
*/--3
select distinct a from tb where a not in (select distinct a from tb where b is null)
/*
a
-----------
3(所影响的行数为 1 行)
*/drop table tb
insert #表A select
1, NULL insert #表A select
1, NULL insert #表A select
1, NULL insert #表A select
2, 'x' insert #表A select
2, NULL insert #表A select
3, 'x' insert #表A select
3, 'x' insert #表A select
3, 'x' insert #表A select
4, NULL
select a from #表A group by a having sum(case when b is null then 0 else 1 end)=0
select a from #表A group by a having sum(case when b is null then 0 else 1 end)<>0 and sum(case when b is null then 1 else 0 end)<>0
select a from #表A group by a having sum(case when b is null then 1 else 0 end)=0
select a from #表A group by a having avg(case when b is null then -1 else 1 end) not in(1,-1)
declare @t table(a int,b varchar(8))
insert into @t values(1,NULL)
insert into @t values(1,NULL)
insert into @t values(1,NULL)
insert into @t values(2,'x')
insert into @t values(2,NULL)
insert into @t values(3,'x')
insert into @t values(3,'x')
insert into @t values(3,'x')
insert into @t values(4,NULL)
--1
select a,max(b) from @t n where not exists (select * from @t where a=n.a and b is not null) group by a
--2
select a,max(b) from @t n group by a having count(b)>0 and exists (select * from @t where a=n.a and b is null)
--3
select a,max(b) from @t n where not exists (select * from @t where a=n.a and b is null) group by a
1.select distinct a from 表A where b is null and a not in(select a from 表A where b is not null)
2.select distinct a from 表A where b is not null and a in(select a from 表A where b is null)
3.select distinct a from 表A where b is not null and a not in(select a from 表A where b is null)