列出每个类别中哪个竞争最激烈的成员得分最高

由 发布于

我有4张桌子：

用户

create table user (
  id int not null identity(1,1) primary key,
  name varchar(30),
  soyad varchar(50),
  city varchar(30),
  e_mail varchar(100),
  pass varchar(10)
);

类别

create table category(
  id int not null primary key,
  name varchar(50)
);

竞争

create table competition(
  id int primary key,
  date date,
  category_id int foreign key references category(id)
);

比赛用户

create table competition_user(
  id int primary key,
  competition_id int foreign key references competition(id),
  joker_id int  foreign key references joker(joker_id),
  user_id int  foreign key references user(id),
  point int
);

我想为此编写SQL代码：

  • 编写SQL查询，该查询将列出每个类别中竞争最激烈的成员中得分最高，在哪个类别中竞争，以及正确的问题数和错误的问题数。

我写这个SQL查询但是失败了：

SELECT category.id, user.id, count(competition_user.competition.id) AS competitioncount 
FROM user, competition_user, competition, category
WHERE competition.id = competition_user.competition_id AND compettion.category_id = category.id AND user.id = competition.user_id
GROUP BY category.id, user.id 
ORDER BY competitioncount desc