英文:
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 osnjoin osn_check on osn_check.osn_id=osn.idgroup 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.idgroup by osn.idhaving 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_typefrom osn oinner join osn_check oc on oc.osn_id = o.idwhere oc.check_date = (select max(oc1.check_date)from osn_check oc1where oc1.osn_id = o.id)
更现代的方法使用 row_number()（适用于 MySQL >= 8.0）：
select o.*, oc.check_date, oc.check_typefrom osn oinner join (select oc.*,row_number() over(partition by osn_id order by check_date desc) rnfrom osn_check oc) oc on oc.osn_id = o.idwhere oc.rn = 1
如果在检查表中有许多行而在参考表中有很少行，也许使用侧向连接会更有效（需要 MySQL >= 8.0.14）：
select o.*, oc.check_date, oc.check_typefrom osn ocross join lateral (select oc.*from osn_check ocwhere oc.osn_id = o.idorder 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_typefrom osn oinner join osn_check oc on oc.osn_id = o.idwhere oc.check_date = (select max(oc1.check_date)from osn_check oc1where oc1.osn_id = o.id)
A more modern approach uses row_number() (MySQL >= 8.0):
select o.*, oc.check_date, oc.check_typefrom osn oinner join (select oc.*,row_number() over(partition by osn_id order by check_date desc) rnfrom osn_check oc) oc on oc.osn_id = o.idwhere 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_typefrom osn ocross join lateral (select oc.*from osn_check ocwhere oc.osn_id = o.idorder 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论