select code from tb where name in('y1','y2') group by code having count(distinct name)=2
select count(distinct code) from tb where code in (select distinct code from tb where name='y1') and code in (select distinct code from tb where name='y2')
select count(*) from (select * from tb where name='y2') K where name='y1'
if object_id('a') is not null drop table a go create table a([code] varchar(10),[name] varchar(10)) insert a select 'f1','y1' union all select 'f1','y2' union all select 'f1','y3' union all select 'f2','y1' union all select 'f2','y3' union all select 'f3','y2' union all select 'f3','y3' union all select 'f4','y1' union all select 'f4','y2' union all select 'f4','y3' union all select 'f4','y4' go select code,count(1) 个数 from a where name in('y1','y2') group by code /* code 个数 ---------- ----------- f1 2 f2 1 f3 1 f4 2(4 行受影响) */
if object_id('a') is not null drop table a go create table a([code] varchar(10),[name] varchar(10)) insert a select 'f1','y1' union all select 'f1','y2' union all select 'f1','y3' union all select 'f2','y1' union all select 'f2','y3' union all select 'f3','y2' union all select 'f3','y3' union all select 'f4','y1' union all select 'f4','y2' union all select 'f4','y3' union all select 'f4','y4' go select count(1) y1y2总个数 from a where name in('y1','y2') /* y1y2总个数 ----------- 6(1 行受影响) */
好像理解错了,修正:if object_id('a') is not null drop table a go create table a([code] varchar(10),[name] varchar(10)) insert a select 'f1','y1' union all select 'f1','y2' union all select 'f1','y3' union all select 'f2','y1' union all select 'f2','y3' union all select 'f3','y2' union all select 'f3','y3' union all select 'f4','y1' union all select 'f4','y2' union all select 'f4','y3' union all select 'f4','y4' go select count(distinct a.code) 个数 from ( select * from a where name ='y1' ) a join ( select * from a where name ='y2' ) b on a.code=b.code /* 个数 ----------- 2*/ (1 行受影响)
create table #tb([code] varchar(10),[name] varchar(10)) insert #tb select 'f1','y1' union all select 'f1','y2' union all select 'f1','y3' union all select 'f2','y1' union all select 'f2','y3' union all select 'f3','y2' union all select 'f3','y3' union all select 'f4','y1' union all select 'f4','y2' union all select 'f4','y3' union all select 'f4','y4'select code,count(code) from #tb where [name] in ('y1','y2') group by code having count(1)=2code ---------- ----------- f1 2 f4 2(2 行受影响)
结果要向htl258修正后的 有没有查询速度更快的语句 要把这个嵌入到游标里。
if object_id('tb') is not null drop table tb go create table tb([code] varchar(10),[name] varchar(10)) insert tb select 'f1','y1' union all select 'f1','y2' union all select 'f1','y3' union all select 'f2','y1' union all select 'f2','y3' union all select 'f3','y2' union all select 'f3','y3' union all select 'f4','y1' union all select 'f4','y2' union all select 'f4','y3' union all select 'f4','y4' go select count(distinct code) from tb a where exists(select 1 from tb where code=a.code and name='y1') and exists (select 1 from tb where code=a.code and name='y2') go drop table tb /* ----------- 2 */
select count(distinct code) from A c where exists( select 1 from ( select a.code form (select * from A where name = 'y1')a, (select * from A where name = 'y2')b where a.code = b.code )t where t.code = c.code )
select count( distinct code) from A where name in ('y1','y2')
from tb
where name in('y1','y2')
group by code
having count(distinct name)=2
and code in (select distinct code from tb where name='y2')
from (select * from tb where name='y2') K
where name='y1'
go
create table a([code] varchar(10),[name] varchar(10))
insert a select 'f1','y1'
union all select 'f1','y2'
union all select 'f1','y3'
union all select 'f2','y1'
union all select 'f2','y3'
union all select 'f3','y2'
union all select 'f3','y3'
union all select 'f4','y1'
union all select 'f4','y2'
union all select 'f4','y3'
union all select 'f4','y4'
go
select code,count(1) 个数 from a where name in('y1','y2') group by code
/*
code 个数
---------- -----------
f1 2
f2 1
f3 1
f4 2(4 行受影响)
*/
go
create table a([code] varchar(10),[name] varchar(10))
insert a select 'f1','y1'
union all select 'f1','y2'
union all select 'f1','y3'
union all select 'f2','y1'
union all select 'f2','y3'
union all select 'f3','y2'
union all select 'f3','y3'
union all select 'f4','y1'
union all select 'f4','y2'
union all select 'f4','y3'
union all select 'f4','y4'
go
select count(1) y1y2总个数 from a where name in('y1','y2')
/*
y1y2总个数
-----------
6(1 行受影响)
*/
go
create table a([code] varchar(10),[name] varchar(10))
insert a select 'f1','y1'
union all select 'f1','y2'
union all select 'f1','y3'
union all select 'f2','y1'
union all select 'f2','y3'
union all select 'f3','y2'
union all select 'f3','y3'
union all select 'f4','y1'
union all select 'f4','y2'
union all select 'f4','y3'
union all select 'f4','y4'
go
select count(distinct a.code) 个数
from
(
select * from a where name ='y1'
) a
join
(
select * from a where name ='y2'
) b
on a.code=b.code
/*
个数
-----------
2*/
(1 行受影响)
insert #tb select 'f1','y1'
union all select 'f1','y2'
union all select 'f1','y3'
union all select 'f2','y1'
union all select 'f2','y3'
union all select 'f3','y2'
union all select 'f3','y3'
union all select 'f4','y1'
union all select 'f4','y2'
union all select 'f4','y3'
union all select 'f4','y4'select code,count(code) from #tb where [name] in ('y1','y2') group by code having count(1)=2code
---------- -----------
f1 2
f4 2(2 行受影响)
有没有查询速度更快的语句
要把这个嵌入到游标里。
go
create table tb([code] varchar(10),[name] varchar(10))
insert tb select 'f1','y1'
union all select 'f1','y2'
union all select 'f1','y3'
union all select 'f2','y1'
union all select 'f2','y3'
union all select 'f3','y2'
union all select 'f3','y3'
union all select 'f4','y1'
union all select 'f4','y2'
union all select 'f4','y3'
union all select 'f4','y4'
go
select count(distinct code) from tb a where exists(select 1 from tb where code=a.code and name='y1')
and exists (select 1 from tb where code=a.code and name='y2')
go
drop table tb
/*
-----------
2
*/
select count(distinct code) from A c where exists(
select 1 from
(
select a.code form
(select * from A where name = 'y1')a,
(select * from A where name = 'y2')b
where a.code = b.code
)t
where t.code = c.code
)