mysql 从具有最新修订版本的SQL数据库信息中选择

bttbmeg0 于 2022-12-17 发布在 Mysql

关注(0)|答案(1)|浏览(106)

我为我的客户端编码网络应用程序，并与从数据库中选择最新修订报告的问题。

SELECT 
        raports.*,
        r1.*,
        users.*, 
        (SELECT COUNT(*) FROM changes WHERE changes.changes_raports_id = raports.raports_id) as changes,
        (SELECT changes.changes_date FROM changes WHERE changes.changes_raports_id = raports.raports_id ORDER BY changes.changes_date DESC LIMIT 1) as last_change,
        (SUM(injuries.injuries_min_procent) / COUNT(injuries_to_raports.injuries_to_raports_id)) as min,
        (SUM(injuries.injuries_max_procent) / COUNT(injuries_to_raports.injuries_to_raports_id)) as max
    FROM raports
    LEFT JOIN users 
        ON users.users_id = raports.raports_users_id 
    LEFT JOIN changes 
        ON changes.changes_raports_id = raports.raports_id 
    LEFT JOIN raports_to_changes r1
        ON r1.raports_to_changes_raports_id = raports.raports_id
    LEFT JOIN injuries_to_raports 
        ON injuries_to_raports.injuries_to_raports_raports_id = r1.raports_to_changes_raports_id
    LEFT JOIN injuries
        ON injuries_to_raports.injuries_to_raports_injuries_id = injuries.injuries_id
    WHERE r1.raports_to_changes_changes_id = (SELECT max(raports_to_changes_changes_id) FROM raports_to_changes r2 WHERE r2.raports_to_changes_raports_id = r1.raports_to_changes_raports_id)
    GROUP BY raports.raports_id ORDER BY raports.raports_id ASC;

在最大值和最小值列中，我没有正确的平均受伤数。当我检查它并计算所有受伤数时，我有36次受伤，而实际数字是2，但我有18次修订。因此，逻辑是我循环了所有修订数的计数，但我只想要最新的
我试着修改WHERE语句和更多的LEFT JOIN，但没有任何帮助。
有人能修复那个代码吗？
先谢谢你

mysql

来源：https://stackoverflow.com/questions/74828217/select-from-sql-database-information-with-newest-revisions

1条答案

按热度按时间

8zzbczxx1#

根据查询所揭示的线索，数据模型可能如下所示：

select列表显示您需要：

reports_id的users信息
1.在raports_id级别聚合injuries_min_procent和injuries_max_procent。（请参见cte_raport_injuries）
1.一个raports_id的changes的数量（参见cte_raport_changes）
raports_id的最后一个change_date（请参见cte_raport_changes）
根据问题中透露的信息，我不确定是否需要raports_of_changes，所以现在我将忽略它。

with cte_raport_injuries as (
select r.raports_id,
       sum(i.injuries_min_procent) / count(*) as injuries_min_procent,
       sum(i.injuries_max_procent) / count(*) as injuries_max_procent
  from raports r
  join injuries_to_raports ir
    on r.raports_id = ir.injuries_to_raports_raports_id
  join injuries i
    on ir.injuries_to_raports_injuries_id = i.injuries_id
 group by r.raports_id),
 cte_raport_changes as (
 select r.raports_id,
       count(c.changes_id) as changes, 
       max(c.changes_date) as last_change
  from raports r
  join changes c
   on r.raports_id = c.changes_raports_id
 group by r.raports_id)
select u.users_id,
       r.raports_id,
       ri.injuries_min_procent,
       ri.injuries_max_procent,
       rc.changes,
       rc.last_change
  from raports r
  join users u
    on r.raports_users_id = u.users_id
  join cte_raport_injuries ri
    on r.raports_id = ri.raports_id
  join cte_raport_changes rc
    on r.raports_id = rc.raports_id;

结果如下所示：

users_id|raports_id|injuries_min_procent|injuries_max_procent|changes|last_change|
--------+----------+--------------------+--------------------+-------+-----------+
       1|        11|             15.0000|             25.0000|      2| 2022-12-02|

所以我想问你的是，你需要reports_to_changes中的什么，它与其他人之间的关系是什么？为了进一步参与社区，你可能想以文本格式分享以下信息：

每个表的DDL（主键、外键、列名和数据类型）
一些可表示的示例数据和基本业务规则
预期产出

赞(0）回复(0）举报 2022-12-17

我来回答

mysql 从具有最新修订版本的SQL数据库信息中选择

1条答案

相关问题

热门标签

最新问答