I am using SQL2000 and I would like to join two table together based on their positions
我正在使用SQL2000,我想根据他们的位置加入两个表
For example consider the following 2 tables:
例如,考虑以下2个表:
table1 ------- name ------- 'cat' 'dog' 'mouse' table2 ------ cost ------ 23 13 25
I would now like to blindly join the two table together as follows based on their order not on a matching columns (I can also guarantee both tables have the same number of rows):
我现在想根据它们的顺序盲目地将两个表连接在一起,而不是匹配的列(我也可以保证两个表具有相同的行数):
-------|----- name |cost -------|------ 'cat' |23 'dog' |13 'mouse'|25
Is this possible in a T-SQL select??
这是否可以在T-SQL中选择?
9 个解决方案
#1
5
This is NOT possible, since there's absolutely no guarantee regarding the order in which the rows will be selected.
这是不可能的,因为绝对不能保证选择行的顺序。
There are a number of ways to achieve what you want (see other answers) provided you're lucky regarding the sorting order, but none will work if you aren't, and you shouldn't rely on such queries.
有很多方法可以实现你想要的东西(见其他答案),只要你对排序顺序很幸运,但是如果你没有,那么没有方法可以工作,你不应该依赖这些查询。
Being forced to do this kind of queries strongly smells of a bad database design.
*做这种查询强烈反映了糟糕的数据库设计。
#2
2
in 2000 you will either have to run 2 forward only cursors and insert into a temp table. or insert the values into a temp table with an extra identity column and join the 2 temp tables on the identity field
在2000年,您将要么必须运行2个仅向前游标并插入临时表。或者将值插入带有额外标识列的临时表,并将标识字段上的2个临时表连接起来
#3
2
If your tables aren't two large, you could create two temp tables in memory and select your content into them in a specific order, and then join them on the row Number.
如果您的表不是两个大表,您可以在内存中创建两个临时表,并按特定顺序选择您的内容,然后将它们连接到行号。
e.g.
CREATE TABLE #Temp_One (
[RowNum] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (50) NOT NULL
)
CREATE TABLE #Temp_Two (
[RowNum] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (50) NOT NULL
)
INSERT INTO #Temp_One
SELECT Your_Column FROM Your_Table_One ORDER BY Whatever
INSERT INTO #Temp_Two
SELECT Your_Column FROM Your_Table_Two ORDER BY Whatever
SELECT *
FROM #Temp_One a
LEFT OUTER JOIN #Temp_Two b
On a.RowNum = b.RowNum
#4
1
Do you have anything that guarantees ordering of each table?
你有什么保证每张桌子的订购吗?
As far ax I know, SQL server does not make any promise on the ordering of a resultset unless the outer query has an order by clause. In your case you need Each table to be ordered in a deterministic manner for this to work.
据我所知,除非外部查询具有order by子句,否则SQL Server不会对结果集的排序做出任何承诺。在您的情况下,您需要以确定的方式对每个表进行排序才能使其正常工作。
Other than that, in SQL 2000, as answered before me, a temp table and two cursors seem like a good answer.
除此之外,在SQL 2000中,正如我之前所回答的那样,临时表和两个游标似乎是一个很好的答案。
Update: Someone mentioned inserting both tables into temp tables, and that it would yield better performance. I am no SQL expert so I defer to those who know on that front, and since I had an up-vote I thought you should investigate those performance considerations. But in any case, if you do not have any other information in your tables than what you showed us I'm not sure you can pull it off, ordering-wise.
更新:有人提到将两个表插入临时表,并且它会产生更好的性能。我不是SQL专家所以我尊重那些在这方面知道的人,因为我有一个向上投票,我认为你应该调查这些性能考虑因素。但无论如何,如果你的桌子上没有任何其他信息,那么你不能确定你可以将它拉下来,顺序排序。
#5
1
Absolutely. Use the following query but make sure that (order by) clause uses the same columns the order of rows will change which you dont want.
绝对。使用以下查询但请确保(order by)子句使用相同的列,行的顺序将更改您不想要的。
select
(
row_number() over(order by name) rno, * from Table1
) A
(
row_number() over(order by name) rno, * from Table2
) B
JOIN A.rno=B.rno
order by clause can be modified according to user linkings
order by子句可以根据用户链接进行修改
The above query produces unique row_numbers for each row, which an be joined with row_numbers of the other table
上面的查询为每一行产生唯一的row_numbers,它与另一个表的row_numbers连接
#6
0
Consider using a rank (rownum in Oracle) to dynamically apply ordered unique numbers to each table. Simply join on the rank column and you should have what you need. See this Microsoft article on numbering rows.
考虑使用排名(Oracle中的rownum)动态地将有序的唯一数字应用于每个表。只需加入排名列,您就应该拥有所需的一切。请参阅有关编号行的Microsoft文章。
#7
0
would be best to use row_number(), but that is only for 2005 and 2008, this should work for 2000...
最好使用row_number(),但这只适用于2005年和2008年,这应该适用于2000 ...
Try this:
create table table1 (name varchar(30))
insert into table1 (name) values ('cat')
insert into table1 (name) values ('dog')
insert into table1 (name) values ('mouse')
create table table2 (cost int)
insert into table2 (cost) values (23)
insert into table2 (cost) values (13)
insert into table2 (cost) values (25)
Select IDENTITY(int,1,1) AS RowNumber
, Name
INTO #Temp1
from table1
Select IDENTITY(int,1,1) AS RowNumber
, Cost
INTO #Temp2
from table2
select * from #Temp1
select * from #Temp2
SELECT
t1.Name, t2.Cost
FROM #Temp1 t1
LEFT OUTER JOIN #Temp2 t2 ON t1.RowNumber=t2.RowNumber
ORDER BY t1.RowNumber
#8
0
Xynth - built in row numbering is not available until SQL2K5 unfortunately, and the example given by microsoft actually uses triangular joins - a horrific hidden performance hit if the tables get large. My preferred approach would be an insert into a pair of temp tables using the identity function and then join on these, which is basically the same answer already given. I think the two-cursors approach sounds much heavier than it needs to be for this task.
Xynth - 内置行编号在SQL2K5之前不可用,不幸的是,微软给出的例子实际上使用了三角形连接 - 如果表格变大,则会出现可怕的隐藏性能。我首选的方法是使用身份函数插入一对临时表,然后加入这些,这基本上是已经给出的相同答案。我认为双光标方法听起来比它完成这项任务要重得多。
#9
0
You could alter both tables to have an auto_increment column, then join on that.
您可以更改两个表以具有auto_increment列,然后加入该表。
As others have told you, SQL has no intrinsic ordering; a table of rows is a set. Any ordering you get is arbitrary, unless you add an order by clause.
正如其他人告诉你的那样,SQL没有内在的排序;行表是一组。除非您添加order by子句,否则您获得的任何顺序都是任意的。
So yeah, there are ways you can do this, but all of them depend on the accidental ordering being what you hope it is. So do this this once, and don't do it again unless you can come up with a way (auto_increments, natural keys, something) to ensure ordering.
所以,是的,你有办法做到这一点,但所有这些都取决于意外排序是你希望它是什么。所以这一次这样做,除非你能想出一种方法(auto_increments,自然键,某些东西)以确保订购,所以不要再这样做了。
#1
5
This is NOT possible, since there's absolutely no guarantee regarding the order in which the rows will be selected.
这是不可能的,因为绝对不能保证选择行的顺序。
There are a number of ways to achieve what you want (see other answers) provided you're lucky regarding the sorting order, but none will work if you aren't, and you shouldn't rely on such queries.
有很多方法可以实现你想要的东西(见其他答案),只要你对排序顺序很幸运,但是如果你没有,那么没有方法可以工作,你不应该依赖这些查询。
Being forced to do this kind of queries strongly smells of a bad database design.
*做这种查询强烈反映了糟糕的数据库设计。
#2
2
in 2000 you will either have to run 2 forward only cursors and insert into a temp table. or insert the values into a temp table with an extra identity column and join the 2 temp tables on the identity field
在2000年,您将要么必须运行2个仅向前游标并插入临时表。或者将值插入带有额外标识列的临时表,并将标识字段上的2个临时表连接起来
#3
2
If your tables aren't two large, you could create two temp tables in memory and select your content into them in a specific order, and then join them on the row Number.
如果您的表不是两个大表,您可以在内存中创建两个临时表,并按特定顺序选择您的内容,然后将它们连接到行号。
e.g.
CREATE TABLE #Temp_One (
[RowNum] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (50) NOT NULL
)
CREATE TABLE #Temp_Two (
[RowNum] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (50) NOT NULL
)
INSERT INTO #Temp_One
SELECT Your_Column FROM Your_Table_One ORDER BY Whatever
INSERT INTO #Temp_Two
SELECT Your_Column FROM Your_Table_Two ORDER BY Whatever
SELECT *
FROM #Temp_One a
LEFT OUTER JOIN #Temp_Two b
On a.RowNum = b.RowNum
#4
1
Do you have anything that guarantees ordering of each table?
你有什么保证每张桌子的订购吗?
As far ax I know, SQL server does not make any promise on the ordering of a resultset unless the outer query has an order by clause. In your case you need Each table to be ordered in a deterministic manner for this to work.
据我所知,除非外部查询具有order by子句,否则SQL Server不会对结果集的排序做出任何承诺。在您的情况下,您需要以确定的方式对每个表进行排序才能使其正常工作。
Other than that, in SQL 2000, as answered before me, a temp table and two cursors seem like a good answer.
除此之外,在SQL 2000中,正如我之前所回答的那样,临时表和两个游标似乎是一个很好的答案。
Update: Someone mentioned inserting both tables into temp tables, and that it would yield better performance. I am no SQL expert so I defer to those who know on that front, and since I had an up-vote I thought you should investigate those performance considerations. But in any case, if you do not have any other information in your tables than what you showed us I'm not sure you can pull it off, ordering-wise.
更新:有人提到将两个表插入临时表,并且它会产生更好的性能。我不是SQL专家所以我尊重那些在这方面知道的人,因为我有一个向上投票,我认为你应该调查这些性能考虑因素。但无论如何,如果你的桌子上没有任何其他信息,那么你不能确定你可以将它拉下来,顺序排序。
#5
1
Absolutely. Use the following query but make sure that (order by) clause uses the same columns the order of rows will change which you dont want.
绝对。使用以下查询但请确保(order by)子句使用相同的列,行的顺序将更改您不想要的。
select
(
row_number() over(order by name) rno, * from Table1
) A
(
row_number() over(order by name) rno, * from Table2
) B
JOIN A.rno=B.rno
order by clause can be modified according to user linkings
order by子句可以根据用户链接进行修改
The above query produces unique row_numbers for each row, which an be joined with row_numbers of the other table
上面的查询为每一行产生唯一的row_numbers,它与另一个表的row_numbers连接
#6
0
Consider using a rank (rownum in Oracle) to dynamically apply ordered unique numbers to each table. Simply join on the rank column and you should have what you need. See this Microsoft article on numbering rows.
考虑使用排名(Oracle中的rownum)动态地将有序的唯一数字应用于每个表。只需加入排名列,您就应该拥有所需的一切。请参阅有关编号行的Microsoft文章。
#7
0
would be best to use row_number(), but that is only for 2005 and 2008, this should work for 2000...
最好使用row_number(),但这只适用于2005年和2008年,这应该适用于2000 ...
Try this:
create table table1 (name varchar(30))
insert into table1 (name) values ('cat')
insert into table1 (name) values ('dog')
insert into table1 (name) values ('mouse')
create table table2 (cost int)
insert into table2 (cost) values (23)
insert into table2 (cost) values (13)
insert into table2 (cost) values (25)
Select IDENTITY(int,1,1) AS RowNumber
, Name
INTO #Temp1
from table1
Select IDENTITY(int,1,1) AS RowNumber
, Cost
INTO #Temp2
from table2
select * from #Temp1
select * from #Temp2
SELECT
t1.Name, t2.Cost
FROM #Temp1 t1
LEFT OUTER JOIN #Temp2 t2 ON t1.RowNumber=t2.RowNumber
ORDER BY t1.RowNumber
#8
0
Xynth - built in row numbering is not available until SQL2K5 unfortunately, and the example given by microsoft actually uses triangular joins - a horrific hidden performance hit if the tables get large. My preferred approach would be an insert into a pair of temp tables using the identity function and then join on these, which is basically the same answer already given. I think the two-cursors approach sounds much heavier than it needs to be for this task.
Xynth - 内置行编号在SQL2K5之前不可用,不幸的是,微软给出的例子实际上使用了三角形连接 - 如果表格变大,则会出现可怕的隐藏性能。我首选的方法是使用身份函数插入一对临时表,然后加入这些,这基本上是已经给出的相同答案。我认为双光标方法听起来比它完成这项任务要重得多。
#9
0
You could alter both tables to have an auto_increment column, then join on that.
您可以更改两个表以具有auto_increment列,然后加入该表。
As others have told you, SQL has no intrinsic ordering; a table of rows is a set. Any ordering you get is arbitrary, unless you add an order by clause.
正如其他人告诉你的那样,SQL没有内在的排序;行表是一组。除非您添加order by子句,否则您获得的任何顺序都是任意的。
So yeah, there are ways you can do this, but all of them depend on the accidental ordering being what you hope it is. So do this this once, and don't do it again unless you can come up with a way (auto_increments, natural keys, something) to ensure ordering.
所以,是的,你有办法做到这一点,但所有这些都取决于意外排序是你希望它是什么。所以这一次这样做,除非你能想出一种方法(auto_increments,自然键,某些东西)以确保订购,所以不要再这样做了。