现有b两个表
a表结构如下
id 种类 单价 数量
1 a 2.3 3
b 26 6
c 3.6 25
2 a 2.5 9
b 36 5
c 0.36 20
3 a 6.5 20
d 9.6 3
......
需要查询 同时具有a,d两种种类的所有id号 ?
a表结构如下
id 种类 单价 数量
1 a 2.3 3
b 26 6
c 3.6 25
2 a 2.5 9
b 36 5
c 0.36 20
3 a 6.5 20
d 9.6 3
......
需要查询 同时具有a,d两种种类的所有id号 ?
(
select distinct id from a where 种类 = 'a'
union all
select distinct id from a where 种类 = 'b'
) t
group by id having count(*) = 2
insert into a values(1, 'a',2.3 ,3 )
insert into a values(1, 'b',26 ,6 )
insert into a values(1, 'c',3.6 ,25)
insert into a values(2, 'a',2.5 ,9 )
insert into a values(2, 'b',36 ,5 )
insert into a values(2, 'c',0.36,20)
insert into a values(3, 'a',6.5 ,20)
insert into a values(3, 'd',9.6 ,3 )
go select id from
(
select distinct id from a where 种类 = 'a '
union all
select distinct id from a where 种类 = 'b '
) t
group by id having count(*) = 2 --drop table a/*
id
-----------
1
2(所影响的行数为 2 行)
(/
insert into a values(1, 'a',2.3 ,3 )
insert into a values(1, 'b',26 ,6 )
insert into a values(1, 'c',3.6 ,25)
insert into a values(2, 'a',2.5 ,9 )
insert into a values(2, 'b',36 ,5 )
insert into a values(2, 'c',0.36,20)
insert into a values(3, 'a',6.5 ,20)
insert into a values(3, 'd',9.6 ,3 )
go select id from
(
select distinct id from a where 种类 = 'a'
union all
select distinct id from a where 种类 = 'd'
) t
group by id having count(*) = 2 drop table a/*
id
-----------
3(所影响的行数为 1 行)
*/
insert into #a values(1, 'a ',2.3 ,3 )
insert into #a values(1, 'b ',26 ,6 )
insert into #a values(1, 'c ',3.6 ,25)
insert into #a values(2, 'a ',2.5 ,9 )
insert into #a values(2, 'b ',36 ,5 )
insert into #a values(2, 'c ',0.36,20)
insert into #a values(3, 'a ',6.5 ,20)
insert into #a values(3, 'd ',9.6 ,3 )
go
select distinct id from #a t
where exists(select*from #a t1 where 种类 = 'a ' and t1.id=t.id )
and exists(select*from #a t2 where 种类 = 'd ' and t2.id=t.id )
id
-----------
3(影響 1 個資料列)
給我分數吧謝謝