英文:
Empty result of a query with 'group by' and 'having' clause
问题
select decimal_num, eri, max(check_date),
concat(date_format(max(check_date), '%d.%m.%Y'), ' - ',
date_format(date_add(max(check_date), interval 1 year), '%d.%m.%Y'))
from osn
join osn_check on osn_check.osn_id=osn.id
group by osn.id;
英文:
I have two tables: osn
+----+---------------------------------+--------+
| id | decimal_num | eri | devices|
+----+---------------------------------+--------+
| 1 | AD2S80AUD | AD2S80AUD | 419 |
| 2 | AD2S99 | AD2S99 | 419 |
| 3 | F2K_14pin | 14pin | F2K |
+----+---------------------------------+--------+
and osn_check:
+----+--------+------------+------------+-------+------------+------+---------+
| id | osn_id | check_date | check_type | works | conclusion | fio | comment |
+----+--------+------------+------------+-------+------------+------+---------+
| 2 | 1 | 2022-04-29 | 1 | | NULL | NULL | NULL |
| 4 | 1 | 2023-05-24 | 0 | NULL | NULL | NULL | NULL |
+----+--------+------------+------------+-------+------------+------+---------+
I need to select the fields from osn and osn_check where osn_check.check_date is maximal for each group. I execute this query:
select decimal_num, eri, check_date,
concat(date_format(check_date, '%d.%m.%Y'), ' - ',
date_format(date_add(check_date, interval 1 year), '%d.%m.%Y'))
from osn join osn_check on osn_check.osn_id=osn.id
group by osn.id
having check_date=max(check_date);
The result is empty. My desired result would be like this:
AD2S80AUD | AD2S80AUD | 2023-05-24 | 24.05.2023 - 24.05.2024
How can I do this?
答案1
得分: 0
基本上，您想要进行过滤而不是聚合。根据您最初的尝试精神，我们可以使用相关子查询来返回每个项目的最新日期，因此：
select o.*, oc.check_date, oc.check_type
from osn o
inner join osn_check oc on oc.osn_id = o.id
where oc.check_date = (
select max(oc1.check_date)
from osn_check oc1
where oc1.osn_id = o.id
)
更现代的方法使用 row_number()（适用于 MySQL >= 8.0）：
select o.*, oc.check_date, oc.check_type
from osn o
inner join (
select oc.*,
row_number() over(partition by osn_id order by check_date desc) rn
from osn_check oc
) oc on oc.osn_id = o.id
where oc.rn = 1
如果在检查表中有许多行而在参考表中有很少行，也许使用侧向连接会更有效（需要 MySQL >= 8.0.14）：
select o.*, oc.check_date, oc.check_type
from osn o
cross join lateral (
select oc.*
from osn_check oc
where oc.osn_id = o.id
order by check_date desc limit 1
) oc
为了提高性能，请考虑在 osn_check(osn_id, check_date desc) 上创建一个索引，以便侧向子查询可以运行得更快。
英文:
Basically you want filtering rather than aggregation. In the spirit of your initial attempt, we could use a correlated subquery to return the latest date of each item, so:
select o.*, oc.check_date, oc.check_type
from osn o
inner join osn_check oc on oc.osn_id = o.id
where oc.check_date = (
select max(oc1.check_date)
from osn_check oc1
where oc1.osn_id = o.id
)
A more modern approach uses row_number() (MySQL >= 8.0):
select o.*, oc.check_date, oc.check_type
from osn o
inner join (
select oc.*,
row_number() over(partition by osn_id order by check_date desc) rn
from osn_check oc
) oc on oc.osn_id = o.id
where oc.rn = 1
If there are many rows in the check table and few rows in the reference table, maybe a lateral join would be more efficient (requires MySQL >= 8.0.14):
select o.*, oc.check_date, oc.check_type
from osn o
cross join lateral (
select oc.*
from osn_check oc
where oc.osn_id = o.id
order by check_date desc limit 1
) oc
For performance, consider an index on osn_check(osn_id, check_date desc), so the lateral subquery can run fast.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论