使用 SQL Server，如何将记录连接到所有“相似”记录的最小值同一个表中的记录？

Question

[请原谅格式错误——首先在这里发帖，我认真地尝试做正确的事情]

从功能上来说，我只是想找到“类似”商品分组中价格最低的商品。例如——葡萄酒有很多，但我需要找到价格最低的750ml红葡萄酒和白葡萄酒；奶酪有很多，但我需要找到价格最低的 8 盎司 CHEDDAR 以及 FETA；等等。

我发现的所有解决方案都说要执行与我的代码中相同的操作。 我的“自加入”一定有问题。选择查询本身的运行时间不到 10 秒，嵌套选择也是如此。但是，当我尝试加入它们时，我的查询挂起并且永远不会完成。 必须有某种方法可以成功完成此加入。

我正在提取数百万行数据。每一行都是一个独特的商品/商店组合。我的嵌套选择试图找到非“名牌”商品的最低价格；最低价格是在“类似”商品中找到的，由以下列确定：store_name、category、subcategory、class、package_desc2、unit_name、chk（在我的查询中，“chk”由两个不同列的结果确定）。

这为我提供了上述列的所有组合的不同列表，以及每个组合的最低价格。我正在尝试将其加入到非“名称”品牌商品中，以查看我们在嵌套选择中找到的价格低廉的确切商品是什么。任何帮助表示赞赏！我已经研究了好几天了，但无法弄清楚。

这里的 SQL：

SELECT b.zone_name, 
       b.store_name, 
       b.family, 
       b.category, 
       b.subcategory, 
       b.class, 
       b.team, 
       b.subteam, 
       b.pos_dept, 
       b.brand_name, 
       b.item_description, 
       b.upc, 
       b.package_desc1         pkg, 
       b.package_desc2         sz, 
       b.unit_name, 
       CASE 
         WHEN b.good = 'good' 
               OR b.how_good = 1 THEN 'YES' 
         ELSE 'NO' 
       END                     AS chk, 
       b.eff_pricetype, 
       b.eff_price             low_price, 
       b.cd                    dollar_sales, 
       b.cu                    unit_sales, 
       b.cgm                   margin_dollars, 
       b.cgm / Nullif(b.cd, 0) AS margin_pct, 
       b.deleted_item, 
       b.discontinue_item, 
       b.not_available, 
       b.remove_item, 
       b.recall_flag, 
       CASE 
         WHEN 
SUM( 
Isnull(b.deleted_item, 0) + Isnull(b.discontinue_item, 0) + Isnull(b.not_available, 0) + Isnull(b.remove_item, 0) + Isnull(b.recall_flag, 0)) = 0 THEN
         'NO' 
  ELSE 'YES' 
END                     AS istatus, 
d.low 
FROM   mytable b 
       INNER JOIN(SELECT c.store_name, 
                         c.category, 
                         c.subcategory, 
                         c.class, 
                         c.package_desc2, 
                         c.unit_name, 
                         CASE 
                           WHEN c.good = 'good' 
                                 OR c.how_good = 1 THEN 'YES' 
                           ELSE 'NO' 
                         END              AS chk, 
                         MIN(c.eff_price) low 
                  FROM   mytable c 
                  WHERE  store_name = 'some store' 
                         AND brand_name NOT LIKE '%name%' 
                         AND weeks = 'Last 12 weeks' 
                         AND ( eff_pricetype = 'REG' 
                                OR eff_pricetype = 'EDV' 
                                OR eff_pricetype = 'GBC' 
                                OR eff_pricetype = 'CMP' 
                                OR eff_pricetype = 'LIN' 
                                OR eff_pricetype = 'FRZ' 
                                OR eff_pricetype = 'GBB' 
                                OR eff_pricetype = 'EDLP' 
                                OR eff_pricetype = 'GBN' 
                                OR eff_pricetype = 'GBR' 
                                OR eff_pricetype = 'MKT' 
                                OR eff_pricetype = 'COMP' 
                                OR eff_pricetype = 'R' 
                                OR eff_pricetype = 'COM' ) 
                         AND ( family = 'carrots' 
                                OR family = 'tomatoes' 
                                OR family = 'Cheese' 
                                OR family = 'Coffee' 
                                OR family = 'peppers' 
                                OR family = 'milk' 
                                OR family = 'oil' 
                                OR family = 'season' 
                                OR family = 'Housewares' 
                                OR family = 'paper' 
                                OR family = 'Meat' 
                                OR family = 'soup' 
                                OR family = 'nuts' 
                                OR family = 'pizza' 
                                OR family = 'potatoes' 
                                OR family = 'Seafood' 
                                OR family = 'beer' 
                                OR family = 'vitamins' 
                                OR family = 'Tea' 
                                OR family = 'Wine' 
                                OR family = 'beans' ) 
                  GROUP  BY c.store_name, 
                            c.category, 
                            c.subcategory, 
                            c.class, 
                            c.package_desc2, 
                            c.unit_name, 
                            c.good, 
                            c.how_good 
                  HAVING MIN(c.eff_price) > 0) AS d 
         ON b.store_name = d.store_name 
            AND b.category = d.category 
            AND b.subcategory = d.subcategory 
            AND b.class = d.class 
            AND b.package_desc2 = d.package_desc2 
            AND b.unit_name = d.unit_name 
            AND CASE 
                  WHEN b.good = 'good' 
                        OR b.how_good = 1 THEN 'YES' 
                  ELSE 'NO' 
                END = d.chk 
            AND b.eff_price = d.low 
