select id from ( select distinct id from tb where name = 'a' union all select distinct id from tb where name = 'b' ) t group by id having count(1) = 2
select distinct id from tb where name='a' or name='b'
create table tb(id int, name varchar(10)) insert into tb values(1 , 'a') insert into tb values(1 , 'b') insert into tb values(1 , 'c') insert into tb values(2 , 'a') insert into tb values(2 , 'b') insert into tb values(2 , 'd') insert into tb values(3 , 'z') insert into tb values(3 , 'v') insert into tb values(3 , 'x') go --只要包含其中的一个 select distinct id from tb where name = 'a' or name = 'b' /* id ----------- 1 2(所影响的行数为 2 行) */ --同时包含a,b select id from ( select distinct id from tb where name = 'a' union all select distinct id from tb where name = 'b' ) t group by id having count(1) = 2 /* id ----------- 1 2(所影响的行数为 2 行) */ drop table tb
/* id name 1 a 1 b 1 c 2 a 2 b 2 d 3 z 3 v 3 x */ create table #AA ( id int, [Name] varchar(20) ) insert into #AA select 1,'a' union all select 1,'b' union all select 1,'c' union all select 2,'a' union all select 2,'b' union all select 2,'d' union all select 3,'z' union all select 3,'v' union all select 3,'x'select distinct(id) from #AA where [Name] in('a','b')
select distinct id from tb where name='a' or name='b'
table id name 1 a 1 b 1 c 2 a 2 b 2 d 3 a 3 v 3 x 查询结果(name字段包含同时包含a和b 的id) 1 2
select distinct id from tb where name in ('a','b')
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB GO create table tb(id int, name varchar(10)) insert into tb values(1 , 'a') insert into tb values(1 , 'b') insert into tb values(1 , 'c') insert into tb values(2 , 'a') insert into tb values(2 , 'b') insert into tb values(2 , 'd') insert into tb values(3 , 'z') insert into tb values(3 , 'v') insert into tb values(3 , 'x') go SELECT DISTINCT ID FROM TB WHERE NAME IN('A','B') /*ID ----------- 1 2*/
declare @tb table(id int, name varchar(1)) insert @tb SELECT 1, 'a' UNION ALL SELECT 1, 'b' UNION ALL SELECT 1, 'c' UNION ALL SELECT 2, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 2, 'd' UNION ALL SELECT 3, 'z' UNION ALL SELECT 3, 'v' UNION ALL SELECT 3, 'x'select id from @tb where name='a' or name='b' group by id having count(distinct name)=2 /* id ----------- 1 2 */
table id name 1 a 1 b 1 c 2 a 2 b 2 d 3 a 3 v 3 x 查询结果(name字段包含同时包含a和b 的id) 1 2 大家看清楚, 3 a 3 v 3 x 我要a,b同时包含的ID
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB GO create table tb(id int, name varchar(10)) insert into tb values(1 , 'a') insert into tb values(1 , 'b') insert into tb values(1 , 'c') insert into tb values(2 , 'a') insert into tb values(2 , 'b') insert into tb values(2 , 'd') insert into tb values(3 , 'z') insert into tb values(3 , 'v') insert into tb values(3 , 'x') go select id from ( select distinct id from tb where name = 'a' union all select distinct id from tb where name = 'b' ) t group by id having count(1) >= 2 /*id ----------- 1 2(影響 2 個資料列) */
select distinct id from tb t where exists(select *from tb where id=t.id and name in('a','b')) /** id ----------- 1 2 **/select * from tb t where exists(select *from tb where id=t.id and name in('a','b')) /** id name ----------- ---- 1 a 1 b 1 c 2 a 2 b 2 d **/
(
select distinct id from tb where name = 'a'
union all
select distinct id from tb where name = 'b'
) t
group by id having count(1) = 2
insert into tb values(1 , 'a')
insert into tb values(1 , 'b')
insert into tb values(1 , 'c')
insert into tb values(2 , 'a')
insert into tb values(2 , 'b')
insert into tb values(2 , 'd')
insert into tb values(3 , 'z')
insert into tb values(3 , 'v')
insert into tb values(3 , 'x')
go
--只要包含其中的一个
select distinct id from tb where name = 'a' or name = 'b'
/*
id
-----------
1
2(所影响的行数为 2 行)
*/
--同时包含a,b
select id from
(
select distinct id from tb where name = 'a'
union all
select distinct id from tb where name = 'b'
) t
group by id having count(1) = 2
/*
id
-----------
1
2(所影响的行数为 2 行)
*/
drop table tb
id name
1 a
1 b
1 c
2 a
2 b
2 d
3 z
3 v
3 x
*/
create table #AA
(
id int,
[Name] varchar(20)
)
insert into #AA
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'b' union all
select 2,'d' union all
select 3,'z' union all
select 3,'v' union all
select 3,'x'select distinct(id) from #AA where [Name] in('a','b')
select distinct id from tb where name='a' or name='b'
1 a
1 b
1 c 2 a
2 b
2 d 3 a
3 v
3 x 查询结果(name字段包含同时包含a和b 的id)
1
2
GO
create table tb(id int, name varchar(10))
insert into tb values(1 , 'a')
insert into tb values(1 , 'b')
insert into tb values(1 , 'c')
insert into tb values(2 , 'a')
insert into tb values(2 , 'b')
insert into tb values(2 , 'd')
insert into tb values(3 , 'z')
insert into tb values(3 , 'v')
insert into tb values(3 , 'x')
go
SELECT DISTINCT ID FROM TB WHERE NAME IN('A','B')
/*ID
-----------
1
2*/
insert @tb
SELECT 1, 'a' UNION ALL
SELECT 1, 'b' UNION ALL
SELECT 1, 'c' UNION ALL
SELECT 2, 'a' UNION ALL
SELECT 2, 'b' UNION ALL
SELECT 2, 'd' UNION ALL
SELECT 3, 'z' UNION ALL
SELECT 3, 'v' UNION ALL
SELECT 3, 'x'select id from @tb where name='a' or name='b' group by id having count(distinct name)=2
/*
id
-----------
1
2
*/
1 a
1 b
1 c 2 a
2 b
2 d 3 a
3 v
3 x 查询结果(name字段包含同时包含a和b 的id)
1
2
大家看清楚,
3 a
3 v
3 x
我要a,b同时包含的ID
GO
create table tb(id int, name varchar(10))
insert into tb values(1 , 'a')
insert into tb values(1 , 'b')
insert into tb values(1 , 'c')
insert into tb values(2 , 'a')
insert into tb values(2 , 'b')
insert into tb values(2 , 'd')
insert into tb values(3 , 'z')
insert into tb values(3 , 'v')
insert into tb values(3 , 'x')
go
select id from
(
select distinct id from tb where name = 'a'
union all
select distinct id from tb where name = 'b'
) t
group by id having count(1) >= 2
/*id
-----------
1
2(影響 2 個資料列)
*/
select distinct id from tb t where exists(select *from tb where id=t.id and name in('a','b'))
/**
id
-----------
1
2
**/select * from tb t where exists(select *from tb where id=t.id and name in('a','b'))
/**
id name
----------- ----
1 a
1 b
1 c
2 a
2 b
2 d
**/