有两张表,一张为结婚登记表,结构为
A表(结婚登记)
mr_name mr_cercode mr_date
(分别为姓名、身份证号和登记时间)
B表(离婚登记)
dr_name dr_cercode dr_date为了用姓名加身份证号码判断某个人目前的婚姻状况,我就要判断这个人最后一次登记的类型了,就是要得到下面这样一个结构的查询结果:
name cercode date type(登记类型)。SQL语句该如何写呢?
比如我现在查姓名为 malename 身份证号码为 malecode的状况,查询结过为这样的一表结构的表:name cercode date type(登记类型)。
A表(结婚登记)
mr_name mr_cercode mr_date
(分别为姓名、身份证号和登记时间)
B表(离婚登记)
dr_name dr_cercode dr_date为了用姓名加身份证号码判断某个人目前的婚姻状况,我就要判断这个人最后一次登记的类型了,就是要得到下面这样一个结构的查询结果:
name cercode date type(登记类型)。SQL语句该如何写呢?
比如我现在查姓名为 malename 身份证号码为 malecode的状况,查询结过为这样的一表结构的表:name cercode date type(登记类型)。
select mr_name as name , mr_cercode as cercode , mr_date as date ,'结婚' as type from A
union
select dr_name as name , dr_cercode as cercode , dr_date as date ,'离婚' as type from B
) a order by date desc
*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码) 最新版本:20070212http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
where mr_cercode= dr_cercode MSSQL中把iif换成case then else
(
select mr_name as name , mr_cercode as cercode , mr_date as date ,'结婚' as type from A
union
select dr_name as name , dr_cercode as cercode , dr_date as date ,'离婚' as type from B
) order by date desc
select mr_name as name , mr_cercode as cercode , mr_date as date ,'结婚' as type from A
union
select dr_name as name , dr_cercode as cercode , dr_date as date ,'离婚' as type from B
) as a where name='malename' and cercode='malecode order by date desc