php - 使用 MySQL DAYNAME 作为列标题，即使结果为 NULL

Question

我有一个查询可以完成我需要的大部分工作，但无法为空结果保留表示逻辑。

考虑这个查询:

SELECT 
SUM(order_detail.price/order_detail.qty*order_detail.qty_dispatched) AS orderedPrice, 
orders.location, 
orders.id,
suppliers.supplier_name,
DAYNAME(FROM_UNIXTIME(orders.datetime)) AS Day_Name,
from_unixtime(orders.datetime) AS Day_Date
FROM orders, order_detail, products,suppliers 
WHERE (orders.datetime BETWEEN '$from_date' AND '$to_date'  
AND order_detail.order_id = orders.id  
AND orders.location LIKE '%$locations%'
AND  order_detail.product_id=products.id 
AND products.supplier_id IN ($suppliers)
AND suppliers.id IN ($suppliers)
AND products.supplier_id=suppliers.id
AND (orders.status <> 'deleted') )

如果我得到一周中每一天 的结果，我可以使用 PHP 对其进行后处理并显示结果:

Location | Supplier Name | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Total
--------------------------------------------------------------------------
Alhambra | Widget Co     | $50 | $50 | $50 | $50 | $50 | $50 | $50 | $350

但是，如果任何一天都没有订单，我显然得不到任何结果，而且我无法找到一种方法来发布过程以在列中显示 $0。

我想也许weekdays 表可能是 JOIN 的答案，但我想不出来。是这样的吗？

CREATE TABLE week_days(
  week_day_num INT(11) DEFAULT NULL
);

INSERT INTO week_days(week_day_num) VALUES (1),(2),(3),(4),(5),(6),(7);

SELECT
DAYNAME(FROM_UNIXTIME(orders.datetime)) AS day_name,
SUM(order_detail.price/order_detail.qty*order_detail.qty_dispatched) AS orderedPrice,
COALESCE(SUM(order_detail.price/order_detail.qty*order_detail.qty_dispatched) AS orderedPrice , 0)
FROM week_days wd
LEFT JOIN (

SELECT 
orders.location, 
orders.id,
suppliers.supplier_name,
from_unixtime(orders.datetime) AS Day_Date
FROM orders, order_detail, products,suppliers 
WHERE (orders.datetime BETWEEN '$from_date' AND '$to_date'  
AND order_detail.order_id = orders.id  
AND orders.location LIKE '%$locations%'
AND  order_detail.product_id=products.id 
AND products.supplier_id IN ($suppliers)
AND suppliers.id IN ($suppliers)
AND products.supplier_id=suppliers.id
AND (orders.status <> 'deleted') )


) order_results
ON wd.week_day_num = DAYOFWEEK(FROM_UNIXTIME(orders.datetime))
GROUP BY
      products.supplier_id,orders.location,DAYOFWEEK(FROM_UNIXTIME(orders.datetime))

ORDER BY location, products.supplier_id,Day_Date ASC;

Answer 1

你的语法很糟糕:

COALESCE(SUM(order_detail.price/order_detail.qty*order_detail.qty_dispatched) AS orderedPrice , 0)

AS 子句需要在 COALESCE 函数之外。

COALESCE(SUM(order_detail.price/order_detail.qty*order_detail.qty_dispatched), 0) AS orderedPrice