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

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

tsql sql-server sql

Ryan Gadsdon · 技术社区 · 8 年前

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

我在这里创建了一个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 回复 | 直到 8 年前

John Woo 8 年前

您需要使用 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 .

Gordon Linoff 8 年前

您可以使用 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小提琴。

JamieD77 8 年前

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

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

paparazzo 8 年前

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

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