create table test_a
(id int)create table test_b
(id int)insert test_a
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 7insert test_b
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 6 select id as id_1,id_2=(select min(id) from test_b a where a.id>b.id) from test_a b
drop table test_a,test_b
id_1 id_2
----------- -----------
1 2
2 3
3 5
5 6
7 NULL(所影响的行数为 5 行)
(id int)create table test_b
(id int)insert test_a
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 7insert test_b
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 6 select id as id_1,id_2=(select min(id) from test_b a where a.id>b.id) from test_a b
drop table test_a,test_b
id_1 id_2
----------- -----------
1 2
2 3
3 5
5 6
7 NULL(所影响的行数为 5 行)
解决方案 »
- 在没有安装 SQLServer2000的情况下,使用 osql
- 如何创建带多个参数的用户自定义函数?
- 怎样在SQL中实现这种排序?
- sqlserver中如何让一个表中查出来的数据隐藏10%
- 讨论一下 游标 和 sp_executesql的效率
- sql server 表处理问题?
- 请问在文章内寻找某个单词 用like效率是不是不太好啊?
- 特急:高手快来:请教如何恢复数据库表中已经删除的数据
- 关于分布式查询的问题,急!
- 请问各位大虾,怎么自动生成一个三位的序列号呀!
- SQL Express 2005是不是像Access一样的,可以到处Copy文件呢?
- 关于调用函数特别郁闷的问题。大师们帮忙看看,谢谢!
union all select 2
union all select 3
union all select 5
union all select 7insert into @d select 1
union all select 2
union all select 3
union all select 5
union all select 6select t.id as id_1,(select min(d.id) from @d d where d.id>t.id) as id_2 from @t t/*id_1 id_2
----------- -----------
1 2
2 3
3 5
5 6
7 NULL
*/
得出的结果应该是:
id_1 id_2
1 2
2 3
3 5
5 6
7 Null
create table a(id int)create table b (id int)insert into a select 1
union all select 2
union all select 3
union all select 5
union all select 7insert into b select 1
union all select 2
union all select 3
union all select 5
union all select 6select a.id as id1 ,min(b.id) as id2 from a left join b on b.id>a.id
group by a.idid1 id2
----------- -----------
1 2
2 3
3 5
5 6
7 NULL(所影响的行数为 5 行)
declare @t_a table(id int)
insert @t_a
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 7declare @t_b table(id int)
insert @t_b
--test_b的结构如下:
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 6select id_1=a.id,
id_2=(select min(id) from @t_b b where b.id>a.id)
from @t_a a
/*
id_1 id_2
----------- -----------
1 2
2 3
3 5
5 6
7 NULL
*/
id count
1 1000
2 2000
3 3000
5 4000
6 5000
然后要显示结果是
id1 id2 count
----------- -----------
1 2 2000
2 3 3000
3 5 5000
5 6 6000
7 NULL Null
又应该怎么做呢?