TSQL 中的循环和更新语句不起作用

Question

Angel D'souza

Asked: 2023-08-19 01:43:21 +0800 CST2023-08-19 01:43:21 +0800 CST 2023-08-19 01:43:21 +0800 CST

根据关键列比较两个表的数据并获取自定义报告

772

SQL Server 2014

有2个表：

BI 和 Cust。

BI 的 PK：长项

CUST 的 PK：长项

如何在列级别比较 2 个表，并根据长项的主键 (PK) 查找不匹配（如果存在）。

数据和 DDL 如下所示：

CREATE  TABLE BI
(
chain VARCHAR(500),
sku VARCHAR (150),
longitem VARCHAR(500),
color_id              INT,
color_desc         VARCHAR(200),
size_id  INT,
size_name          VARCHAR(200)
 
)
INSERT BI
 
 
 
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30407092',              '33300000200010354003',           035,       'WOOL GABARDINE', 243,              '40  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '29308392',              '33300000200040204602',           020,       'TONAL WEAVE',                256,       '46  REG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '29310505',              '33300000200160033601',           003,       'MED DOT STRP',   231,   '36  SHR'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30743884',              '33300000200180044403',           004,       'SMALL TWILL 2B', 253,  '44  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '28994986',              '33300000200340413803',           041,       'PLAIN PINDOT 3B',238,  '38  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30745574',              '33300000200780413801',           041,       'STRUCTURED BTN', 236,              '38  SHR'            UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30742241',              '33300000200790414200',           041,       'BASIC BL BT NTC',248,   '42  REG'             UNION ALL
SELECT 'ECOMM',                                '44746212',   '33300010002560010179',              001,       'WEST-US-0602021',419,              'OS'



CREATE  TABLE CUST
(
chain_ID VARCHAR(500),
sku_id VARCHAR (150),
longitem VARCHAR(500),
color_id              INT,
color_desc         VARCHAR(200),
size_id  INT,
size_name          VARCHAR(200)
 
)
INSERT CUST
 
 
 
