34,590
社区成员
发帖
与我相关
我的任务
分享
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
**/
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 個資料列)
*/
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
*/
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*/
select distinct id from tb where name='a' or name='b'
/*
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')
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
select distinct id from tb where name='a' or name='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