SQL Server 2008比较同一数据库中的两个表，并更改get列 [英] SQL Server 2008 compare two tables in same database and get column is changed

问题描述

我需要弄清两个表之间的区别. 我需要比较产品，数量和数量；对两个表中的列进行定价，然后说出它的新记录还是我需要提及更改了哪个列值.

I need to get the difference between two tables. I need to compare Product, Qty & price columns from two tables and say if its new record or I need to mention which column value is changed.

示例表A

Product | Qty | Price | Comments

A          20    500     xyz
B          50    200     xyz
C          90    100     abc

示例表B

Product | Qty | Price | Comments

A          20    500     sd
B          70    200     cv
C          90    200     wsd
D          50    500     xyz

当前，我使用的是Expect，它提供所有新的/不匹配的行.

Currently I am using Expect which gives all new / mismatched rows.

select Product,Qty,Price 
from TableB 
except 
select Product,Qty,Price 
from TableA

Product | Qty | Price 
B          70    200   
C          90    200   
D          50    500   

但是我需要像下面这样的结果集

But I need the result set like below

Product |  Result

B          Updated Qty
C          Updated Price
D          New

推荐答案

您可以使用LEFT JOIN:

SELECT  b.Product,
        b.Qty,
        b.Price,
        Result = CASE WHEN a.product IS NULL THEN 'New'
                    ELSE 'Updated: ' + 
                        STUFF(  CASE WHEN a.Qty != b.Qty THEN ',Qty' ELSE '' END + 
                                CASE WHEN a.Price != b.Price THEN ',Price' ELSE '' END,
                            1, 1, '')
                END
FROM    TableB b    
        LEFT JOIN TableA a
            ON a.Product = b.Product
WHERE   a.Product IS NULL
OR      a.Qty != b.Qty
OR      a.Price != b.Price;

关于SQL Fiddle的示例

Example on SQL Fiddle

这篇关于SQL Server 2008比较同一数据库中的两个表，并更改get列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！