我正在尝试从 2 个不同的表中选择 PHP 中 2 个日期之间的平均时间。
我有这个：
$sql="select *, avg(end_date - start_date) as avg_days from tickets where deleted = '' and DAY(datetime) = '04' and MONTH(datetime) = '".date("m")."' and YEAR(datetime) = '".date("Y")."' group by assigned_to";
但我希望能够计算出tickets表中datetime列和ticket_updates表中startdate列的平均"响应"时间。
ticket_updates表中的ticket_seq与tickets表中的ticketnumber列链接，因此我需要选择从ticket_updates表中创建的第一行，该行与tickets表中的选定行链接
我试过：
$sql="select tickets.*, avg(tickets.datetime - ticket_updates.timestart) as avg_days from tickets, ticket_updates where tickets.ticketnumber = ticket_updates.ticket_seq and deleted = '' and DAY(datetime) = '04' and MONTH(datetime) = '".date("m")."' and YEAR(datetime) = '".date("Y")."' group by assigned_to";
但是没有返回任何内容，当我在MySQL中运行时，我得到：
#1052 - Column 'datetime' in where clause is ambiguous
我已经设法通过使用此查询解决了这个问题：
$sql="
SELECT
tickets.*,
avg(tickets.datetime - ticket_updates.timestart) as avg_days
FROM
tickets, ticket_updates
WHERE
tickets.ticketnumber = ticket_updates.ticket_seq
AND deleted = ''
AND DAY(tickets.datetime) = '04'
AND MONTH(tickets.datetime) = '".date("m")."'
AND YEAR(tickets.datetime) = '".date("Y")."'
GROUP BY
tickets.assigned_to
";