作者TeemingVoid (TeemingVoid)
标题Re: [SQL ] mysql如何交集查询条件?
时间Tue Jan 21 22:39:26 2014
※ 引述《SonEat (善液)》之铭言：
: 再追加一个差集的问题
: 我把原表再多一栏
: 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
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 MoleculeID not in (
select MoleculeID from MoleculeElement
where Element = 'Na' and Number = 3
)
-- 或者 --
select MoleculeID
from (
select MoleculeID, Element from MoleculeElement where Element = 'Bi'
) as A
join (
select MoleculeID, Element from MoleculeElement
where Element = 'Na' and Number <> 3
) as B
using (MoleculeID)
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 114.41.97.177
1F：推 SonEat:再次感谢大大指教 01/21 23:34