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
非常感谢你的帮助