作者SonEat (善液)
看板Database
标题Re: [SQL ] mysql如何交集查询条件?
时间Tue Jan 21 21:49:57 2014
※ 引述《TeemingVoid (TeemingVoid)》之铭言：
: ※ 引述《SonEat (善液)》之铭言：
: : table
: : ID element
: : 1 Bi
: : 1 Na
: : 2 Bi
: : 2 Na
: : 2 O
: : 3 Bi
: : 3 C
: : 因为mysql没有Intersection查询的功能可用
: : 因此我的问题
: : 1. 我想查出至少包含Bi与Na的ID 也就是查询结果为 1,2
: : 我原本用select * from table where element='Bi' and element='Na'结果会查出空集合
: : select * from table where element='Bi' or element='Na' 则会查出 1,2,3
: -- 依你提出的资料为例:
: use test;
: create table MoleculeElement
: (
: MoleculeID int,
: Element varchar(2)
: );
: insert into MoleculeElement values
: (1,'Bi'),(1,'Na'),
: (2,'Bi'),(2,'Na'),(2,'O'),
: (3,'Bi'),(3,'C');
: -- 利用子查询分别找出两个元素的资料，再以 inner join 取交集
: -- 查出至少包含 Bi 与 Na 的 ID，也就是查询结果为 1, 2
: select A.MoleculeID
: from (
: select MoleculeID, Element from MoleculeElement where Element = 'Bi'
: ) as A
: join (
: select MoleculeID, Element from MoleculeElement where Element = 'Na'
: ) as B
: on (A.MoleculeID = B.MoleculeID);
: : 2. 我想查出只有包含Bi与Na两种元素的ID 也就是查询结果为 1
: -- 继续找出元素数量正好是二的分子，再取一次交集
: select MoleculeID
: from (
: select MoleculeID, Element from MoleculeElement where Element = 'Bi'
: ) as A
: join (
: select MoleculeID, Element from MoleculeElement where Element = 'Na'
: ) as B
: using (MoleculeID)
: join (
: select MoleculeID from MoleculeElement
: group by MoleculeID having count(*) = 2
: ) as C
: using (MoleculeID)
小弟愚钝
再追加一个差集的问题
我把原表再多一栏
table:MoleculeElement
MoleculeID Element Number
1 Bi 1
1 Na 3
2 Bi 1
2 Na 1
2 O 1
3 Bi 3
3 C 1
create table MoleculeElement
(
MoleculeID int,
Element varchar(2),
Number int
);
insert into MoleculeElement values
(1,'Bi',1),(1,'Na',3),
(2,'Bi',1),(2,'Na',1),(2,'O',1),
(3,'Bi',1),(3,'C',3);
我想查询同时有Na与Bi的结果，也就是原问题1，再排除Na的Number=3的结果
也就是查询结果只得到 2
小弟试着用left join与not exists都失败，请问该如何修正?
select MoleculeID
from (
select MoleculeID, Element from MoleculeElement where Element = 'Bi'
) as A
join (
select MoleculeID, Element from MoleculeElement where Element = 'Na'
) as B
using (MoleculeID)
left join (select MoleculeID, Element from MoleculeElement where Element =
'Na' and Number=3
) as C
using (MoleculeID);
失败，得到结果1,2
select MoleculeID
from (
select MoleculeID, Element from MoleculeElement where Element = 'Bi'
) as A
join (
select MoleculeID, Element from MoleculeElement where Element = 'Na'
) as B
using (MoleculeID)
where not exists
(select MoleculeID, Element from MoleculeElement where Element = 'Na' and
Number=3);
失败，得到Empty set
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 103.6.151.80