mysql 一对多，根据多表的排序显示A_一对多的时候,多的那张表进行排序-优快云博客

文章讲述了如何使用SQL查询从用户表和用户奖励表中找出单次奖励金额最高的用户，提供了三种不同的查询思路，包括先排序去重再JOIN、直接在ORDERBY中嵌套查询以及考虑无奖励用户的特殊情况.

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

时间：2021-07-09 17:38:53

表如下：

用户表、用户奖励表，用户表与奖励表一对多关系。

图片.png

CREATE TABLE `ht_user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `username` (`username`)

) ENGINE=InnoDB AUTO_INCREMENT=334 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `ht_reward` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `uid` int(11) NOT NULL COMMENT '用户uid',

  `money` decimal(10,2) NOT NULL COMMENT '奖励金额',

  `datatime` datetime NOT NULL COMMENT '时间',

  `created` int(10) DEFAULT '0',

  PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='奖励表';


INSERT INTO `ht_reward`(`id`, `uid`, `money`, `datatime`, `created`) VALUES (1, 111, 300.00, '2019-12-17 10:25:27', 1576549527);

INSERT INTO `ht_reward`(`id`, `uid`, `money`, `datatime`, `created`) VALUES (2, 222, 100.00, '2019-12-17 10:11:46', 1576548706);

INSERT INTO `ht_reward`(`id`, `uid`, `money`, `datatime`, `created`) VALUES (3, 222, 600.00, '2019-12-17 10:12:15', 1576548735);

INSERT INTO `ht_reward`(`id`, `uid`, `money`, `datatime`, `created`) VALUES (4, 333, 500.00, '2019-12-17 10:12:31', 1576548751);

INSERT INTO `ht_reward`(`id`, `uid`, `money`, `datatime`, `created`) VALUES (5, 111, 500.00, '2019-12-17 10:12:45', 1576548765);

INSERT INTO `ht_reward`(`id`, `uid`, `money`, `datatime`, `created`) VALUES (6, 333, 200.00, '2019-12-17 10:12:57', 1576548777);

INSERT INTO `ht_reward`(`id`, `uid`, `money`, `datatime`, `created`) VALUES (7, 333, 700.00, '2019-12-17 10:13:07', 1576548787);

INSERT INTO `ht_reward`(`id`, `uid`, `money`, `datatime`, `created`) VALUES (8, 222, 400.00, '2019-12-17 10:13:18', 1576548798);

INSERT INTO `ht_reward`(`id`, `uid`, `money`, `datatime`, `created`) VALUES (9, 111, 350.00, '2019-12-17 10:13:29', 1576548809);



INSERT INTO `ht_user`(`id`, `username`) VALUES (111, 'AAA');

INSERT INTO `ht_user`(`id`, `username`) VALUES (222, 'BBB');

INSERT INTO `ht_user`(`id`, `username`) VALUES (333, 'CCC');

问题：

现在要按单次金额最高的金额把用户查出来

思路1：先按奖励排序去重查到最高金额，再联用户表查到

1

2

3

4

5

6

7

8

SELECT

    a.username,

    b.money

FROM

    ht_user a

    LEFT JOIN ( SELECT * FROM ( SELECT * FROM ht_reward ORDER BY money DESC LIMIT 999999 ) r GROUP BY r.uid ) b ON a.id = b.uid

GROUP BY

    a.id

思路2：在order by 中写查询

1

2

3

4

5

6

SELECT

    a.username

FROM

    ht_user a

ORDER BY

    ( SELECT b.money FROM ht_reward b WHERE b.uid = a.id ORDER BY b.money DESC LIMIT 1 ) DESC;

思路3：先查产奖励表排序去重再联用户用，此思路会有一个问题，无奖励用户需单独处理

1

#暂时没写