为未找到的列显示空或 0 的剩余行 [英] Display rest of row with null or 0 in place for columns not found

问题描述

我认为与此类似的问题:​​如果未找到行，则为列显示空值

Question similar to this I think: Display null for column if no row found

我尝试了多个连接，但都无法获得所需的结果.

I have tried several joins, but can't get the results I require.

SQL 语句:

SELECT months.month, SUM(job_details.price_each*job_details.quantity) AS sum_monthly_price, DATE_FORMAT(job.order_date, '%M') AS order_date, customer.company_name 
FROM months 
JOIN job on months.month = month(job.order_date) 
JOIN job_details on job.job_id = job_details.job_id 
JOIN customer on job.company_id = customer.company_id 
WHERE months.month > month(date_sub(NOW(), INTERVAL 4 month)) 
AND months.month <= month(NOW()) 
AND customer.company_id = 6 
GROUP BY months.month 
ORDER BY months.month asc

当前语句返回:

month    |  sum_monthly_price  |  order_date  |   company_name
  7      |        100          |     July     |      test
  9      |        300          |  September   |      test

我试图返回的是:

month    |  sum_monthly_price  |  order_date  |   company_name
  7      |        100          |     July     |      test
  8      |         0           |     NULL     |      test
  9      |        300          |  September   |      test
  10     |         0           |     NULL     |      test

months 表只有 1 到 12 个整数，用于查看前 4 个月是否有任何工作.但是，如果特定月份没有工作，我要求它在 sum_monthly_price 中显示 0.

The months table just has 1 to 12 integers to see if there are any jobs for the previous 4 months. But I require it to display 0 in sum_monthly_price if there were no jobs for a particular month.

推荐答案

如果我理解正确，您需要使用 OUTER JOIN 来获取缺失月份的结果，但您还需要一个 CROSS JOIN 以正确获取公司名称——如果没有，它将显示为 NULL:

If I'm understanding correctly, you need to use an OUTER JOIN to get results for the missing months, but you also need a CROSS JOIN to get the Company name correctly -- if not, it will show up as NULL:

SELECT months.month,
     SUM(job_details.price_each*job_details.quantity) AS sum_monthly_price, 
     DATE_FORMAT(job.order_date, '%M') AS order_date, 
     customer.company_name 
FROM months CROSS JOIN customer 
    LEFT JOIN job on job.company_id = customer.company_id 
         AND months.month = month(job.order_date) 
    LEFT job_details on job.job_id = job_details.job_id 
WHERE months.month > month(date_sub(NOW(), INTERVAL 4 month)) 
    AND months.month <= month(NOW()) 
    AND customer.company_id = 6 
GROUP BY months.month 
ORDER BY months.month asc

这篇关于为未找到的列显示空或 0 的剩余行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！