如何比较sql中的cols [英] how to compare cols in sql

问题描述

大家好，



在Sql server 2008 r2。



我有两个相同的表no.of cols and cols名称及其数据类型。



场景

 tableOne 
  - ---------------------------------- 
 | col1 | col2 |日期| 
 | ---------------------------------- | 
 | A | Apple | 10/01/2014 | 
 | ---------------------------------- | 
 | B |球| 10/01/2014 | 
 ------------------------------------ 
 
 tableTwo 
 ------------------------------------ 
 | col1 | col2 |日期| 
 | ---------------------------------- | 
 | A | Apple | 10/02/2014 | 
 | ---------------------------------- | 
 | B |蝙蝠| 10/02/2014 | 
 ------------------------------------ 

我想选择那些不匹配的记录。



例如：tableOne的行'A'是与tableTwo的行'A'匹配，

但tableOne的行'B'与tableTwo的行'B'不匹配。



我想从tableTwo中选择行'B'，因为它不匹配。



上面的表格仅用于场景，实时我有1000条记录
两张桌子都是
。



任何人都可以帮助我...



谢谢

解决方案

试试这个

 选择 * 来自 tableTwo   选择 * 来自 tableOne 

假设你将变量中的两个日期命名为@currDate和@prevDate。进一步假设没有删除或新记录。更进一步假设col2不为null。

 选择 curr.col1，curr.col2  as  col2_curr，prev.col2  as  col2_prev 
 来自 [ table ] curr 
  inner   join  [表]上一页  prev.col1 = curr.col1 
 其中 curr。 date  =  @ currDate  
 和 prev。 date  =  @ prevDate  
 和 curr.col2<> prev.col2 

Hi guys,

In Sql server 2008 r2.

I have two table with same no.of cols and cols name and its data type.

scenario

tableOne
------------------------------------
|col1  | col2       | date         |
|----------------------------------|
| A    | Apple      | 10/01/2014   |
|----------------------------------|
| B    | Ball       | 10/01/2014   |
------------------------------------

tableTwo
------------------------------------
|col1  | col2       | date         |
|----------------------------------|
| A    | Apple      | 10/02/2014   |
|----------------------------------|
| B    | Bat        | 10/02/2014   |
------------------------------------

I want to select those records which are not matching.

for eg: row 'A' of tableOne is matching with row 'A' of tableTwo,
but row 'B' of tableOne is not matching with row 'B' of tableTwo.

I want to select row 'B' from tableTwo because it is not matching.

The above tables are just given for scenario, in real time i have 1000's of records
in both the tables.

Can anyone please help me...

Thanks

解决方案

Try this

select * from tableTwo except select * from tableOne

Assuming you have the two dates in variables named @currDate and @prevDate. Further assuming there are no deleted or new records. Even further assuming that col2 is not null.

select curr.col1, curr.col2 as col2_curr, prev.col2 as col2_prev
from [table] curr
inner join [table] prev on prev.col1 = curr.col1
where curr.date = @currDate
and prev.date = @prevDate
and curr.col2 <> prev.col2

这篇关于如何比较sql中的cols的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！