A表结构
col1 col2
A --
a1 A
a2 A
a3 A
B --
b1 B
b2 B
b3 BB表结构
Col1 col2
001 a1
001 a2
001 a3
001 b1
002 a1
002 a2
002 a3
002 b1
002 b2
002 b3
最后需要查询B表,根据001查询出个1(因为001包含了A,但是没有包含完B)根据002查询出2(因为002包含了A和B)
col1 col2
A --
a1 A
a2 A
a3 A
B --
b1 B
b2 B
b3 BB表结构
Col1 col2
001 a1
001 a2
001 a3
001 b1
002 a1
002 a2
002 a3
002 b1
002 b2
002 b3
最后需要查询B表,根据001查询出个1(因为001包含了A,但是没有包含完B)根据002查询出2(因为002包含了A和B)
001 a2
001 a3
001 b1
002 a1
002 a2
002 a3
002 b1
002 b2
002 b3
001不是有 B 吗
declare @a table (col1 varchar(10) ,col2 varchar(10))
insert into @a
select 'a1','A'
UNION ALL
select 'a2','A'
union all
select 'a3','A'
union all
select 'b1','B'
UNION ALL
select 'b2','B'
UNION ALL
select 'b3','B'declare @B table (col1 varchar(10) ,col2 varchar(10))
insert into @b
select '001','a1'
union all
select '001','a2'
union all
select '001','a3'
union all
select '002','a1'
union all
select '002','a2'
union all
select '002','a3'
union all
select '002','b1'
union all
select '002','b2'
union all
select '002','b3'---查询
select col1,count(col2) as [count] from
(select distinct b.col1,a.col2 from @b b inner join @a a on b.col2=a.col1) a group by a.col1
---结果
col1 count
---------- -----------
001 1
002 2
B表:col1=001不重复纪录为4 != (*),所以返回1,
col1=002不重复纪录为6 = (*),所以返回2。
select
temp1.col1,
case when
(count(*) - (select count(*) as cnt from TA)) <> 0
then '1'
else '2'
end as cnt
from (select distinct col1, col2 from TB) as temp1
group by temp1.col1
create table #a (col1 varchar(10) ,col2 varchar(10))
insert into #a
select 'a1','A'
UNION ALL
select 'a2','A'
union all
select 'a3','A'
union all
select 'b1','B'
UNION ALL
select 'b2','B'
UNION ALL
select 'b3','B'create table #B (col1 varchar(10) ,col2 varchar(10))
insert into #b
select '001','a1'
union all
select '001','a2'
union all
select '001','a3'
union all
select '001','b1'
union all
select '002','a1'
union all
select '002','a2'
union all
select '002','a3'
union all
select '002','b1'
union all
select '002','b2'
union all
select '002','b3'查询
------------select a.col1,count(1) as col_count from
(
select c.col1,c.col2,count(c.col2) as col_count from
(
select a.col1,b.col2 from
(select distinct col1,col2 from #b)a inner join #a b on a.col2=b.col1
) c group by c.col1,c.col2
)a inner join (
select col2,count(1) as col1 from #a group by col2)b on a.col2=b.col2 and a.col_count=b.col1 group by a.col1
结果
---------
col1 col_count
---------- -----------
001 1
002 2
declare @a table (col1 varchar(10) ,col2 varchar(10))
insert into @a
select 'a1','A'
UNION ALL
select 'a2','A'
union all
select 'a3','A'
union all
select 'b1','B'
UNION ALL
select 'b2','B'
UNION ALL
select 'b3','B'declare @B table (col1 varchar(10) ,col2 varchar(10))
insert into @b
select '001','a1'
union all
select '001','a2'
union all
select '001','a3'
union all
select '002','a1'
union all
select '002','a2'
union all
select '002','a3'
union all
select '002','b1'
union all
select '002','b2'
union all
select '002','b3'
select
b.col1,count(distinct a.col2) as 记录
from
@a a
join
@b b on a.col1=b.col2
where
b.col1='001'
group by
b.col1select
b.col1,count(distinct a.col2) as 记录
from
@a a
join
@b b on a.col1=b.col2
where
b.col1='002'
group by
b.col1
--(所影响的行数为 6 行)
(所影响的行数为 9 行)col1 记录
---------- -----------
001 1(所影响的行数为 1 行)col1 记录
---------- -----------
002 2(所影响的行数为 1 行)
insert into @a
select 'a1','A'
UNION ALL
select 'a2','A'
union all
select 'a3','A'
union all
select 'b1','B'
UNION ALL
select 'b2','B'
UNION ALL
select 'b3','B'declare @B table (col1 varchar(10) ,col2 varchar(10))
insert into @b
select '001','a1'
union all
select '001','a2'
union all
select '001','a3'
union all
select '001','b1'
union all
select '002','a1'
union all
select '002','a2'
union all
select '002','a3'
union all
select '002','b1'
union all
select '002','b2'
union all
select '002','b3'
select count(distinct col2 ) from @a
where col2 not in
(
select col2 from @a a
where not exists
(select 1 from @b where col2=a.col1 and col1='001')
)select count(distinct col2 ) from @a
where col2 not in
(
select col2 from @a a
where not exists
(select 1 from @b where col2=a.col1 and col1='002')
)