SQL代码如下:
declare @i int
set @i=0
select [email protected]+1,EmployeeID from Employees union select [email protected]+2,OrderID from Orders
执行结果:
执行的结果是:
1 1312
1 246
1 3
1 0989
1 5346
2 10248
2 10249
2 10250
2 10251
..
我本意是想这样:
1 1312
2 246
3 3
4 0989
5 5346
6 10248
7 10249
8 10250
9 10251
我是想连接几个表,然后用数字排序,能不能实现,
请高手指点
------解决方案--------------------
select identity(int,1,1),[id] into #t from (select EmployeeID as [id] from Employees union select OrderID as [id] from Orders ) a
select * from #t
drop table #t
------解决方案--------------------
create table #t (type identity(1,1) int, iid int)
insert #t
select EmployeeID from Employees union select OrderID from Orders
select * from #t
drop table #t
------解决方案--------------------
你的要求就是跟SQL查询分析器里查出的记录集前方它自己显示的序号吧
基于性能考虑，建议采用临时表处理
SELECT IDENTITY(INT, 1, 1) AS Rank,a.ID
INTO #tmp
FROM (select EmployeeID as ID from Employees union select OrderID as ID from Orders) a
SELECT * FROM #tmp
用完后DROP TABLE #tmp