英文:
How to remove duplicates from mysql results while applying sort
问题
我有以下表格和数据：
CREATE TABLE `a_athletes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `a_events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`athlete_id` bigint(20) DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `a_events` (`id`, `name`, `athlete_id`, `date`) VALUES
(1, 'Long jump', 1, '2023-02-18'),
(2, 'Row', 1, '2023-02-09'),
(3, 'Sprint', 2, '2023-02-10'),
(4, 'Sprint', 1, '2023-02-14'),
(5, 'Long Jump', 2, '2023-02-20');
INSERT INTO `a_athletes` (`id`, `name`) VALUES
(1, 'Sarah'),
(2, 'Simon'),
(3, 'Barbera');
我想获取所有那些运动员没有与之关联的 'Row' 事件的结果。
我有以下查询可以实现这一目标：
select *
from a_athletes
left join a_events on a_athletes.id = a_events.athlete_id
where a_athletes.id not in (
select a_athletes.id
from a_athletes
inner join a_events on a_athletes.id = a_events.athlete_id
where a_events.`name` = 'Row'
)
order by a_events.`date`;
这将提供以下结果：
3,Barbera,NULL,NULL,NULL,NULL
2,Simon,3,Sprint,2,2023-02-10
2,Simon,5,Long Jump,2,2023-02-20
我想在 UI 表格中显示这些结果，但要去除重复的结果。我希望在 a_athletes.id 已经出现在结果中后忽略任何结果。结果需要分页显示，因此这需要在 SQL 查询内部完成。
在这个示例中，预期输出将是：
3,Barbera,NULL,NULL,NULL,NULL
2,Simon,3,Sprint,2,2023-02-10
您可以通过以下方式实现这一目标：
英文:
I have the following tables and data:
CREATE TABLE `a_athletes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `a_events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`athlete_id` bigint(20) DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `a_events` (`id`, `name`, `athlete_id`, `date`) VALUES
(1, 'Long jump', 1, '2023-02-18'),
(2, 'Row', 1, '2023-02-09'),
(3, 'Sprint', 2, '2023-02-10'),
(4, 'Sprint', 1, '2023-02-14'),
(5, 'Long Jump', 2, '2023-02-20');
INSERT INTO `a_athletes` (`id`, `name`) VALUES
(1, 'Sarah'),
(2, 'Simon'),
(3, 'Barbera');
I want to get all the results where the athlete has no 'Row' events associated.
I have the following query which achieves this:
select *
from a_athletes
left join a_events on a_athletes.id = a_events.athlete_id
where a_athletes.id not in (
select a_athletes.id
from a_athletes
inner join a_events on a_athletes.id = a_events.athlete_id
where a_events.`name` = 'Row'
)
order by a_events.`date`;
Which gives these results:
3,Barbera,NULL,NULL,NULL,NULL
2,Simon,3,Sprint,2,2023-02-10
2,Simon,5,Long Jump,2,2023-02-20
I want to display these results in a UI table of athletes without duplicates. I want to ignore any results after the a_athletes.id has already appeared in the results. The results need to be paginated, so this would have to be done within the sql query.
In this example the expected output would be:
3,Barbera,NULL,NULL,NULL,NULL
2,Simon,3,Sprint,2,2023-02-10
How can I achieve this?
答案1
得分: 1
以下是已翻译的代码部分：
你可以尝试这个查询
选择 *
从 a_athletes a1
左连接 a_events e1
在 a1.id = e1.athlete_id
其中 不存在 (选择 1
从 a_events e2
其中 a1.id = e2.athlete_id
和 e2.`name` = 'Row')
和 不存在 (选择 1
从 a_events e3
其中 a1.id = e3.athlete_id
和 e1.id > e3.id)
按 e1.`date` 排序;
查看演示 [这里][1]
英文:
You could try this query
SELECT *
FROM a_athletes a1
LEFT JOIN a_events e1
ON a1.id = e1.athlete_id
WHERE NOT EXISTS (SELECT 1
FROM a_events e2
WHERE a1.id = e2.athlete_id
AND e2.`name` = 'Row')
AND NOT EXISTS (SELECT 1
FROM a_events e3
WHERE a1.id = e3.athlete_id
AND e1.id > e3.id)
ORDER BY e1.`date`;
See demo here
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论