SQL查询:过去7天内按舞者姓名排列的频道浏览和单击次数

Channel_Statistics 桌子

我看到了 channel_views 和 clicks 链接到 channel_id .

Video_Channel

channel_dancer_id 链接到 频道id . fk是为了 dancer_id 在 Dancers 桌子。

舞者 桌子

舞者 链接到 dancers_name .

    CREATE TABLE Dancers (
      dancers_id int NOT NULL,
      dancers_name varchar(255) NOT NULL,
      PRIMARY KEY (dancers_id)
    ); 

    CREATE TABLE Video_Channel (
      channel_id int NOT NULL,
      channel_name varchar(255) NOT NULL,
      channel_dancer_id int,
      PRIMARY KEY (channel_id),
      FOREIGN KEY (channel_dancer_id) REFERENCES Dancers(dancer_id)
    );

    CREATE TABLE Channel_Statistics (
      channel_id int NOT NULL,
      _date date,
      channel_views int,
      clicks int,
      country varchar(255),
      FOREIGN KEY (channel_id) REFERENCES Video_Channel(channel_id)
    );

我试过的

SELECT COUNT(Channel_Statistics.channel_views), COUNT(Channel_Statistics.clicks), Dancers.dance_name
FROM Channel_Statistics
INNER JOIN Video_Channel ON Channel_Statistics.channel_id=Video_Channel.channel_id
INNER JOIN Dancers ON Video_Channel.channel_dancer_id=Dancers.dancer_id
WHERE _date > NOW() - INTERVAL 7 DAY
GROUP BY Dancers.dancer_name;