mysql - 查询 mysql select 获取捐赠给 child 最多的玩具

Question

从下面的数据库中，我这几天一直在努力寻找捐赠给 children 最多的玩具。我对 mysql 的经验有限，到目前为止我通过这个论坛和网络的搜索还没有让我明白我做错了什么。这是包含插入数据的数据库:

DROP DATABASE IF EXISTS gifts;

CREATE DATABASE gifts ;
USE gifts ;

CREATE TABLE children
 (
      id_children INT NOT NULL  PRIMARY KEY AUTO_INCREMENT ,
      name_child VARCHAR(50) NOT NULL,
      age INT NOT NULL
 ) ;


CREATE TABLE toys 
(
  id_toy INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
  name_toy VARCHAR(50) NOT NULL,
  age_adequate INT NOT NULL
) ;


CREATE TABLE gifts
(
  id_children INT NOT NULL  ,
  id_gift INT NOT NULL ,

  PRIMARY KEY(id_children, id_gift),
  FOREIGN KEY (id_children)    REFERENCES children(id_children),
  FOREIGN KEY (id_gift) REFERENCES toys(id_toy)
) ;




 INSERT INTO children
 VALUES 
     (1,'Juan Perez',5),
     (2,'Jordi Lopez',8),
     (3,'Marta Santiago',12),
     (4,'Laura Martinez',5),
     (5,'Gerard Laudo', 2) ;





 INSERT INTO toys
 VALUES 
       (1,'Scalextric',12),
       (2,'Barbie',3),
       (3,'Bicicleta',5),
       (4,'Monopoly',12),
       (5,'Casa Feber',1),
       (6,'Palacio princesas',5),
       (7,'Nintendo DS',12),
       (8,'Puzzle',5),
       (9,'Sonajero',1);




 INSERT INTO gifts
 VALUES 

   (1,3),
   (2,3),
   (2,8),
   (3,3),
   (3,7),
   (3,4),
   (4,2),
   (4,8),
   (5,9) 

这是我正在实现的咨询。

 select name_toy,max(times_given_as_gift) as max_gift
 from toys t, 

( select id_gift,count(*) as times_given_as_gift from gifts g

group by id_gift ) as aux 

where aux.id_gift = t.id_toy

group by times_given_as_gift

然后我在查询结果中得到了这个:

  name_toy       max_gift

  Barbie          1
  Puzzle          2
  Bicicleta       3

在本例中，Bicicleta 确实是被捐赠次数最多的玩具，因为我从表数据中检查过它，但我只希望它在查询结果中显示为单行。我怎样才能实现这一目标？

任何建议/注释/指导都会有帮助。先谢谢了。

Answer 1

你们已经很接近了。只需添加 order by 和 limit 子句即可:

 select name_toy, ;max(times_given_as_gift) as max_gift
 from toys t join
      (select id_gift, count(*) as times_given_as_gift
       from gifts g
       group by id_gift
      ) aux 
     on aux.id_gift = t.id_toy
 group by times_given_as_gift
 order by max_gift desc
 limit 1;

我还“修复”了查询以使用正确的 ANSI 标准 join 语法。