mysql - MySQL 中的关系和 QUERY 问题

Question

您好，我有两个关于 MySQL 和 QUERIES 的问题。

• 首先，我想知道如果我在 phpmyadmins 设计器中设置表之间的关系或者不设置，在形成查询时会有什么不同？

• 其次我有 3 个表并且在我的查询中有一些逻辑错误似乎 MAX() 函数和 GROUP BY 不能很好地结合在一起 ://

所以如果有人知道如何解决我的问题，请分享:)

是的，我正在使用 MySQL 5.1.41 并且我建立了表之间的关系

CREATE TABLE  `sandbox`.`videos` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 255 ) NOT NULL ,
`views` INT NOT NULL ,
`id_user` INT UNSIGNED NOT NULL ,
`id_type` INT UNSIGNED NOT NULL ,
INDEX (  `id_user` ,  `id_type` )
) ENGINE = INNODB;

id title views id_user id_type
------------------------------------
1 video1 50  1  1
2 video2 55  3  1
3 video3 100  2  3
4 video4 20  5  3
5 video5 62  4  5



CREATE TABLE  `sandbox`.`users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL
) ENGINE = INNODB;

id name
-----------
1 adam
2 mike
3 chuck
4 walker
5 nancy



CREATE TABLE  `sandbox`.`types` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL
) ENGINE = INNODB;

id name
-------------
1 20 min
2 30 min
3 50 min
4 90 min
5 120 min

我的查询:

SELECT videos.id, videos.title, MAX(videos.views) AS views, users.name AS user, types.name AS type 
FROM videos, users, types 
WHERE videos.id_user = users.id 
AND videos.id_type = types.id 
GROUP BY id_type  
ORDER BY id_type ASC
LIMIT 0 , 20;

查询结果:

id title views user type
------------------------------------
1 video1 55  adam 20 min
3 video3 100  mike 50 min
5 video5 62  walker 120 min

GROUP BY 函数按类型对所有条目进行分组，并且仅从组中的第一个条目获取数据，但是由于 MAX() 函数，它从 View 中复制最大值并将其写入组中的第一个条目。

想要的结果:

id title views user type
------------------------------------
1 video2 55  chuck 20 min
3 video3 100  mike 50 min
5 video5 62  walker 120 min

我需要的是它选择所有数据(还有标题和用户名)，不仅查看数据并按类型分组。

感谢任何帮助:)

Answer 1

首先，MIN(), MAX, AVG(), COUNT(), SUM() 等聚合函数如果涉及其他列，例如您的示例...知道在什么时候考虑每个组...(除了像 SELECT MAX(Balance) from CustomerAccount 这样的东西——无论谁或什么，它都会获得最大余额)。

您需要做的是预先查询您的条件，然后获取与该限定符关联的元素。

SELECT 
      id_type, 
      max( views )
   from
      videos
   group by 
      id_type

现在，这为我提供了最内在的资格预审...对于每种视频类型，不管 ID 是多少，最大值是多少。现在，如果出现多个视频具有相同计数的问题，我还将在二级查询中使用 ID 的 MIN() 限定符。

select 
      PreQuery.id_type,     
      min( v.ID ) as FirstVideoIDByType
   from
      ( SELECT 
             id_type, 
             max( views )
          from
             videos
          group by 
             id_type ) PreQuery,
      Videos v
   where 
      PreQuery.id_type = v.id_type
   group by 
      PreQuery.id_type

最后，我将拥有每个视频类型的最大观看次数的 ID 的第一个实例，现在，获取这些特定视频的详细信息

select 
      FinalVideo.*
   from
      ( select 
             PreQuery.id_type,     
             min( v.ID ) as FirstVideoIDByType
          from
             ( SELECT 
                    id_type, 
                    max( views )
                 from
                    videos
                 group by 
                    id_type ) PreQuery,
             Videos v
          where 
             PreQuery.id_type = v.id_type
          group by 
             PreQuery.id_type ) PreQuery2,
      Videos v2
   where
      PreQuery2.FirstVideoIDByType = v2.id
   order by
      v2.id_type
   limit
      0, 20;

希望这能阐明事情如何以及为什么按照它们的方式工作...有时分解元素以获得您真正想要的东西很复杂。

如果您想包含可能与相同最大观看次数相关的所有视频，您可以跳过辅助查询，但加入相同的最大观看次数，通过

select 
      FinalVideo.*
   from
      ( SELECT 
              id_type, 
              max( views ) MaxViews
           from
              videos
           group by 
              id_type ) PreQuery,
       Videos v
    where 
           PreQuery.id_type = v.id_type
       and PreQuery.MaxViews = v.views
   order by
      v.id_type
   limit
      0, 20;