- mongodb - 在 MongoDB mapreduce 中,如何展平值对象?
- javascript - 对象传播与 Object.assign
- html - 输入类型 ="submit"Vs 按钮标签它们可以互换吗?
- sql - 使用 MongoDB 而不是 MS SQL Server 的优缺点
我已经创建了一个返回我想要的结果的查询,但我觉得必须有更好的方法来执行此操作。任何指导将不胜感激。
我正在尝试获取特定 session 的所有项目并加入他们的最大 session 日期 < X 并加入最大日期的委员会首字母缩写词。 X 是当前 session 日期。
我已经尝试了几个不同的查询,但除了下面的查询之外,没有一个始终返回预期的结果。
您可以转到 rextester 查看此查询的运行情况。 .
DROP TABLE IF EXISTS `committees`;
CREATE TABLE committees
(`id` int, `acronym` varchar(4))
;
INSERT INTO committees
(`id`, `acronym`)
VALUES
(1, 'Com1'),
(2, 'Com2'),
(3, 'Com3')
;
DROP TABLE IF EXISTS `meetings`;
CREATE TABLE meetings
(`id` int, `date` datetime, `committee_id` int)
;
INSERT INTO meetings
(`id`, `date`, `committee_id`)
VALUES
(1, '2017-01-01 00:00:00', 1),
(2, '2017-02-02 00:00:00', 2),
(3, '2017-03-03 00:00:00', 2)
;
DROP TABLE IF EXISTS `agenda_items`;
CREATE TABLE agenda_items
(`id` int, `name` varchar(6))
;
INSERT INTO agenda_items
(`id`, `name`)
VALUES
(1, 'Item 1'),
(2, 'Item 2'),
(3, 'Item 3')
;
DROP TABLE IF EXISTS `join_agenda_items_meetings`;
CREATE TABLE join_agenda_items_meetings
(`id` int, `agenda_item_id` int, `meeting_id` int)
;
INSERT INTO join_agenda_items_meetings
(`id`, `agenda_item_id`, `meeting_id`)
VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1),
(4, 3, 2),
(5, 2, 1),
(6, 1, 3)
;
SELECT agenda_items.id,
meetings.id,
meetings.date,
sub_one.max_date,
sub_two.acronym
FROM agenda_items
LEFT JOIN (SELECT ai.id AS ai_id,
me.id AS me_id,
Max(me.date) AS max_date
FROM agenda_items AS ai
JOIN join_agenda_items_meetings AS jaim
ON jaim.agenda_item_id = ai.id
JOIN meetings AS me
ON me.id = jaim.meeting_id
WHERE me.date < '2017-02-02'
GROUP BY ai_id) sub_one
ON sub_one.ai_id = agenda_items.id
LEFT JOIN (SELECT agenda_items.id AS age_id,
meetings.date AS meet_date,
committees.acronym AS acronym
FROM agenda_items
JOIN join_agenda_items_meetings
ON join_agenda_items_meetings.agenda_item_id = agenda_items.id
JOIN meetings
ON meetings.id = join_agenda_items_meetings.meeting_id
JOIN committees
ON committees.id = meetings.committee_id
WHERE meetings.date) sub_two
ON sub_two.age_id = agenda_items.id
AND sub_one.max_date = sub_two.meet_date
JOIN join_agenda_items_meetings
ON agenda_items.id = join_agenda_items_meetings.agenda_item_id
JOIN meetings
ON meetings.id = join_agenda_items_meetings.meeting_id
WHERE meetings.id = 2;
审查/测试答案(修订):*
我已经根据评论修改了测试。
由于我对这个问题悬赏,我觉得我应该展示我如何评估答案并提供一些反馈。总的来说,我非常感谢所有提供帮助的人,谢谢。
为了测试,我审查了针对以下方面的查询:
我使用 EXPLAIN 的原始查询
+----+-------------+---------------------------+------+----------------------------------------------+
| id | select_type | table | rows | Extra |
+----+-------------+---------------------------+------+----------------------------------------------+
| 1 | PRIMARY | meetings | 1 | |
| 1 | PRIMARY | join_agenda_item_meetings | 1976 | Using where; Using index |
| 1 | PRIMARY | agenda_items | 1 | Using index |
| 1 | PRIMARY | <derived2> | 1087 | |
| 1 | PRIMARY | <derived3> | 2202 | |
| 3 | DERIVED | join_agenda_item_meetings | 1976 | Using index |
| 3 | DERIVED | meetings | 1 | Using where |
| 3 | DERIVED | committees | 1 | |
| 3 | DERIVED | agenda_items | 1 | Using index |
| 2 | DERIVED | jaim | 1976 | Using index; Using temporary; Using filesort |
| 2 | DERIVED | me | 1 | Using where |
| 2 | DERIVED | ai | 1 | Using index |
+----+-------------+---------------------------+------+----------------------------------------------+
12 rows in set (0.02 sec)
Paul Spiegel 的回答。
最初的答案有效并且似乎是最有效的选项,比我的要多得多。
Paul Spiegel 的第一个查询提取的行最少,比我的更短且可读性更好。它也不需要引用日期,这在编写时也会更好。
+----+--------------------+-------+------+--------------------------+
| id | select_type | table | rows | Extra |
+----+--------------------+-------+------+--------------------------+
| 1 | PRIMARY | m1 | 1 | |
| 1 | PRIMARY | am1 | 1976 | Using where; Using index |
| 1 | PRIMARY | am2 | 1 | Using index |
| 1 | PRIMARY | m2 | 1 | |
| 2 | DEPENDENT SUBQUERY | am3 | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | m3 | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | c3 | 1 | Using where |
+----+--------------------+-------+------+--------------------------+
7 rows in set (0.00 sec)
在将 DISTINCT
添加到 select 语句时,此查询也会返回正确的结果。虽然这个查询的性能不如第一个(但很接近)。
+----+-------------+------------++------+-------------------------+
| id | select_type | table | rows | Extra |
+----+-------------+------------++------+-------------------------+
| 1 | PRIMARY | <derived2> | 5 | Using temporary |
| 1 | PRIMARY | am | 1 | Using index |
| 1 | PRIMARY | m | 1 | |
| 1 | PRIMARY | c | 1 | Using where |
| 2 | DERIVED | m1 | 1 | |
| 2 | DERIVED | am1 | 1787 | Using where; Using index |
| 2 | DERIVED | am2 | 1 | Using index |
| 2 | DERIVED | m2 | 1 | |
+----+-------------+------------+------+--------------------------+
8 rows in set (0.00 sec)
Stefano Zanini 的回答
此查询使用 DISTINCT
返回预期结果。当使用 EXPLAIN
和被拉取的行数时,与我原来的查询相比,这个查询更有效,但 Paul Spiegel 的稍微好一点。
+----+-------------+------------+------+---------------------------------+
| id | select_type | table | rows | Extra |
+----+-------------+------------+------+---------------------------------+
| 1 | PRIMARY | me | 1 | Using temporary; Using filesort |
| 1 | PRIMARY | rel | 1787 | Using where; Using index |
| 1 | PRIMARY | <derived2> | 1087 | |
| 1 | PRIMARY | rel2 | 1 | Using index |
| 1 | PRIMARY | me2 | 1 | Using where |
| 1 | PRIMARY | co | 1 | |
| 2 | DERIVED | t1 | 1787 | Using index |
| 2 | DERIVED | t2 | 1 | Using where |
+----+-------------+------------+------+---------------------------------+
8 rows in set (0.00 sec)
EoinS 的回答
如评论中所述,如果 session 是连续的,则此答案有效,但不幸的是,它们可能并非如此。
最佳答案
这个有点疯狂..让我们一步一步来:
第一步是基本连接
set @meeting_id = 2;
select am1.meeting_id,
am1.agenda_item_id,
m1.date as meeting_date
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
where m1.id = @meeting_id;
我们选择 session (id = 2) 和相应的 agenda_item_ids。这将返回我们需要的前三列行。
下一步是获取每个议程项目的最后 session 日期。我们需要将第一个查询与连接表和相应的 session 连接起来(id = 2 - am2.meeting_id <> am1.meeting_id
除外)。我们只希望 session 的日期早于实际 session ( m2.date < m1.date
)。在所有这些 session 中,我们只需要每个议程项目的最新日期。所以我们按议程项目分组并选择 max(m2.date)
:
select am1.meeting_id,
am1.agenda_item_id,
m1.date as meeting_date,
max(m2.date) as max_date
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
left join join_agenda_items_meetings am2
on am2.agenda_item_id = am1.agenda_item_id
and am2.meeting_id <> am1.meeting_id
left join meetings m2
on m2.id = am2.meeting_id
and m2.date < m1.date
where m1.id = @meeting_id
group by m1.id, am1.agenda_item_id;
这样我们得到第四列(max_date
)。
最后一步是选择 acronym
session 的最后日期 ( max_date
)。这是最疯狂的部分——我们可以在 SELECT 子句中使用相关子查询。我们可以使用 max(m2.date)
对于相关性:
select c3.acronym
from meetings m3
join join_agenda_items_meetings am3 on am3.meeting_id = m3.id
join committees c3 on c3.id = m3.committee_id
where am3.agenda_item_id = am2.agenda_item_id
and m3.date = max(m2.date)
最终查询将是:
select am1.meeting_id,
am1.agenda_item_id,
m1.date as meeting_date,
max(m2.date) as max_date,
( select c3.acronym
from meetings m3
join join_agenda_items_meetings am3 on am3.meeting_id = m3.id
join committees c3 on c3.id = m3.committee_id
where am3.agenda_item_id = am2.agenda_item_id
and m3.date = max(m2.date)
) as acronym
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
left join join_agenda_items_meetings am2
on am2.agenda_item_id = am1.agenda_item_id
and am2.meeting_id <> am1.meeting_id
left join meetings m2
on m2.id = am2.meeting_id
and m2.date < m1.date
where m1.id = @meeting_id
group by m1.id, am1.agenda_item_id;
说实话,我很惊讶你可以使用 max(m2.date)
在子查询中。
另一个解决方案 - 在子查询(派生表)中使用第二个查询。使用 max_date
通过 session 和加入表加入委员会.只保留带有首字母缩写词的行和没有 max_date
的行.
select t.*, c.acronym
from (
select am1.meeting_id,
am1.agenda_item_id,
m1.date as meeting_date,
max(m2.date) as max_date
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
left join join_agenda_items_meetings am2
on am2.agenda_item_id = am1.agenda_item_id
and am2.meeting_id <> am1.meeting_id
left join meetings m2
on m2.id = am2.meeting_id
and m2.date < m1.date
where m1.id = @meeting_id
group by m1.id, am1.agenda_item_id
) t
left join join_agenda_items_meetings am
on am.agenda_item_id = t.agenda_item_id
and t.max_date is not null
left join meetings m
on m.id = am.meeting_id
and m.date = t.max_date
left join committees c on c.id = m.committee_id
where t.max_date is null or c.acronym is not null;
关于mysql - 查询是否可以优化 : Get a records max date then join the max date's values,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42564643/
我是一名优秀的程序员,十分优秀!