如何："Get user numbers for users who played the same game on all consoles."

Question

我已经尝试这个查询几个小时了。我当前的查询只为每个用户提供编号，并忽略几乎所有嵌套查询。例如：

SELECT DISTINCT p.UNum
FROM Plays AS p
WHERE p.CNum IN
(SELECT p.CNum
FROM Plays AS p
WHERE EXISTS
(SELECT COUNT(p.GNum) AS GNumCount, COUNT(p.UNum) AS UNumCount
FROM Plays AS p
GROUP BY p.CNum
HAVING COUNT(p.GNum) = COUNT(p.UNum)));

这只给了U10-U50。我想返回的答案是使用嵌套查询的 U20。请帮助我找出我做错了什么以及如何解决它。谢谢。

|用户|

---------------------------------------
| UNum | UserName | Mastery | Hometown|
---------------------------------------
| U10  | Sheldon   | 20  | Tokyo |
| U20  | Missy   | 10  | NewYork |
| U30  | Meemaw   | 30  | NewYork |
| U40  | George   | 20  | Tokyo |
| U50  | Mary | 30  | Seattle |

|游戏|

---------------------------------------
| GNum | GameName | ListPrice | Version | CityCreated |
---------------------------------------
| G100  | Pong   | 9.99  | 12 | Tokyo |
| G200  | PacMan   | 24.99  | 6 | NewYork |
| G300  | Zelda   | 19.99  | 8 | Peking |
| G400  | Doom   | 9.99  | 15 | Tokyo |
| G500  | MarioBros | 19.99  | 10 | NewYork |
| G600  | Pitfall | 9.99  | 4 | Tokyo |

|控制台|

---------------------------------------
| CNum | ConsoleName | Price | ManuLoc |
---------------------------------------
| C1  | Atari   | 99.99  | NewYork |
| C2  | Intellivision   | 129.99  | Peking |
| C3  | Nintendo   | 119.99  | Seattle |
| C4  | PlayStation   | 89.99  | Seattle |
| C5  | Coleco | 189.99  | Tokyo |
| C6  | GameBoy | 129.99  | Poughkeepsie |
| C7  | SegaGenesis | 129.99  | Tokyo |

|戏剧|

---------------------------------------
| UNum | GNum | CNum | TimesPlayed |
---------------------------------------
| U10  | G100 | C1  | 22 |
| U10  | G100 | C4  | 72 |
| U20  | G300 | C1  | 44 |
| U20  | G300 | C2  | 25 |
| U20  | G300 | C3  | 27 |
| U20  | G300 | C4  | 55 |
| U20  | G300 | C5  | 69 |
| U20  | G300 | C6  | 44 |
| U20  | G300 | C7  | 86 |
| U20  | G500 | C2  | 14 |
| U30  | G300 | C1  | 21 |
| U30  | G400 | C2  | 52 |
| U40  | G600 | C3  | 33 |
| U40  | G600 | C7  | 38 |
| U50  | G100 | C4  | 15 |
| U50  | G200 | C2  | 27 |
| U50  | G200 | C4  | 14 |
| U50  | G300 | C4  | 2 |
| U50  | G400 | C4  | 8 |
| U50  | G500 | C4  | 44 |
| U50  | G500 | C5  | 56 |
| U50  | G500 | C7  | 1 |
| U50  | G600 | C2  | 24 |
| U50  | G600 | C4  | 55

Answer 1

嗯，首先GROUP BY用户和游戏。在HAVING子句检查中，不同控制台的计数是否等于所有控制台的计数。这应该为您提供用户在所有主机上玩过的游戏的用户游戏对。USeDISTINCT每个用户只获得一次。

SELECT DISTINCT
unum
FROM plays
GROUP BY unum,
gnum
HAVING count(DISTINCT cnum) = (SELECT count(*)
FROM consoles)) x;