mysql - 选择列值为 1 而不是 4 的人

Question

问题是:

列出购买了演出id 1而非4门票的顾客姓名。

相关表格1)性能

SaleId > SaleTime > Total > PerformanceId > PatronId
1   2014-02-12 12:10:11 100.00  1   1
2   2014-02-12 12:10:11 40.00   1   2
3   2014-02-12 12:10:11 30.00   1   3
4   2014-02-12 12:10:11 30.00   1   null
5   2014-02-12 12:10:11 100.00  4   1
6   2014-02-12 12:10:11 40.00   4   5
7   2014-02-12 12:10:11 30.00   4   3
8   2014-02-12 12:10:11 30.00   4   null
9   2014-02-12 12:10:11 100.00  7   1
10  2014-02-12 12:10:11 40.00   7   4
11  2014-02-12 12:10:11 30.00   7   3
12  2014-02-12 12:10:11 30.00   7   null

2)赞助人

PatronId > lastName > firstName

1   Paul      Smith
2   Linda     Odom
3   Gigi      Koo
4   Kailee    Jefferson
5   Kimberly  Heart
6   boyle     Heart
7   Kimberly  Beetle
8   boyle     Beetle
9   Joe       Junior
10  Jane      Junior
11  Junior    Junior
12  Kar       Kargoolie

我的 SQL 代码是:

select distinct lastName, firstName
from Patron p, ticketsale t1, ticketsale t2
where p.PatronId = t1.PatronId
and p.PatronId = t2.PatronId
and t1.PerformanceId = 1
and t2.PerformanceId <> 4;

这不起作用，因为它提供了 3 个名字(保罗、琳达、吉吉)，我应该只得到(琳达)

Answer 1

查询

使用 EXISTS 的子查询相反(不存在):

SELECT
  pt.lastname, pt.firstname
FROM
  patron pt
WHERE
  EXISTS ( 
           SELECT 1 
           FROM performance pf 
           WHERE pt.patronid = pf.patronid AND pf.performanceid = 1 
         )
  AND NOT EXISTS ( 
           SELECT 1 
           FROM performance pf 
           WHERE pt.patronid = pf.patronid AND pf.performanceid = 4 
         )

---

测试

请参阅 SQL Fiddle 上的实例

我只使用您在表性能样本数据中的顾客(不需要其他人来证明这一点):

create table patron(patronid int, lastname varchar(50), firstname varchar(50));
insert into patron values 
(1, 'Paul', 'Smith'),
(2, 'Linda', 'Odom'),
(3, 'Gigi', 'Koo'),
(4, 'Kailee', 'Jefferson'),
(5, 'Kimberly', 'Heart');

create table performance(performanceid int, patronid int);
insert into performance values
(1,1),(1,2),(1,3),(1,null),(4,1),(4,5),(4,3),(4,null),(7,1),(7,4),(7,3),(7,null);

我的查询返回:

lastname | firstname
---------+----------
Linda    | Odom