时间戳

首页 > 解决方案 > 如何计算mysql中嵌套表架构的百分比

mysql - 如何计算mysql中嵌套表架构的百分比

问题描述

我当前的表架构如下：

  1. 卡片表（父表Card：）
  2. 每张卡片都有一个或多个列表（第二个孩子lists）
  3. 每个列表有许多任务（第三个孩子tasks）
  4. 每当用户填写一项任务时，它将被填写在一个名为 ( user_tasks)的单独表格中

因此，如果您想想象它就像树卡 => 很多卡列表 => 很多列表任务。

卡片

身份证 | 姓名

列表

身份证 | 卡号 | 姓名

任务

身份证 | Lists_Id | Card_Id | 标题

用户任务

身份证 | 任务ID | 用户 ID | 内容

现在我需要编写一个查询或一个简单的逻辑，我根本无法计算出每张卡片的进度。

卡片进度是根据用户完全填写的列表来计算的。

例子：

  1. 卡片 1 有 3 个列表，每个列表有 4 个任务

用户填写了这些列表中的所有任务 (4x3)，卡片进度将为 3/3。

  1. 卡片 1 有 3 个列表，每个列表有 4 个任务

用户填写了任意 2 个列表 (4x2) 中的所有任务，卡片进度将为 2/3

  1. 卡片 1 有 3 个列表，每个列表有 4 个任务

用户在列表 1 中仅填写了 2 个任务，其中包含 4 个任务，卡片进度将为 0/3

因此，要考虑完成的列表，用户必须完成所有任务。有人可以指导我如何执行嵌套表架构的逻辑以使卡片取得进展吗？

所以我希望输出以任何方式如下：

{
   card_id: 1,
   total_lists: 10
   total_filled: 3
}

继续，在数据库中的所有卡片上......

标签： mysqlsql

解决方案

您可以使用以下查询

SELECT target.cardid as 'card_id', target.total as 'total_lists', IFNULL(progress.total,0) as 'total_filled'
FROM   (SELECT l.cardid, Count(l.id) total 
        FROM   lists l 
        GROUP  BY l.cardid) target
LEFT JOIN (SELECT l.cardid, userlist.userid, Count(userlist.listid) total 
                   FROM   lists l 
                          INNER JOIN (SELECT ut.userid, t1.listid, Count(ut.taskid) tcount 
                                      FROM   user_tasks ut 
                                             INNER JOIN tasks t1 ON t1.id = ut.taskid 
                                      GROUP  BY ut.userid, t1.listid) userlist --- STEP#1
                                  ON l.id = userlist.listid 
                          INNER JOIN (SELECT t.listid, Count(*) tcount 
                                      FROM   tasks t 
                                      GROUP  BY t.listid) tasklist --- STEP#2
                                  ON userlist.listid = tasklist.listid  AND userlist.tcount = tasklist.tcount --- STEP#3
                   GROUP  BY l.cardid, userlist.userid) progress  -- STEP#4
ON target.cardid = progress.cardid;

描述：

  • Step#1：确定每个 user_id 和 list_id 的任务数
  • Step#2: 确定所有列表的每个 list_id 的任务数
  • Step#3：过滤user_task列表中没有所有任务条目的记录
  • Step#4user_task：获取包含所有任务条目的列表计数
  • 最后，加入target和progress子查询以获取最终结果。

演示： https ://www.db-fiddle.com/f/5tAzoL49SSd2SJDK5k19a3/3

注意：几个观察

  1. 理想情况下， tabletasks不应该有cardid，只有listid必须存在。
  2. 2个不同的用户可以在同一张卡上工作吗？在这种情况下，在结果中，您还需要包含userid。参考 - https://www.db-fiddle.com/f/5tAzoL49SSd2SJDK5k19a3/4

推荐阅读