mySQL查询问题

Question

我有来自客户购物车的 ORDERS 表。这是典型的客户名称、地址等字段列表。不同之处在于，当客户输入与账单信息不同的运输信息时，购物车会将运输信息填充到适当的字段(shipName、shipAddress、ShipState 等)中。但是，当客户运送给自己时，购物车不会将运送信息移动到适当的运送字段(即 shipName、shipAddress、shipState)。它将此信息保存在 ordName、ordAddress、ordCity 等字段中，然后将运输相关字段保留为空。

因此，对于客户自己运送的订单，这些运送字段为空。当没有提供运输信息时，我使用 IF 命令将 ordName、ordAddr 等移动到别名，以便处理所有订单类型的运输信息(无论是客户运送到自己​​还是另一个地址)。该部分在我下面的查询中运行良好。

还有一个问题。我的运输程序不能使用长州名称(例如，密歇根州、纽约州等)。它需要状态是两个字符的缩写(例如，MI、NY)。我有一个名为 *STATES 的查找表，它在长州名和双字符缩写之间有一个映射。我正在尝试使用 ShipState 别名来查找给定状态的正确的两个字符名称。我试图以 JOIN 的形式执行此操作，但不断出现错误。我删除了我正在使用的连接，并且只显示了现在可以正常工作但没有为状态缩写进行映射的代码。有人可以帮忙吗？

SELECT   
  orders.ordDate AS `Date`,  
  orders.ordID AS Order_ID,  
  orders.ordEmail AS Email,  
  IF(ordShipName = '          ', ordName, ordShipName) AS Name,  
  IF(ordShipAddress = '          ', ordAddress, ordShipAddress) AS Address_1,  
  IF(ordShipAddress2 = '          ', ordAddress2, ordShipAddress2) AS Address_2,  
  IF(ordShipCity = '          ', ordCity, ordShipCity) AS City,  
  IF(ordShipState = '          ', ordState, ordShipState) AS ShipState,  
  IF(ordShipZip = '          ', ordZip, ordShipZip) AS Postal,  
  IF(ordShipCountry = '          ', ordCountry, ordShipCountry) AS Country,  
  IF(ordShipPhone = '          ', ordPhone, ordShipPhone) AS Phone,  

FROM  
  orders   

WHERE  
  orders.ordID > 21700  
  HAVING  
  Country = 'United States of America' 

Answer 1

您不能使用列别名 ShipState 来连接您的查找表。相反，再次重复您的 IF 构造:

SELECT   
  orders.ordDate AS `Date`,  
  orders.ordID AS Order_ID,  
  orders.ordEmail AS Email,  
  IF(ordShipName = '          ', ordName, ordShipName) AS Name,  
  IF(ordShipAddress = '          ', ordAddress, ordShipAddress) AS Address_1,  
  IF(ordShipAddress2 = '          ', ordAddress2, ordShipAddress2) AS Address_2,  
  IF(ordShipCity = '          ', ordCity, ordShipCity) AS City,  
  IF(ordShipState = '          ', ordState, ordShipState) AS ShipState,  
  IF(ordShipZip = '          ', ordZip, ordShipZip) AS Postal,  
  IF(ordShipCountry = '          ', ordCountry, ordShipCountry) AS Country,  
  IF(ordShipPhone = '          ', ordPhone, ordShipPhone) AS Phone,  
  STATE.StateAbbreviation
FROM  
  orders   
    INNER JOIN STATES
    ON IF(orders.ordShipState = '          ', orders.ordState, orders.ordShipState) = STATES.State

WHERE  
  orders.ordID > 21700  
  HAVING  
  Country = 'United States of America'