两个完全相同的表查询 查询结果按时间排列出 顺序,前面还 带上 序号
如 table1
id 自动编号 name varchar(50) lrdate datetime
1 张三 2013-09-02 09:12:07
2 张三1 2013-10-03 10:14:07
4 张三2 2013-11-05 15:12:07
如 table2
id 自动编号 name varchar(50) lrdate datetime
3 里明 2013-10-02 09:16:07
4 王明元 2013-11-03 10:12:07
5 李开富 2013-12-05 15:16:07
从这两个表里查询
查询全部数据。结果按时间排序,前面要有唯一一个序号,如:
1 张三 2013-09-02 09:12:07
2 里明 2013-10-02 09:16:07
3 张三1 2013-10-03 10:14:07
4 王明元 2013-11-03 10:12:07
5 张三2 2013-11-05 15:12:07
6 李开富 2013-12-05 15:16:07
[解决办法]
create table table1(id Int,name varchar(50) , lrdate datetime)
insert into table1
select 1 ,'张三 ' ,'2013-09-02 09:12:07' union all
select 2 ,'张三1' ,'2013-10-03 10:14:07' union all
select 4 ,'张三2' ,'2013-11-05 15:12:07'
create table table2(id int,name varchar(50),lrdate datetime)
insert into table2
select 3 ,'里明' ,'2013-10-02 09:16:07' union all
select 4 ,'王明元' ,'2013-11-03 10:12:07' union all
select 5 ,'李开富' ,'2013-12-05 15:16:07'
go
select
ROW_NUMBER() over(order by lrdate) as id,
name,
lrdate
from
(
select * from table1
union all
select * from table2
)t
order by id
/*
idnamelrdate
1张三 2013-09-02 09:12:07.000
2里明2013-10-02 09:16:07.000
3张三12013-10-03 10:14:07.000
4王明元2013-11-03 10:12:07.000
5张三22013-11-05 15:12:07.000
6李开富2013-12-05 15:16:07.000
*/