代码之家  ›  专栏  ›  技术社区  ›  Ryan Gadsdon

计算两个表之间不匹配的数字

  •  1
  • Ryan Gadsdon  · 技术社区  · 7 年前

    我最初的查询是按月统计从未订购过的商品数量。

    我在这里创建了一个sql小提琴 http://sqlfiddle.com/#!18/e89a7/2

    并将下面的查询放在一起

    SELECT COUNT(ItemNo)
    FROM Item
    WHERE ItemNo NOT IN 
    (SELECT ItemNo
    FROM Order1)
    

    然而,这并不是我想要的,我想知道我是否走错了方向。我需要按订购的月份和年份对其进行分组,但NOT IN子句不允许我这样做。

    样本数据:

    CREATE TABLE Item (
        ItemNo varchar(10),
        MonthStocked varchar(10),
        YearStocked varchar(10)
    );
    
    CREATE TABLE Order1 (
        OrderNo int,
        ItemNo varchar(10),
        MonthOrdered varchar(10),
        YearOrdered varchar(10)
    );
    
    INSERT INTO Item (ItemNo, MonthStocked,YearStocked)
    VALUES ('111','Feb', 2017),
    ('222','Jan',  2018),
    ('333','Feb', 2017),
    ('444','Feb', 2017),
    ('555','Jan', 2017),
    ('666','Jan', 2017);
    
    INSERT INTO Order1 (OrderNo, ItemNo,MonthOrdered,YearOrdered)
    VALUES ('897', '111', 'Dec', '2017'),
    ('657', '222', 'Nov', '2017'),
    ('896', '333', 'Nov' , '2017'),
    ('867', '333', 'Dec' , '2017'),
    ('234', '444', 'Nov' , '2017');
    

    所需输出:

    | ItemsNotOrdered  |     Month    |     Year    |
    |------------------|--------------|-------------|
    |                3 |          Nov |        2017 |
    |                4 |          Dec |        2017 |
    
    4 回复  |  直到 7 年前
        1
  •  2
  •   John Woo    7 年前

    您需要使用 CROSS JOIN 在所有唯一之间 ItemNo 到所有唯一的有序月份和年份。然后,生成的产品将外部联接到表 Items 有三个条件。势不可挡的 项目编号 就是你要找的人。

    SELECT  b.MonthOrdered, b.YearOrdered,
            COUNT(CASE WHEN c.ItemNo IS NULL THEN 1 END)
    FROM    (SELECT DISTINCT ItemNo FROM Item) a
            CROSS JOIN (SELECT  DISTINCT MonthOrdered, YearOrdered FROM Order1) b
            LEFT JOIN Order1 c
                ON a.ItemNo = c.ItemNo
                    AND b.MonthOrdered = c.MonthOrdered 
                    AND b.YearOrdered = c.YearOrdered 
     GROUP BY b.MonthOrdered, b.YearOrdered
    

    这里有一个 Demo .

    如果 项目编号 是唯一的,消除子查询并直接使用表。

    SELECT  b.MonthOrdered, b.YearOrdered,
            COUNT(CASE WHEN c.ItemNo IS NULL THEN 1 END)
    FROM    Item a
            CROSS JOIN (SELECT  DISTINCT MonthOrdered, YearOrdered FROM Order1) b
            LEFT JOIN Order1 c
                ON a.ItemNo = c.ItemNo
                    AND b.MonthOrdered = c.MonthOrdered 
                    AND b.YearOrdered = c.YearOrdered 
     GROUP BY b.MonthOrdered, b.YearOrdered
    

    这里有一个 Demo .

        2
  •  1
  •   Gordon Linoff    7 年前

    您可以使用 cross join 生成所有项目/年/月组合。然后 left join group by 要获得所需的结果,请执行以下操作:

    select ym.mon, ym.mon, count(*)
    from item i cross join
         (select distinct MonthOrdered as mon, YearOrdered as yr from order1
         ) ym left join
         order1 o
         on i.itemno = o.itemno and ym.mon = o.MonthOrdered and ym.yr = o.YearOrdered 
    where o.itemno is null
    group by ym.yr, ym.mon;
    

    Here 是SQL小提琴。

        3
  •  0
  •   JamieD77    7 年前

    使用外部应用的另一个选项

    SELECT   DISTINCT 
             MonthOrdered,
             YearOrdered,
             ItemsNotOrdered
    FROM     Order1 o
    OUTER APPLY (
             SELECT COUNT(ItemNo) ItemsNotOrdered
             FROM   Item i
             WHERE  NOT EXISTS (
                SELECT  ItemNo 
                FROM    Order1 o2
                WHERE   i.ItemNo = o2.ItemNo
                AND     o2.MonthOrdered = o.MonthOrdered
                AND     o2.YearOrdered = o.YearOrdered
             ) 
    ) ino
    
        4
  •  0
  •   paparazzo    7 年前

    我不喜欢你想要的产量,因为它错过了几个月什么都卖不出去的时候。我也不喜欢月份不排序。

    declare @item table (ItemNo varchar(10), MonthStocked varchar(10), YearStocked varchar(10))
    INSERT INTO @Item (ItemNo, MonthStocked,YearStocked)
    VALUES ('111','Feb', 2017),
           ('222','Jan', 2018),
           ('333','Feb', 2017),
           ('444','Feb', 2017),
           ('555','Jan', 2017),
           ('666','Jan', 2017);
    
    declare @Order1 table (OrderNo int, ItemNo varchar(10), MonthOrdered varchar(10), YearOrdered varchar(10)) 
    INSERT INTO @Order1 (OrderNo, ItemNo,MonthOrdered,YearOrdered)
    VALUES ('897', '111', 'Dec', '2017'),
           ('657', '222', 'Nov', '2017'),
           ('896', '333', 'Nov', '2017'),
           ('867', '333', 'Dec', '2017'),
           ('234', '444', 'Nov', '2017');
    
    declare @itemCount int = (select count(distinct ItemNo) from @item);
    
    with CTEyymm as 
    ( select MonthStocked as mm, YearStocked as yy
        from @item 
      union
      select MonthOrdered, YearOrdered
        from @Order1
    )
    
    select yymm.yy, yymm.mm
         , (@itemCount - count(distinct(o.ItemNo))) as cnt  
      from CTEyymm yymm 
      left join @order1 o 
        on o.YearOrdered  = yymm.yy 
       and o.MonthOrdered = yymm.mm 
     group by yymm.yy, yymm.mm
     order by yymm.yy, yymm.mm;
    

    如果您不关心空白月份,那么只需:

    select o.YearOrdered, o.MonthOrdered
         , (@itemCount - count(distinct(o.ItemNo))) as cnt  
      from @order1 o 
     group by o.YearOrdered, o.MonthOrdered
     order by o.YearOrdered, o.MonthOrdered