试试这个:select id,time,other
from
(
select *,1 as v from Tab_A union all select *,2 as v from Tab_B
where not exists(select 1 from Tab_A where Tab_A.id = Tab_B.id)
)t
order by v,time
from
(
select *,1 as v from Tab_A union all select *,2 as v from Tab_B
where not exists(select 1 from Tab_A where Tab_A.id = Tab_B.id)
)t
order by v,time
select 1,'2010-01-01',123 union all
select 2,'2010-01-02',123create table Tab_B(id int, time datetime,other int)insert into Tab_B
select 2,'2010-01-01',123 union all
select 3,'2010-01-03',123 union all
select 4,'2010-01-02',123
select id,time,other
from
(
select *,1 as v from Tab_A union all select *,2 as v from Tab_B
where not exists(select 1 from Tab_A where Tab_A.id = Tab_B.id)
)t
order by v,time
/*
id time other
1 2010-01-01 00:00:00.000 123
2 2010-01-02 00:00:00.000 123
4 2010-01-02 00:00:00.000 123
3 2010-01-03 00:00:00.000 123
*/
create table Tab_A (id int,[time] datetime,other nvarchar(10))
insert into Tab_A
select 1,'2013-01-01','A' union all
select 2,'2013-01-02','B' union all
select 3,'2013-01-03','C' union all
select 4,'2013-01-04','D'
create table Tab_B (id int,[time] datetime,other nvarchar(10))
insert into Tab_B
select 2,'2013-01-06','B' union all
select 4,'2013-01-04','C' union all
select 5,'2013-01-01','D'select *,'A' from Tab_A
UNION ALL
select *,'B' from Tab_B where Tab_B.id not in(select id from Tab_A) order by time
/*
1 2013-01-01 00:00:00.000 A A
2 2013-01-02 00:00:00.000 B A
3 2013-01-03 00:00:00.000 C A
4 2013-01-04 00:00:00.000 D A
5 2013-01-08 00:00:00.000 D B
*/
create table Tab_A (id int,[time] datetime,other nvarchar(10))
insert into Tab_A
select 1,'2013-01-01','A' union all
select 2,'2013-01-02','B' union all
select 3,'2013-01-03','C' union all
select 4,'2013-01-04','D'
create table Tab_B (id int,[time] datetime,other nvarchar(10))
insert into Tab_B
select 2,'2013-01-06','B' union all
select 4,'2013-01-04','C' union all
select 5,'2013-01-01','D'
select *,'A' AS 表 from Tab_A
UNION ALL
select *,'B' from Tab_B where Tab_B.id not in(select id from Tab_A) order by 表,time
/*
1 2013-01-01 00:00:00.000 A A
2 2013-01-02 00:00:00.000 B A
3 2013-01-03 00:00:00.000 C A
4 2013-01-04 00:00:00.000 D A
5 2013-01-01 00:00:00.000 D B
*/
declare @page_size int;
declare @page_num int;--比如:每页10条记录
set @page_size = 10;--比如:先取第1页
set @page_num = 1;select id,time,other
from
(
select id,time,other,
(row_number() over(order by v,time) - 1) / @page_size as rownum
from
(
select *,1 as v from Tab_A union all select *,2 as v from Tab_B
where not exists(select 1 from Tab_A where Tab_A.id = Tab_B.id)
)t
)t
where rownum = @page_num - 1
你的方法可以如果要在最终结果上实现分页功能,提取指定页的几条记录呢?你用的是2000,还是2005呢sql 2005能不能按得到的记录顺序生成一个依次递增的自然数序列,比如从0开始递增
你的方法可以如果要在最终结果上实现分页功能,提取指定页的几条记录呢?你用的是2000,还是2005呢sql 2005能不能按得到的记录顺序生成一个依次递增的自然数序列,比如从0开始递增是这样吗:
declare @page_size int;
declare @page_num int;--比如:每页10条记录
set @page_size = 10;--比如:先取第1页
set @page_num = 1;select id,time,other,
ROW_NUMBER() over(order by getdate())-1 '自然数序列'
from
(
select id,time,other,
(row_number() over(order by v,time) - 1) / @page_size as rownum
from
(
select *,1 as v from Tab_A union all select *,2 as v from Tab_B
where not exists(select 1 from Tab_A where Tab_A.id = Tab_B.id)
)t
)t
where rownum = @page_num - 1
/*
id time other 自然数序列
1 2010-01-01 00:00:00.000 123 0
2 2010-01-02 00:00:00.000 123 1
4 2010-01-02 00:00:00.000 123 2
3 2010-01-03 00:00:00.000 123 3
*/
你的方法可以如果要在最终结果上实现分页功能,提取指定页的几条记录呢?你用的是2000,还是2005呢sql 2005能不能按得到的记录顺序生成一个依次递增的自然数序列,比如从0开始递增多谢
你的方法可以如果要在最终结果上实现分页功能,提取指定页的几条记录呢?你用的是2000,还是2005呢sql 2005能不能按得到的记录顺序生成一个依次递增的自然数序列,比如从0开始递增多谢
在union all 语句中:
select *,1 as v from Tab_A union all select *,2 as v from Tab_B where not exists(select 1 from Tab_A where Tab_A.id = Tab_B.id)
为了防止死锁什么的
有必要加在from Tab_A和from Tab_B后with(nolock)吗?
为了防止死锁什么的
有必要加在from Tab_A和from Tab_B后with(nolock)吗?
为了防止死锁什么的
有必要加在from Tab_A和from Tab_B后with(nolock)吗?嗯 加上with(nolock)把
为了防止死锁什么的
有必要加在from Tab_A和from Tab_B后with(nolock)吗?嗯 加上with(nolock)把
能否通过把union all改为union而不使用not exists呢?如果可以,这两种方式差别大吗
为了防止死锁什么的
有必要加在from Tab_A和from Tab_B后with(nolock)吗?嗯 加上with(nolock)把
能否通过把union all改为union而不使用not exists呢?如果可以,这两种方式差别大吗不能这样,因为union 是对所有字段进行去重,而not exists是对某一些列去重,上面就是对id列去重,所以不能修改为union,另外not exists的性能也更好
为了防止死锁什么的
有必要加在from Tab_A和from Tab_B后with(nolock)吗?嗯 加上with(nolock)把
能否通过把union all改为union而不使用not exists呢?如果可以,这两种方式差别大吗不能这样,因为union 是对所有字段进行去重,而not exists是对某一些列去重,上面就是对id列去重,所以不能修改为union,另外not exists的性能也更好多谢