复杂的SQL查询问题( 6.3)

Question

我很难弄清楚如何写这个查询。让我解释一下情况。

所以，问题是，我需要显示所有得分大于99的球员的名字，他们在某个球员(例如pid = 1)的所有场地上都打过比赛，并且得分大于99。(除了一个pid =1的场地外，他们还可以在其他场地打球，但最低要求是他们必须在与他相同的场地上打球)。

我有一个数据库，其中包括三个表:球员，场地，比赛。并跟踪数据。

create database test1;
use test1;

CREATE TABLE `player` (
  `pid` int(11) NOT NULL,
  `pname` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ground` (
  `gid` int(11) NOT NULL,
  `gname` varchar(20) DEFAULT NULL,
  `country` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `matches` (
  `pid` int(11) DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `player` ADD PRIMARY KEY (`pid`);

ALTER TABLE `ground` ADD PRIMARY KEY (`gid`);

ALTER TABLE `matches`
  ADD KEY `gid` (`gid`),
  ADD KEY `pid` (`pid`);

INSERT INTO `player` (`pid`, `pname`) VALUES
(1, 'afridi'),
(2, 'kohli'),
(3, 'imam'),
(4, 'fawad'),
(5, 'baven'),
(6, 'awais');

INSERT INTO `ground` (`gid`, `gname`, `country`) VALUES
(1, 'Qaddafi', 'PK'),
(2, 'National', 'PK'),
(3, 'Eden Garden', 'IND'),
(4, 'Lords', 'ENG'),
(5, 'MCG', 'AUS'),
(6, 'Arbab Nayyaz', 'PK');

INSERT INTO `matches` (`pid`, `gid`, `score`) VALUES
(1, 2, 23),
(1, 1, 111),
(2, 3, 107),
(2, 5, 103),
(1, 3, 117),
(1, 4, 55),
(1, 5, 101),
(1, 6, 44),
(2, 6, 103),
(2, 4, 103),
(2, 2, 117),
(2, 1, 103),
(4, 1, 77),
(3, 1, 13),
(5, 2, 22),
(3, 2, 101),
(3, 3, 101),
(5, 1, 101),
(5, 4, 101),
(5, 5, 101),
(6, 1, 101),
(6, 2, 101),
(6, 3, 101),
(6, 4, 101),
(6, 5, 101),
(6, 4, 101);

一个相对简单的数据库。

我编写了下面的查询，其中显示了4名玩家的名字。它显示了所有在pid = 1的场地上玩过的球员。如何只显示，只显示那些在pid = 1相同场地上玩过的球员。

select p.pname 
from player p
join matches mn on mn.pid = p.pid
where (p.pid != 1) and (mn.score > 99) and exists (select m.gid from matches m where (m.pid = 1) and (mn.gid = m.gid))
group by pname;

根据表中提供的数据，Afridi (pid = 1)在以下几个方面得分: 1、3和5。

球员(pid) 2，3，5 ,6的得分分别为1，3，5和5。

这些玩家已经在其他地方创造了数百年，但是这个查询显示了所有在这三个场地中的任何一个场地上玩过的玩家。

球员也可以在其他场地打球，但最低要求是球员必须在所有场地上比赛；1，3，5。

所以，我需要的是，只有所有的，那些球员，在所有相同的场地，在场地，1，3，5。

通过观察表中的数据，我们可以看到所有相同场地的球员只有2，即pid = 2，6。

知道该怎么做吗？

Answer 1

我认为这个查询应该做你想做的事。它创建了一个场地表，其中第一个球员已经玩了一个世纪(g1)，并加入到球员谁也曾在这些场地。如果另一名球员所打的不同场地的数目与第一名球员所打的不同场地的数目相同，则他们必须在同一组场地上玩。注意，有几个地方(在两个子查询中)需要设置播放器id以进行比较。

SELECT p.pname
FROM (SELECT gid, pid FROM matches WHERE pid=1 AND score >= 100) g1
LEFT JOIN matches m
ON m.gid = g1.gid AND m.pid != g1.pid
JOIN player p
ON p.pid = m.pid
GROUP BY m.pid
HAVING COUNT(DISTINCT m.gid) = (SELECT COUNT(DISTINCT gid) FROM matches WHERE pid=1 AND score >= 100)
ORDER BY m.pid

SQLFiddle演示