记录是通过excel导入的,查询两张表相同记录
表[IcNew] 、[IcOld] 两表结构相同
Model:必填
Supplier:允许空
DateCode:允许空注:下面的[Null]代表数据库的Null,[空串]代表空字符
表[IcNew]
ID Model Supplier DateCode
17 11111 FSC 09+
18 22222 FSC 0817+
19 33333 [空串] 0741+
20 44444 FSC [Null]
21 55555 [Null] 0511+表[IcOld]
ID Model Supplier DateCode
17 11111 FSC 09+
18 22222 FSC 0817+
19 33333 [空串] 0741+
20 44444 FSC [Null]
21 55555 [Null] 0511+
SELECT N.ID, N.Model
FROM IcNew N INNER JOIN
IcOld O ON N.DateCode = O.DateCode AND N.Supplier = O.Supplier AND
N.Model = O.ModelSELECT ID, Model
FROM IcNew N
WHERE EXISTS
(SELECT 1
FROM IcOld O
WHERE N.DateCode = O.DateCode AND N.Supplier = O.Supplier AND
N.Model = O.Model)这两条查询出来的都是只有3条记录,按理来说是应该有5条记录才对啊?怎么个回事?要如何解决呢?
ID Model
17 11111
18 22222
19 33333
表[IcNew] 、[IcOld] 两表结构相同
Model:必填
Supplier:允许空
DateCode:允许空注:下面的[Null]代表数据库的Null,[空串]代表空字符
表[IcNew]
ID Model Supplier DateCode
17 11111 FSC 09+
18 22222 FSC 0817+
19 33333 [空串] 0741+
20 44444 FSC [Null]
21 55555 [Null] 0511+表[IcOld]
ID Model Supplier DateCode
17 11111 FSC 09+
18 22222 FSC 0817+
19 33333 [空串] 0741+
20 44444 FSC [Null]
21 55555 [Null] 0511+
SELECT N.ID, N.Model
FROM IcNew N INNER JOIN
IcOld O ON N.DateCode = O.DateCode AND N.Supplier = O.Supplier AND
N.Model = O.ModelSELECT ID, Model
FROM IcNew N
WHERE EXISTS
(SELECT 1
FROM IcOld O
WHERE N.DateCode = O.DateCode AND N.Supplier = O.Supplier AND
N.Model = O.Model)这两条查询出来的都是只有3条记录,按理来说是应该有5条记录才对啊?怎么个回事?要如何解决呢?
ID Model
17 11111
18 22222
19 33333
FROM IcNew N INNER JOIN
IcOld O ON isnull(N.DateCode,'') = isnull(O.DateCode,'') AND isnull(N.Supplier,'') = isnull(O.Supplier,'') AND
N.Model = O.Model
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-09 11:54:04
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[IcNew]
if object_id('[IcNew]') is not null drop table [IcNew]
go
create table [IcNew]([ID] int,[Model] int,[Supplier] varchar(3),[DateCode] varchar(5))
insert [IcNew]
select 17,11111,'FSC','09+' union all
select 18,22222,'FSC','0817+' union all
select 19,33333,null,'0741+' union all
select 20,44444,'FSC',null union all
select 21,55555,null,'0511+'
--> 测试数据:[IcOld]
if object_id('[IcOld]') is not null drop table [IcOld]
go
create table [IcOld]([ID] int,[Model] int,[Supplier] varchar(3),[DateCode] varchar(5))
insert [IcOld]
select 17,11111,'FSC','09+' union all
select 18,22222,'FSC','0817+' union all
select 19,33333,null,'0741+' union all
select 20,44444,'FSC',null union all
select 21,55555,null,'0511+'
--------------开始查询--------------------------
SELECT N.ID, N.Model
FROM IcNew N left JOIN
IcOld O ON N.DateCode = O.DateCode AND N.Supplier = O.Supplier AND
N.Model = O.Model
----------------结果----------------------------
/* ID Model
----------- -----------
17 11111
18 22222
19 33333
20 44444
21 55555(5 行受影响)
*/