mysql - 初级SQL语句

Question

我正在构建一份佣金报告，其中需要包含自定义运费(如果订单中存在)。下面我连接了两个表。如果 OrderDetails.Custom 包含 'Freight' 或 'Overnight' 我需要它显示 'OrderDetails.CustomPrice' 。

如果OrderDetails.Custom不包含'Freight'或'Overnight'我需要它离开OrderDetails.CustomPrice 空白，但只要满足 WHERE 子句，仍然显示所有其他字段。

非常感谢任何帮助!

<小时/>

SELECT Orders.OrderDate, Orders.OrderID,      Orders.PaymentAmount, 
       Orders.Shipping,  OrderDetails.Custom, OrderDetails.CustomPrice  
FROM Orders
  JOIN OrderDetails 
    ON OrderDetails.OrderID = Orders.OrderID
WHERE OrderDate BETWEEN '04/01/12 00:00:01' 
                    AND '04/30/12 11:59:59' 
  AND SalesRep_CustomerID = '21140';

Answer 1

您可以通过 case 语句实现您想要的:

SELECT Orders.OrderDate, Orders.OrderID, Orders.PaymentAmount, Orders.Shipping, 
CASE WHEN (OrderDetails.Custom = 'Freight' OR OrderDetails.Custom = 'Overnight') THEN OrderDetails.CustomPrice ELSE '' END AS  CustomPrice,
 OrderDetails.CustomPrice  
FROM Orders
JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID
WHERE OrderDate BETWEEN '04/01/12 00:00:01' AND '04/30/12 11:59:59' and SalesRep_CustomerID = '21140';