SELECT 'FSC',      '30407092',        '33300000200010354003',           035,              'WOOL GABARDINE', 243,            '40  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '29308392',              '33300000200040204602',           020,       'TONAL WEAVE',                256,       '46  REG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '293XXX05',              '33300000200160033601',           003,       'DOT STRP',   231,            '36  SHR'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30743884',              '33300000200180044403',           004,       'SMALL TWILL 2B', 253,  '44  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '28994986',              '33300000200340413803',           041,       'PLAIN PINDOT 3B',238,  '38  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30745574',              '33300000200780413801',           041,       'STRUCTURED BTN', 236,              '38  SHR'            UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30742241',              '33300000200790414200',           111,       'BASIC BL BT NTC',248,   '42  REG'             UNION ALL
SELECT 'ECOMM',                                '44746212',   '33300010002560010179',              001,       'WEST-US-0602021',419,              'OS'`

我尝试过类似的东西

Select * from BI as B inner join cust as c on b.longitem = c.longitem
where a.chain <> b.chain
or a.sku <> b.sku_id
or a.color_id <> b.color_id
or a.color_desc <> b.color_desc
or a.size_id <> b.size_id
or a.size_name <> b.size_name

预期输出

longitem|Source_Column_name|Target_column_name|SRC_VALUE      |TARGET_VALUE


33300000200010354003|chain|chain_ID|FSC - FACTORY STORE CONCEPTS| FSC

33300000200160033601|SKU|SKU_ID|29308392|293XXX05

33300000200790414200|color_id |color_id |041|111

非常感谢你的帮助

1 个回答

Voted

Yitzhak Khabinsky · Answer 1 · 2023-08-19T03:11:36+08:00

请尝试以下通用解决方案。

它根据相同的列名比较两个表。

因此，您可以为第二个表创建一个视图（或另一个 CTE），以便将列命名为与第一个表完全相同的名称。

SQL

-- DDL and sample data population, start
DECLARE @BI  TABLE 
(
    chain VARCHAR(500),
    sku VARCHAR (150),
    longitem VARCHAR(500),
    color_id INT,
    color_desc VARCHAR(200),
    size_id INT,
    size_name VARCHAR(200)
);
INSERT @BI
VALUES
('FSC - FACTORY STORE CONCEPTS', '30407092', '33300000200010354003', 035, 'WOOL GABARDINE', 243, '40  LNG'),
('FSC - FACTORY STORE CONCEPTS', '29308392', '33300000200040204602', 020, 'TONAL WEAVE',    256, '46  REG'),
('FSC - FACTORY STORE CONCEPTS', '29310505', '33300000200160033601', 003, 'MED DOT STRP',   231, '36  SHR'),
('FSC - FACTORY STORE CONCEPTS', '30743884', '33300000200180044403', 004, 'SMALL TWILL 2B', 253, '44  LNG'),
('FSC - FACTORY STORE CONCEPTS', '28994986', '33300000200340413803', 041, 'PLAIN PINDOT 3B',238, '38  LNG'),
('FSC - FACTORY STORE CONCEPTS', '30745574', '33300000200780413801', 041, 'STRUCTURED BTN', 236, '38  SHR'),
('FSC - FACTORY STORE CONCEPTS', '30742241', '33300000200790414200', 041, 'BASIC BL BT NTC',248, '42  REG'),
('ECOMM',                        '44746212', '33300010002560010179', 001, 'WEST-US-0602021',419, 'OS');

DECLARE @CUST TABLE 
(
    chain VARCHAR(500),
    sku VARCHAR (150),
    longitem VARCHAR(500),
    color_id INT,
    color_desc VARCHAR(200),
    size_id INT,
    size_name VARCHAR(200)
);
INSERT @CUST
VALUES
('FSC',                          '30407092', '33300000200010354003', 035, 'WOOL GABARDINE', 243, '40  LNG'),
('FSC - FACTORY STORE CONCEPTS', '29308392', '33300000200040204602', 020, 'TONAL WEAVE',    256, '46  REG'),
('FSC - FACTORY STORE CONCEPTS', '293XXX05', '33300000200160033601', 003, 'DOT STRP',       231, '36  SHR'),
('FSC - FACTORY STORE CONCEPTS', '30743884', '33300000200180044403', 004, 'SMALL TWILL 2B', 253, '44  LNG'),
('FSC - FACTORY STORE CONCEPTS', '28994986', '33300000200340413803', 041, 'PLAIN PINDOT 3B',238, '38  LNG'),
('FSC - FACTORY STORE CONCEPTS', '30745574', '33300000200780413801', 041, 'STRUCTURED BTN', 236, '38  SHR'),
('FSC - FACTORY STORE CONCEPTS', '30742241', '33300000200790414200', 111, 'BASIC BL BT NTC',248, '42  REG'),
('ECOMM',                        '44746212', '33300010002560010179', 001, 'WEST-US-0602021',419, 'OS');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT Src = 1
        , longitem 
        , c.value('local-name(.)','SYSNAME') AS [Key]
        , c.value('text()[1]','VARCHAR(MAX)') AS [Value]
    FROM @BI AS t
      CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x)
      CROSS APPLY x.nodes('/root/*') AS t2(c)
    UNION ALL
    SELECT Src = 2
        , longitem 
        , c.value('local-name(.)','SYSNAME') AS [Key]
        , c.value('text()[1]','VARCHAR(MAX)') AS [Value]
    FROM @CUST AS t
      CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x)
      CROSS APPLY x.nodes('/root/*') AS t2(c)
)
SELECT longitem
   , [key] AS [column]
   , Org_Value = MAX(CASE WHEN Src = 1 THEN Value END)
   , New_Value = MAX(CASE WHEN Src = 2 THEN Value END)
FROM rs
GROUP BY longitem, [key]
HAVING COALESCE(MAX(CASE WHEN Src = 1 THEN Value END),'')
     <> COALESCE(MAX(CASE WHEN Src = 2 THEN Value END),'')
ORDER BY longitem,[key];

输出

长项	柱子	组织值	新值
33300000200010354003	链	FSC - 工厂店概念	森林管理委员会
33300000200160033601	颜色描述	医疗点STRP	点STRP
33300000200160033601	存货单元	29310505	293XXX05
33300000200790414200	颜色编号	41	111

根据关键列比较两个表的数据并获取自定义报告

使用 <font color="#xxx"> 突出显示 html 中的代码

为什么在传递 {} 时重载解析更喜欢 std::nullptr_t 而不是类？

您可以使用花括号初始化列表作为（默认）模板参数吗？

为什么列表推导式在内部创建一个函数？

我正在尝试仅使用海龟随机和数学模块来制作吃豆人游戏

java.lang.NoSuchMethodError: 'void org.openqa.selenium.remote.http.ClientConfig.<init>(java.net.URI, java.time.Duration, java.time.Duratio

为什么 'char -> int' 是提升，而 'char -> Short' 是转换（但不是提升）？

为什么库中不调用全局变量的构造函数？

std::common_reference_with 在元组上的行为不一致。哪个是对的？

C++17 中 std::byte 只能按位运算？

根据关键列比较两个表的数据并获取自定义报告

1 个回答

相关问题