代码之家 › 专栏 › 技术社区 › Karol Janyst

SQL-按其他表中的值对项进行排序的正确查询

mysql sql

Karol Janyst · 技术社区 · 16 年前

我对正确的查询有点问题,希望你能帮我。我的测试表结构如下:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ;

CREATE TABLE IF NOT EXISTS `user_throws` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `length` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
) ;

它们之间的关系是多对一的(一个用户可以有多个抛出)。问题是我需要为所有用户做一个排名页面,比如说前100名投手。所以我需要一个查询结果:

按用户筛选,因此只有有任何抛出且抛出长度大于0的用户才是正确的记录。
按抛出的长度排序,因此只会追加最佳(最大)结果。

为此,我写了一个查询:

SELECT  `user_throws`.`length` , `users` . * 
FROM  `users` 
JOIN  `user_throws` ON (  `user_throws`.`user_id` =  `users`.`id` ) 
WHERE  `user_throws`.`length` >  '0'
GROUP BY  `users`.`id` 
ORDER BY  `user_throws`.`length` DESC

但问题是结果没有按最长距离排序。它过滤长度大于0的抛出,并显示唯一的用户,但附加的长度与所需的排序不一致。我发现 length user_throws id 与用户关联的。正确的查询应该是什么样的?

8 回复 | 直到 16 年前

Rudu Andrew Whitaker 16 年前

尝试:

SELECT longthrow,id,name FROM (
SELECT max(t.length) longthrow,u.id,u.name
FROM users u, user_throws t
WHERE u.id=t.user_id
 AND t.length>0
GROUP BY u.id
) ORDER BY longthrow DESC

它应该为您提供每个用户中最长的抛出时间,按最远的抛出时间优先、最短的最后一个进行排序—认为这就是您想要的;)

SELECT max(t.length) longthrow,u.id,u.name

比:

SELECT max(t.length) longthrow,u.*

Jeremy Goodell 16 年前

SELECT MAX(`user_throws`.`length`) , `users` . * 
...

Rohrbs 16 年前

添加 having group by

SELECT  `user_throws`.`length` , `users` . * 
FROM  `users` 
JOIN  `user_throws` ON (  `user_throws`.`user_id` =  `users`.`id` ) 
GROUP BY  `users`.`id` 
HAVING `user_throws`.`length` > 0
ORDER BY  `user_throws`.`length` DESC

编辑:我现在知道这不是你要找的。你在寻找每个用户的最佳投球机会。其他一些答案更适用。

Nik 16 年前

尝试

SELECT  u2.`length` , u . * 
FROM  `users` u ,  `user_throws` u2 where u2.`user_id` =  u.`id` 
AND  u2.`length` >  '0' and u2.`id` = (select u1.id from `user_throws` u1 where u1.`user_id` =  u.id  order by u1.`length` desc limit 1)
GROUP BY  u2.`user_id`

Paul Sasik 16 年前

您可以尝试如下子查询解决方案:

   SELECT  
      (SELECT MAX(length) FROM user_throws WHERE user_id=users.id) AS LongThrow,
      users.* 
   FROM  users
   WHERE (SELECT MAX(length) FROM user_throws WHERE user_id=users.id) >  '0'
   ORDER BY (SELECT MAX(length) FROM user_throws WHERE user_id=users.id)

注: 我脑子里想不起语法。。。请加点盐吃。此外,上面的查询非常冗长,但应该可以直接进行。你可以把它缩写为你认为合适的。

user359040 user359040 16 年前

稍有不同的方法:

select ut.longest, u.id, u.name
from users u
join
(select user_id, max(length) longest
 from user_throws
 where length > 0
 group by user_id) ut on u.id = ut.user_id
order by ut.longest desc

dexah 16 年前

选择最大值(用户_1.长度),用户。*从用户加入用户_用户id= 用户id)WHERE用户_1.长度>“0”分组依据用户id按用户排序_1.长度描述

我试过了,它是有效的,只要检查两个选项的执行时间

robert vano 14 年前

SELECT  `user_throws2`.`length` , `users` . * 
FROM  `users` 
JOIN  (SELECT  * FROM user_throws ORDER  BY length DESC ) as user_throws2 
    ON (  `user_throws2`.`user_id` =  `users`.`id` ) 
WHERE  `user_throws2`.`length` >  '0'
GROUP BY  `users`.`id` 
ORDER BY  `user_throws2`.`length` DESC

试试看,在上一个mysql上运行(如果在你的mysql中不工作,请更新:D)