跨月字段比较 2 个表之间的值 SQL Server

当前演示:

命令表表达式返回是否曾订购过任何项目.然后，我们只需要计算/求和:

Current demo: http://sqlfiddle.com/#!18/7acdc/17

I am looking for a total of how many items were added in a specific month but then a total of how many were never ordered after that.

Tables:

CREATE TABLE Item (
    ItemNo varchar(10)
   ,DateAdded varchar(10)
);

CREATE TABLE Order1 (
    OrderNo int,
    ItemNo varchar(10),
    OrderDate varchar(10)
);


INSERT INTO Item (ItemNo, DateAdded)
VALUES ('111', 'Jan-17'),
('222', 'Jan-17'),
('333', 'Jan-17'),
('444', 'Feb-17'),
('555', 'Feb-17'),
('666', 'Feb-17');

INSERT INTO Order1 (ItemNo, OrderDate)
VALUES ('111', 'Jan-17'),
('111', 'Feb-17'),
('222', 'May-17'),
('333', 'Jan-17'),
('333', 'March-17'),
('444', 'Jan-17');

Currently i have:

SELECT  
-- b.OrderDate,
    A.DateAdded,
    COUNT(DISTINCT A.ItemNo) AS [Items Added],
    COUNT(CASE WHEN c.ItemNo IS NULL THEN 1 END) as [Items Never Ordered]
FROM    Item a
    CROSS JOIN (SELECT  DISTINCT OrderDate FROM Order1) b
    LEFT JOIN Order1 c
        ON a.ItemNo = c.ItemNo
            AND b.OrderDate = c.OrderDate 

  GROUP BY A.DateAdded

Which produces:

| DateAdded | Items Added | Items Never Ordered |
|-----------|-------------|---------------------|
|    Feb-17 |           3 |                  11 |
|    Jan-17 |           3 |                   7 |

But i am looking for a result set such as:

| DateAdded | Items Added | Items Never Ordered |
|-----------|-------------|---------------------|
|    Feb-17 |           3 |                   2 |
|    Jan-17 |           3 |                   0 |

I am struggling to get this working. Do i need a sub-query or something to match the items individually. Can anyone push me in the right direction? Thanks

解决方案

Maybe this:

WITH DataSource AS
(
    SELECT A.*
         ,MAX([IsEverOrdered]) OVER (PARTITION BY ItemNo) [IsEverOrdered]
    FROM Item A
    OUTER APPLY
    (
        SELECT 1 [IsEverOrdered]
        FROM Order1 B
        WHERE A.[ItemNo] = B.[ItemNo]
    ) DS
)
SELECT DateAdded
      ,COUNT(DISTINCT ItemNo) AS [Items Added]
      ,SUM(CASE WHEN [IsEverOrdered] IS NULL THEN 1 ELSE 0 END) AS [Items Never Ordered]
FROM DataSource
GROUP BY DateAdded;

The Commant Table Expression returns if any item was ever ordered. Then, we just need to count/sum: