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

Comparing values between 2 tables across month field SQL Server(跨月字段比较 2 个表之间的值 SQL Server)
本文介绍了跨月字段比较 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:

这篇关于跨月字段比较 2 个表之间的值 SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Convert varchar to float IF ISNUMERIC(将 varchar 转换为 float IF ISNUMERIC)
multi part identifier could not be bound sql(无法绑定多部分标识符 sql)
Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?(在存储过程结束时显式删除本地临时表有什么好处?)
Check if a column contains text using SQL(使用 SQL 检查列是否包含文本)
Select value if condition in SQL Server(SQL Server 中的条件选择值)
Control flow in T-SQL SP using IF..ELSE IF - are there other ways?(使用 IF..ELSE IF 在 T-SQL SP 中控制流 - 还有其他方法吗?)