mysql - 如何根据这个标准检索数据？

Question

我有下表:

付款表

CREATE TABLE Payment (
    PayID INTEGER,
PayName CHAR (55),
PaymentDescription VARCHAR (100),
    Primary Key (PayID)
);

用户表

CREATE TABLE Users (
    UserID INTEGER, 
    UserFirst CHAR (40),
    UserLast CHAR (40),
    UserName VARCHAR (40),
    UserAddress VARCHAR (35),
    UserBirthdate DATE,
    UserGender CHAR (1),
    UserEmail VARCHAR (35),
    PRIMARY KEY (UserID)
);

类型表

CREATE TABLE GENRE (
    GenreID Integer,
    Name CHAR(45),
    Description VARCHAR (250),
    PRIMARY KEY (GenreID)
);

歌曲表

CREATE TABLE SONG (
    SongID Integer,
    GenreID Integer,
    Title VARCHAR (50),
    Album VARCHAR (40),
    Duration TIME,
    ReleaseYear DATE,
    PRIMARY KEY (SongID),
    CONSTRAINT foreignkey_holds_GenreID FOREIGN KEY (GenreID) 
    REFERENCES Genre(GenreID)
);

Song_Users 表

CREATE TABLE Song_Users (
UserID INTEGER, 
SongID Integer,
PRIMARY KEY (UserID,SongID),
CONSTRAINT fk_users_song_user FOREIGN KEY (UserID) 
    REFERENCES Users(UserID),
CONSTRAINT fk_users_song_song FOREIGN KEY (SongID) 
    REFERENCES SONG(SongID)
);

Payment_Users 表

CREATE TABLE Payment_Users (
UserID INTEGER,
PayID INTEGER,
PRIMARY KEY (UserID,PayID),
CONSTRAINT fk_users_payment_user FOREIGN KEY (UserID) 
    REFERENCES Users(UserID),
CONSTRAINT fk_users_payment_pay FOREIGN KEY (PayID) 
    REFERENCES Payment(PayID)
);

我还填充了表格:

付款

INSERT INTO Payment (PayID,PayName,PaymentDescription) VALUES (1, 'Visa', 'Applies to VISA Debit, Electron, Credit')

INSERT INTO Payment (PayID,PayName,PaymentDescription) VALUES (2, 'Mastercard', 'Applies across all types')


INSERT INTO Payment (PayID,PayName,PaymentDescription) VALUES (3, 'PayPal', 'Payment made using PayPal')

INSERT INTO Payment (PayID,PayName,PaymentDescription) VALUES (4, 'ApplePay', 'Payment method for IOS users')

用户

INSERT INTO Users (UserID, UserFirst, UserLast, UserName, UserAddress, UserBirthdate, UserGender, UserEmail) VALUES (1, 'Jane', 'Johnson', 'jjsweet135', '10 House Wood Street',19970321, 'F', 'jj1997@gmail.com')


INSERT INTO Users (UserID, UserFirst, UserLast, UserName, UserAddress, UserBirthdate, UserGender, UserEmail) VALUES (2, 'Joe', 'Watson', 'joewatson98', '21 House Red Street',19980414, 'M', 'joewatson1998@gmail.com')


INSERT INTO Users (UserID, UserFirst, UserLast, UserName, UserAddress, UserBirthdate, UserGender, UserEmail) VALUES (3, 'Katie', 'Davies', 'katiethebest425', '17 House Fun Street',20000318, 'F', 'katiebest452@hotmail.com')

INSERT INTO Users (UserID, UserFirst, UserLast, UserName, UserAddress, UserBirthdate, UserGender, UserEmail) VALUES (4, 'Tom', 'Branson', 'tommylad100', '27 House Church Street',20010719, 'M', 'tommylad35@orange.com')

类型表

INSERT INTO Genre (GenreID, Name, Description) VALUES  

(1, 'Electro Swing', 'A musical journey set in the 90s')

INSERT INTO Genre (GenreID, Name, Description) VALUES  

(2, 'House', 'When the beat shakes your house')

