为一个订单号获取多个送货地址

Question

我正在使用 4 个表

• 客户
• CUSTOMER_ORDER
• CUST_ORDER_LINE
• CUST_ADDRESS

我使用内部联接来链接表。 CUSTOMER链接到CUSTOMER_ORDER并按customer_ID CUST_ADDRESS，CUSTOMER_ORDER_LINE链接到order_ID CUSTOMER_ORDER，Order_ID不会出现在CUSTOMER或CUST_ADDRESS表中。

当我运行下面的查询时，我会得到该特定客户和订单号记录的每个送货地址。

例如，分销商有 25 个可能的送货地址，但他们一次只能将一个订单运送到一个送货地址。我的查询是为每个地址返回一个订单号 25 次。任何建议都会很棒。谢谢。

SELECT DISTINCT TOP (100) PERCENT dbo.CUSTOMER_ORDER.ID, 
    dbo.CUSTOMER.NAME, 
    dbo.CUST_ORDER_LINE.PART_ID, 
    dbo.CUST_ORDER_LINE.ORDER_QTY, 
    dbo.CUSTOMER_ORDER.STATUS, 
    dbo.CUSTOMER_ORDER.SHIPTO_ID, 
    dbo.CUST_ADDRESS.NAME AS Expr1
FROM dbo.CUSTOMER 
    INNER JOIN  dbo.CUSTOMER_ORDER 
        ON dbo.CUSTOMER.ID = dbo.CUSTOMER_ORDER.CUSTOMER_ID 
    INNER JOIN dbo.CUST_ORDER_LINE 
        ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID 
    INNER JOIN dbo.CUST_ADDRESS 
        ON dbo.CUSTOMER.ID = dbo.CUST_ADDRESS.CUSTOMER_ID
WHERE     (dbo.CUSTOMER_ORDER.ORDER_DATE > '1/1/2014') 
    AND (dbo.CUSTOMER_ORDER.ID NOT LIKE 'RMA%')
GROUP BY dbo.CUSTOMER_ORDER.ID, 
    dbo.CUSTOMER.NAME, 
    dbo.CUST_ORDER_LINE.PART_ID, 
    dbo.CUST_ORDER_LINE.ORDER_QTY, 
    dbo.CUSTOMER_ORDER.STATUS, 
    dbo.CUSTOMER_ORDER.SHIPTO_ID, 
    dbo.CUST_ADDRESS.NAME
ORDER BY dbo.CUSTOMER_ORDER.ID

Answer 1

作为黑暗中的镜头，您的查询似乎应该是这样的。

SELECT 
    co.ID, 
    c.NAME, 
    col.PART_ID, 
    col.ORDER_QTY, 
    co.STATUS, 
    co.SHIPTO_ID, 
    ca.NAME AS Expr1
FROM dbo.CUSTOMER c
    INNER JOIN  dbo.CUSTOMER_ORDER co ON c.ID = co.CUSTOMER_ID 
    INNER JOIN dbo.CUST_ORDER_LINE col ON co.ID = col.CUST_ORDER_ID 
    INNER JOIN dbo.CUST_ADDRESS ca ON co.SHIPTO_ID = ca.CUSTOMER_ID --this is now joining to the order table.
WHERE co.ORDER_DATE > '2014-01-01'
    AND co.ID NOT LIKE 'RMA%'
GROUP BY co.ID, 
    c.NAME, 
    col.PART_ID, 
    col.ORDER_QTY, 
    co.STATUS, 
    co.SHIPTO_ID, 
    ca.NAME
ORDER BY co.ID

请注意，使用别名如何使它看起来更干净。我还更改了字符串日期以使用普遍接受的格式。无论您的日期格式设置如何，这都将起作用。