SQL Server将一个表的行与其他表的所有行进行比较

I have 2 tables which contains firstname, surname,dob. First table has datatype as varchar for all columns and second table is varchar(50),varchar(50),datetime datatype.

我有2个表，其中包含名字，姓氏，dob。第一个表的数据类型为所有列的varchar，第二个表是varchar（50），varchar（50），datetime数据类型。

Ex:
1st table 
fname surname dob
a       b      04/12/1948
a       b      05/08/1984

 2nd table
fname surname dob
a       b      05/08/1984
a       b      04/12/1948

when i do Intersect is not matching the decors (possibly b'ze of datatype) and except give me result.

当我做Intersect不匹配装饰（可能是数据类型的b'ze），除了给我结果。

Is there any way to select all record from table 1 with matching records of table 2.

有没有办法从表1中选择表2中匹配记录的所有记录。

Any help much apprecciated

任何帮助都非常赞赏

4 个解决方案

#1

Both EXCEPT and INTERSECT work:

EXCEPT和INTERSECT都工作：

Declare @tblA AS TABLE(
    FirstName VARCHAR(50),
    SurName VARCHAR(50),
    Dob VARCHAR(50)
)

Declare @tblB AS TABLE(
    FirstName VARCHAR(50),
    SurName VARCHAR(50),
    Dob Date
)

INSERT INTO @tblA VALUES
('a','b','04/12/1948'),
('a','b','05/08/1948')

INSERT INTO @tblB VALUES
('a','b','04/12/1948'),
('a','b','05/09/1948')

SELECT
    *
FROM @tblA
INTERSECT
SELECT
    *
FROM @tblB

SELECT
    *
FROM @tblA
EXCEPT
SELECT
    *
FROM @tblB

Output:

输出：

#2

SELECT fname, surname, CAST(dob AS DATETIME) AS dob FROM table1
INTERSECT
SELECT fname, surname, dob FROM table2

#3

Use this query.

使用此查询。

Select * from table1 as a
Inner join table2 as b
on a.fname = b.fname and a.surname = b.surname and cast(a.dob as date)=b.dob

#4

SELECT * FROM #Table1 JOIN #Table2 ON #Table2.dob = CONVERT(DATETIME,#Table1.dob,101)

#1