Wiem, że jest podobny do Jak zrobić pełny zewnętrzny dołączyć MySQL?, ale używam go w podzapytaniu

Próbuję uzyskać całkowitą grę (liczba zbiegów beatmaps.id w rankingach.beatMapid), w tym beatmaps z gwiazdami

Mój rzeczywisty kod zawsze zwraca 1 w rzędzie Casteplay ...

BeatmapSetID    BeatmapID BeatmapSetCreatorUsername TotalPlays
     1             11              Legend               1
     1             12              Legend               1
     2             13              Legend               1
     2             14              Legend               1
     3             15              Darkar               1

Spodziewany wynik:

BeatmapSetID    BeatmapID BeatmapSetCreatorUsername TotalPlays
     1             11              Legend               0
     1             12              Legend               2
     2             13              Legend               3
     2             14              Legend               2
     3             15              Darkar               1

Co robię źle?

To jest mój kod:

SELECT
        BeatmapSets.id AS BeatmapSetID,
        Beatmaps.id AS BeatmapID,
        Account.username AS BeatmapSetCreatorUsername,

    (SELECT COUNT(plays) FROM
     (SELECT Rankings.beatmapID, COUNT(Rankings.id) as plays FROM Beatmaps 
       LEFT OUTER JOIN Rankings ON Beatmaps.id = Rankings.beatmapID
    
     UNION
     
     SELECT Rankings.beatmapID, COUNT(Rankings.id) as plays FROM Beatmaps 
       RIGHT OUTER JOIN Rankings ON Beatmaps.id = Rankings.beatmapID
     ) Rankings
    ) AS TotalPlays

    FROM BeatmapSets AS BeatmapSets

    INNER JOIN Beatmaps AS Beatmaps ON BeatmapSets.id = Beatmaps.setID
    INNER JOIN Account AS Account ON BeatmapSets.creatorID = Account.id

Przykładowe dane:

SET sql_mode = '';
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

CREATE TABLE Rankings (
  `id` INT NOT NULL,
  `userID` INT NOT NULL,
  `beatmapID` INT NOT NULL,
  `score` INT NOT NULL
);

INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (1, 1, 12, 500);
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (2, 1, 12, 1000);
                                    
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (3, 1, 13, 1000);
                                    
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (4, 2, 13, 1000);
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (5, 2, 13, 2000);       
                                    
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (6, 1, 14, 2000);                                   
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (7, 2, 14, 3000); 
                                    
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (8, 2, 15, 1000);                                      
                                    
CREATE TABLE Account (
  `id` INT NOT NULL,
  `username` varchar(50) NOT NULL
);

INSERT INTO Account (id, username) VALUES (1, "Legend");
INSERT INTO Account (id, username) VALUES (2, "Darkar");

CREATE TABLE BeatmapSets (
  `id` INT NOT NULL,
  `creatorID` INT NOT NULL
);

INSERT INTO BeatmapSets (id, creatorID) VALUES (1, 1);
INSERT INTO BeatmapSets (id, creatorID) VALUES (2, 1);
INSERT INTO BeatmapSets (id, creatorID) VALUES (3, 2);

CREATE TABLE Beatmaps (
  `id` INT NOT NULL,
  `setID` INT NOT NULL
);

INSERT INTO Beatmaps (id, setID) VALUES (11, 1);
INSERT INTO Beatmaps (id, setID) VALUES (12, 1);
INSERT INTO Beatmaps (id, setID) VALUES (13, 2);
INSERT INTO Beatmaps (id, setID) VALUES (14, 2);
INSERT INTO Beatmaps (id, setID) VALUES (15, 3);

Skrzypce: https://www.db-fiddle.com/f/3y5m7dsfeldgpc23kimdp/ 4.

1
Arturo Rodriguez 22 lipiec 2020, 07:50

1 odpowiedź

Najlepsza odpowiedź

Wymiana wewnętrznego pełne połączenie z poniższym kodem wydaje się rozwiązać swój problem:

(SELECT COUNT(Rankings.id) FROM Rankings WHERE Rankings.beatmapID = Beatmaps.id) AS TotalPlays

Dzięki następującym DB Fiddle do wykazania wyniku.

1
Fullslack 22 lipiec 2020, 08:02