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

Question

我当前的表体系结构如下：

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

所以如果你想想象它就像一棵树卡片=>许多卡片列表=>许多列出了任务。

卡片

ID |名称

列表

ID|Card_ID|名称

任务

ID|Lists_ID|Card_ID|标题

用户任务

Id |任务Id |用户Id |内容

现在我需要写一个查询或一个简单的逻辑，无论它是什么，我都无法计算出，以计算每张卡的进度。

卡片进度是通过列出用户完全填写的方式来计算的。

示例：

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

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

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

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

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

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

因此，要考虑一个已完成的列表，用户必须填写其所有任务。有人能指导我如何使用嵌套表体系结构的逻辑来让卡片继续前进吗？

因此，我希望以任何方式输出如下：

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

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

Answer 1

您可以使用以下查询

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#4：获取user_task中包含所有任务项的列表计数
• 最后，加入target和progress子查询以获取最终结果

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

---

注意：的观测耦合

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

Answer 2

这很简单。要查找所有包含任务的列表，并检查是否所有任务都已填写，请执行以下操作：

select lists.id,
count(tasks.id)=count(user_tasks.id) filled  
from lists
join tasks on tasks.lists_id=lists.id
left join user_tasks on user_tasks.task_id=tasks.id
group by lists.id

要报告每张卡的总列表和已填写列表的数量，您可以将其用作子查询(添加card_id以能够按卡查找列表(：

select card.id,
count(card_lists.id) as total_lists,
sum(card_lists.filled) as total_filled
from card
join (
select lists.id, lists.card_id,
count(tasks.id)=count(user_tasks.id) filled  
from lists
join tasks on tasks.lists_id=lists.id
left join user_tasks on user_tasks.task_id=tasks.id
group by lists.id, lists.card_id
) card_lists on card_lists.card_id=card.id
group by card.id

如果一个任务可以由多个用户完成，请将count(user_tasks.id)更改为count(distinct user_tasks.tasks_id)。