对于现有查询SQL联盟

  • Z时代
  • 2024-01-10
  • 分类：问答

我有以下查询来显示在一段时间内的销售，但不幸的是，它似乎不报告已在其他类别购买的客户。对于现有查询SQL联盟

例如，顾客从未购买过产品（猫2或猫3），并且首次以5/5购买产品。报告中没有提到这次销售，因为他们以前从猫猫购买了一些东西。

select CUSTOMER_DEL_HIST.CUSTOMER_NUMBER 
     ,max (CUSTOMER_DEL_HIST.SERVE_LOCATION) as SERVE_LOCATION 
     ,max (CUSTOMER_DEL_HIST.PRODUCT_NUMBER) as PRODUCT_NUMBER 
     ,max (CUSTOMER_DEL_HIST.DEL_DATE) as DEL_DATE 
     ,max (ORDER_RTE_BAL.WAREHOUSE) as WAREHOUSE 
     ,max (ORDER_RTE_BAL.ROUTE_NUMBER) as ROUTE_NUMBER 
     ,max (ORDER_RTE_BAL.ROUTE_DAY) as ROUTE_DAY 
from CUSTOMER_DEL_HIST 
     inner join INV_MASTER on CUSTOMER_DEL_HIST.PRODUCT_NUMBER = INV_MASTER.PRODUCT_NUMBER 
     inner join ORDER_HEADER on CUSTOMER_DEL_HIST.ORDER_NUMBER = ORDER_HEADER.ORDER_NUMBER 
     inner join ORDER_RTE_BAL 
      on ORDER_HEADER.WAREHOUSE_NUMBER = ORDER_RTE_BAL.WAREHOUSE 
       and ORDER_HEADER.ROUTE_NUMBER = ORDER_RTE_BAL.ROUTE_NUMBER 
       and ORDER_HEADER.ROUTE_DAY = ORDER_RTE_BAL.ROUTE_DAY 
       and ORDER_HEADER.DELIVERY_DATE = ORDER_RTE_BAL.ROUTE_DATE 
where (CUSTOMER_DEL_HIST.DEL_DATE between X and Y) 
     and (INV_MASTER.INVENTORY_CATEGORY in ('02', '03', '60', '74')) 
     and (CUSTOMER_DEL_HIST.CUSTOMER_NUMBER not in 
      (select H2.CUSTOMER_NUMBER 
      from CUSTOMER_DEL_HIST H2 
      inner join INV_MASTER as I on H2.PRODUCT_NUMBER = I.PRODUCT_NUMBER 
      where H2.DEL_DATE between '6/01/2014' and '04/30/2015' 
       and I.INVENTORY_CATEGORY in ('02', '03', '60', '74'))) 
group by CUSTOMER_DEL_HIST.CUSTOMER_NUMBER

我是新来的SQL，但知道我应该在SELECT中使用联合，但就是这样。不幸的是我不够精通，所以我正在寻求一些帮助，以便我能够获得我想要的结果。

回答：

不能提供很多帮助解决实际问题的方法，但既然你说你是新的，我想我会告诉你只需一点点努力，你的查询就可以有多清晰。如果你开始使用别名，你可以消除每行数十个字符，并且你的代码可以更清晰。很多格式都是个人喜好的，但您发布的代码只是挑战阅读的方式。这是一种使用别名的方法，并且可以大大地清除它。

SELECT cdh.customer_number 
    , Max(cdh.serve_location) AS serve_location 
    , Max(cdh.product_number) AS product_number 
    , Max(cdh.del_date) AS del_date 
    , Max(orb.warehouse) AS warehouse 
    , Max(orb.route_number) AS route_number 
    , Max(orb.route_day) AS route_day 
FROM customer_del_hist cdh 
INNER JOIN inv_master im ON cdh.product_number = im.product_number 
INNER JOIN order_header oh ON cdh.order_number = oh.order_number 
INNER JOIN order_rte_bal orb ON oh.warehouse_number = orb.warehouse 
     AND oh.route_number = orb.route_number 
     AND oh.route_day = orb.route_day 
     AND oh.delivery_date = orb.route_date 
WHERE cdh.del_date BETWEEN @StartDate AND @EndDate 
    AND im.inventory_category IN ('02', '03', '60', '74') 
    AND cdh.customer_number NOT IN 
    (
     SELECT h2.customer_number 
     FROM customer_del_hist AS h2 
     INNER JOIN inv_master AS i ON h2.product_number = i.product_number 
     WHERE h2.del_date BETWEEN '6/01/2014' AND '04/30/2015' 
      AND i.inventory_category IN ('02', '03', '60', '74') 
    ) 
GROUP BY cdh.customer_number

回答：

在查询的WHERE子句，最后一块有

cdh.customer_number NOT IN 
    (
     SELECT h2.customer_number 
     FROM customer_del_hist AS h2 
     INNER JOIN inv_master AS i ON h2.product_number = i.product_number 
     WHERE h2.del_date BETWEEN '6/01/2014' AND '04/30/2015' 
      AND i.inventory_category IN ('02', '03', '60', '74') 
    )

这将是被排除客户的区域。您可以看到子查询有一个where子句，并且此where子句指定inventory_category必须是02,03,60,74中的一个。因此，您正在查找以任何一种类别购买任何内容的所有客户，然后从主要查询的结果中排除它们。

以上是 对于现有查询SQL联盟 的全部内容， 来源链接： utcz.com/qa/266316.html

回到顶部