PHP select timediff with select query and group by user

Question

我有这个 SQL 查询:

$sql="SELECT *, COUNT(assigned_to) AS my_groupcount from tickets where deleted = '' and DAY(datetime) = '".$_GET["d"]."' and MONTH(datetime) = '".$_GET["m"]."' and YEAR(datetime) = '".$_GET["Y"]."' group by assigned_to order by datetime ASC ";
    $rs=mysql_query($sql,$conn);
    while($result=mysql_fetch_array($rs))
    {
        //work out the total time taken
        $sql3="SELECT *,  TIMEDIFF( timeend, timestart ) AS support_time_used FROM ticket_updates WHERE ticket_seq = '".$result["ticketnumber"]."' ";
        $rs3=mysql_query($sql3,$conn) or die (mysql_error());
        $totaltime = 0;
        while($result3=mysql_fetch_array($rs3))
        {
            $totaltime = $totaltime+substr($result3["support_time_used"],0,2)*60+substr($result3["support_time_used"],3,2);
        }   
        $hours=intval($totaltime / 60);
        $minutes=$totaltime -($hours * 60);
        $total_ticket_time = $hours.'h '.$minutes.'m';

        echo $result["assigned_to"].' ('.$result["my_groupcount"].') - Time: '.$total_ticket_time.'<br>';
    }

因此它从 tickets 表中选择所有行并按 asigned_to 列分组。

然后它从 ticket_updates 表计算出每个用户使用的时间。

ticket_updates.ticket_seq 列链接到 tickets.ticketnumber 列，tickets 表中的 1 行的 ticket_updates 表中可能只有一行或多行，因此它将所有差异添加到 ticket_updates 表中。

我试图从工单表中列出 assigned_to(每个一个)并在每个旁边列出他们使用了多少时间，但它只是从一张工单中选择它。

如何让它从所有票中进行选择？

Answer 1

你不需要分两步完成，对吧。

select assigned_to, sum(TIMEDIFF( timeend, timestart )) as total_time, count(distinct ticket_id) as ticket_count from ticket_update where ticket_id in (select ticket_id from ticket where condition)
group by assigned_to

编辑:你实际上需要:

        select ticket.assigned_to, 
sum(TIMEDIFF( ticket_update_timeend, ticket_update.timestart )) as total_time, count(distinct ticket.ticket_id) as ticket_count 
from 
    ticket_update 
    inner join ticket
    on ticket.ticket_id=ticket_update.ticket_id
where ticket.deleted = '' and DAY(ticket.datetime) = '".$_GET["d"]."' and MONTH(ticket.datetime) = '".$_GET["m"]."' and YEAR(ticket.datetime) = '".$_GET["Y"]."'
    group by ticket.assigned_to