php - mysql PHP查询包含连接计数

Question

我有这个查询，在检查了各种教程后应该可以工作 - 但事实并非如此。

$query="SELECT week, year, COUNT(week) AS week_no
FROM archive_agent_booking
LEFT JOIN invoice_additions ON invoice_additions.week = archive_agent_booking.week
WHERE client_id='$account_no' GROUP BY week, year ORDER BY week DESC";

表格如下:

archive_agent_booking

+---------+----------+----------+----------+----------+---------+---------+
| job_id  |   week   |   year   |   desc   |   price  |   date  | acc_no  |
+---------+----------+----------+----------+----------+---------+---------+


invoice_additions

+---------+----------+----------+----------+----------+---------+
| acc_no  |  week    |   year   |  desc    | am_price | am_date |
+---------+----------+----------+----------+----------+---------+

我基本上想计算两个表中的每个周元素并将它们显示为一个总数，即使其中一个表中没有显示周值之一。不知道这是否是最好的解决方案，因此我愿意接受替代方案。

Answer 1

select 
    week, 
    sum(items) 
from 
    (
        (select week, count(*) as items from archive_agent_booking group by week)
    union 
        (select week, count(*) from invoice_additions group by week)
    ) 
group by 
    week

编辑:我对你想看到的内容做了一些巨大的假设