表 a
id name code
1 aaaa 01
2 bbbb 01
1 aaaa 02
1 aaaa 03
3 cccc 01
2 bbbb 02
4 dddd 02需查出在表a中,一个id同时有code 01和02的所有记录
效果如下id name code
1 aaaa 01
1 aaaa 02
2 bbbb 01
2 bbbb 02请问sql语句怎么写?
id name code
1 aaaa 01
2 bbbb 01
1 aaaa 02
1 aaaa 03
3 cccc 01
2 bbbb 02
4 dddd 02需查出在表a中,一个id同时有code 01和02的所有记录
效果如下id name code
1 aaaa 01
1 aaaa 02
2 bbbb 01
2 bbbb 02请问sql语句怎么写?
select id from
(
select distinct id from a where code = '01'
union all
select distinct id from a where code = '02'
) t group by id having count(1) = 2)
insert #tttt select 1 ,'aaaa','01'
insert #tttt select 2 ,'bbbb','01'
insert #tttt select 1 ,'aaaa','02'
insert #tttt select 1 ,'aaaa','03'
insert #tttt select 3 ,'cccc','01'
insert #tttt select 2 ,'bbbb','02'
insert #tttt select 4 ,'dddd','02'select *
from (select * from #tttt where code in ('01','02'))a
where exists (select * from (select * from #tttt where code in ('01','02')) b where a.id=b.id and a.name=b.name and a.code<>b.code)
order by id id name code
----------- ---- ----
1 aaaa 01
1 aaaa 02
2 bbbb 02
2 bbbb 01(4 行受影响)
insert into tb values(1, 'aaaa' ,'01')
insert into tb values(2, 'bbbb' ,'01')
insert into tb values(1, 'aaaa' ,'02')
insert into tb values(1, 'aaaa' ,'03')
insert into tb values(3, 'cccc' ,'01')
insert into tb values(2, 'bbbb' ,'02')
insert into tb values(4, 'dddd' ,'02')
goselect * from tb where id in(
select id from
(
select distinct id from tb where code = '01'
union all
select distinct id from tb where code = '02'
) t group by id having count(1) = 2)
order by iddrop table tb/*
id name code
----------- ---------- ----------
1 aaaa 01
1 aaaa 02
1 aaaa 03
2 bbbb 01
2 bbbb 02(所影响的行数为 5 行)
*/
EXISTS(SELECT 1 FROM a WHERE id<>t.id AND code=t.code)
and t.code in ('01','02')
insert into tb values(1, 'aaaa' ,'01')
insert into tb values(2, 'bbbb' ,'01')
insert into tb values(1, 'aaaa' ,'02')
insert into tb values(1, 'aaaa' ,'03')
insert into tb values(3, 'cccc' ,'01')
insert into tb values(2, 'bbbb' ,'02')
insert into tb values(4, 'dddd' ,'02')
goselect * from tb where id in(
select id from
(
select distinct id from tb where code = '01'
union all
select distinct id from tb where code = '02'
) t group by id having count(1) = 2)
and code in ('01','02')
order by iddrop table tb/*
id name code
----------- ---------- ----------
1 aaaa 01
1 aaaa 02
2 bbbb 01
2 bbbb 02(所影响的行数为 4 行)*/
select * from a t WHERE
EXISTS(SELECT 1 FROM a WHERE id=t.id AND code<>t.code)
and t.code in ('01','02')
insert @a select 1 ,'aaaa', '01'
union all select 2 ,'bbbb', '01'
union all select 1 ,'aaaa', '02'
union all select 1 ,'aaaa', '03'
union all select 3 ,'cccc', '01'
union all select 2 ,'bbbb' ,'02'
union all select 4 ,'dddd' ,'02'
select * from @a a where code in('01','02') and exists(select 1 from @a where id=a.id and code<>a.code)--result
/*id name code
----------- -------------------- ----
1 aaaa 01
2 bbbb 01
1 aaaa 02
2 bbbb 02(所影响的行数为 4 行)
*/
drop table tb
go
create table tb(id int, name nvarchar(10), code nvarchar(10))
insert into tb
select 1, 'aaaa', '01' union all
select 2 ,'bbbb', '01' union all
select 1 ,'aaaa' ,'02' union all
select 1, 'aaaa', '03' union all
select 3 ,'cccc' ,'01' union all
select 2, 'bbbb' ,'02' union all
select 4, 'dddd' ,'02'select * from tb t WHERE
EXISTS(SELECT * FROM tb WHERE id=t.id AND code<>t.code)
and t.code in ('01','02')结果:
1 aaaa 01
2 bbbb 01
1 aaaa 02
2 bbbb 02