INSERT INTO Genre (GenreID, Name, Description) VALUES  

(3, 'Classical', 'To stimulate neural activity in your brain')


INSERT INTO Genre (GenreID, Name, Description) VALUES  

(4, 'Rap', 'Experience the streets from the comfort of your own home')


INSERT INTO Genre (GenreID, Name, Description) VALUES  

(5, 'Pop', 'Follow the status quo')

歌曲表

INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (1, 1, 'Swing the Electro', 'Electro Swing Vol 1', 11537, 19970316)


INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (2, 2, 'This is my House', 'UK House Anthem', 11454, 19990419)


INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (3, 3, 'Candy Shop', '50 Cent Classics', 2254, 20010324)

INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (4, 4, 'Mozart', 'Classical strokes', 44524, 18000225)

INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (5, 5, 'Katy Perry', 'Baby you are a Firework', 13524, 20050324)

INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (6, 5, 'One Direction', 'We are the one', 013204, 20060211)

INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (7, 5, 'Chainsmokers', 'In the back seat of your rover', 33544, 20160123)

INSERT INTO SONG (SongID, GenreID, Title, Album, Duration, ReleaseYear) VALUES (8, 5, 'Justin Bieber', 'Teenage dream', 22222, 20150325)

Song_Users 表

INSERT INTO Song_Users (UserID, SongID) VALUES (1,1)

INSERT INTO Song_Users (UserID, SongID) VALUES (2,2)

INSERT INTO Song_Users (UserID, SongID) VALUES (3,3)

INSERT INTO Song_Users (UserID, SongID) VALUES (4,4)

INSERT INTO Song_Users (UserID, SongID) VALUES (4,1)

INSERT INTO Song_Users (UserID, SongID) VALUES (4,2)

INSERT INTO Song_Users (UserID, SongID) VALUES (4,3)


INSERT INTO Song_Users (UserID, SongID) VALUES (4,5)

INSERT INTO Song_Users (UserID, SongID) VALUES (4,6)

INSERT INTO Song_Users (UserID, SongID) VALUES (4,7)

INSERT INTO Song_Users (UserID, SongID) VALUES (4,8)

Payment_Users 表

INSERT INTO Payment_Users VALUES (1,1)
INSERT INTO Payment_Users VALUES (2,2)
INSERT INTO Payment_Users VALUES (3,3)
INSERT INTO Payment_Users VALUES (4,3)

我需要找到最受女性欢迎的音乐类型 (F)。我似乎无法检索此信息，因为流派(GenreID)没有相应的外键。

如何创建查询来获取女性中最受欢迎的类型？

编辑

SELECT Users.UserID, UserGender, SongID, GenreID FROM Users, Song_Users, Song

WHERE Users.UserID = Song_Users.UserID 

AND 

WHERE Genre.GenreID = Song.GenreID;

我正在尝试将 GenreID 与 SongID 链接，但我不知道该怎么做。有一个带有外键的链接，但不在 Genre 表中，而仅在 Song 表中。我的逻辑是选择用户、他们的性别、他们的歌曲，然后选择相应的流派。然后我可以使用 HAVING CLAUSE 之类的内容来严格选择女性，并使用 COUNT(流派)和适当的 GROUPBY 函数

Answer 1

根据上面的评论，这个问题中有大量不必要的信息。应包含一组示例数据和您的努力证据，但用于填充表格的命令无关紧要。

我假设流行度的定义如下:购买给定流派歌曲的最大用户数量。

根据上面的内容，您可以通过四个表的内部联接来完成您正在寻找的内容:

SELECT 
    count(u.UserID) as 'popularity', 
    g.Name as 'genre' 
FROM
    Users u INNER JOIN
    Song_Users su INNER JOIN
    SONG s INNER JOIN
    GENRE g
ON
    u.UserID = su.UserID,
    su.SongID = s.SongID,
    s.GenreID = g.GenreID
WHERE
    u.UserGender = 'F'
GROUP BY
    g.Name
ORDER BY
    count(u.UserID) DESC