22,207
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-06-01 21:22:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([id] int,[姓名] varchar(2),[身份证号码] bigint,[出生日期] datetime)
insert [a]
select 1,'aa',610411195011220556,'1950-11-22' union all
select 2,'aa',610411195010220557,'1950-10-22' union all
select 3,'bb',610411195011230558,'1950-11-23'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[姓名] varchar(2),[身份证号码] bigint,[出生日期] datetime)
insert [b]
select 1,'aa',610411196011220559,'1960-11-22' union all
select 2,'bb',610411194010220555,'1940-10-22' union all
select 3,'bb',610411197011230552,'1970-11-23'
--> 测试数据:[c]
if object_id('[c]') is not null drop table [c]
go
create table [c]([id] int,[身份证号码] bigint)
insert [c]
select 1,610411196011220559 union all
select 2,610411194010220555 union all
select 3,610411197011230552
--------------开始查询--------------------------
select
姓名,t.身份证号码,出生日期
from
(
select * from a
union all
select * from b
)t
join c on
t.身份证号码=c.身份证号码
where
t.姓名='bb'
----------------结果----------------------------
/* 姓名 身份证号码 出生日期
---- -------------------- -----------------------
bb 610411194010220555 1940-10-22 00:00:00.000
bb 610411197011230552 1970-11-23 00:00:00.000
(2 行受影响)
*/
select 姓名,身份证号,出生日期
(select * from a
union all
select * from b) tmp
inner join c on tmp.身份证号=c.身份证号
where tmp.姓名=bb
select * from(
select * from A
union all
select * from B
) t , C where t.身份证号码=c.身份证号码
select t.*
from
(select * from a union all select * from b) t
join c on t.身份证号码=c.身份证号码
where t.姓名='XXX'
select t.*
from
(select * from a union all select * from b) t
join c on t.身份证号码=c.身份证号码
;with TT
as
(select * from A union all select * from B)
select C.* from TT inner join C on TT.id = C.id where TT.姓名 = '张三'