WHERE  store_name = 'some store' 
       AND brand_name NOT LIKE '%name%' 
       AND weeks = 'Last 12 weeks' 
       AND ( eff_pricetype = 'REG' 
              OR eff_pricetype = 'EDV' 
              OR eff_pricetype = 'GBC' 
              OR eff_pricetype = 'CMP' 
              OR eff_pricetype = 'LIN' 
              OR eff_pricetype = 'FRZ' 
              OR eff_pricetype = 'GBB' 
              OR eff_pricetype = 'EDLP' 
              OR eff_pricetype = 'GBN' 
              OR eff_pricetype = 'GBR' 
              OR eff_pricetype = 'MKT' 
              OR eff_pricetype = 'COMP' 
              OR eff_pricetype = 'R' 
              OR eff_pricetype = 'COM' ) 
       AND ( family = 'carrots' 
              OR family = 'tomatoes' 
              OR family = 'Cheese' 
              OR family = 'Coffee' 
              OR family = 'peppers' 
              OR family = 'milk' 
              OR family = 'oil' 
              OR family = 'season' 
              OR family = 'Housewares' 
              OR family = 'paper' 
              OR family = 'Meat' 
              OR family = 'soup' 
              OR family = 'nuts' 
              OR family = 'pizza' 
              OR family = 'potatoes' 
              OR family = 'Seafood' 
              OR family = 'beer' 
              OR family = 'vitamins' 
              OR family = 'Tea' 
              OR family = 'Wine' 
              OR family = 'beans' ) 
GROUP  BY b.zone_name, 
          b.store_name, 
          b.family, 
          b.category, 
          b.subcategory, 
          b.class, 
          b.team, 
          b.subteam, 
          b.pos_dept, 
          b.brand_name, 
          b.item_description, 
          b.upc, 
          b.package_desc1, 
          b.package_desc2, 
          b.unit_name, 
          d.org, 
          b.eff_pricetype, 
          b.eff_price, 
          b.cd, 
          b.cu, 
          b.cgm, 
          b.deleted_item, 
          b.discontinue_item, 
          b.not_available, 
          b.remove_item, 
          b.recall_flag, 
          d.low, 
          b.good, 
          b.how_good 

Answer 1

您可以通过将系列和价格类型子句更改为使用 IN 而不是 OR 来简化代码并可能提高性能。

例如：

   AND eff_pricetype IN ('REG','EDV','GBC','CMP','LIN','FRZ',
                         'GBB','EDLP','GBN','GBR','MKT','COMP','R','COM') 

   AND family IN ('carrots','tomatoes','Cheese','Coffee', etc...

另外，如果您的子查询按照您所说的那样工作，您可以将它们放入两个不同的表变量中，然后连接它们。