在INSERT INTO..SELECT中订购 [英] Order by in a INSERT INTO..SELECT
问题描述
我执行了以下基本语句：
创建临时表，＃TempPaging
插入#TempPaging（Col1， Col2）
选择Col1，Col2从SomeOtherTable订购Col2，Col1
从#TempPaging中选择*
我现在不能提供一个可重现的场景而不将这个
变成一个200K的帖子，所以我希望有人知道这个问题是什么..我
在BOL中找不到任何关于此的内容。
基本上订单有点不时有点
是大量的数据，就像超过几百行。是否
我需要做些什么来保证最初选择的
订单？
这是非常重要的从原始选择
语句订购，因为这是用于分页。在临时表中从第二个
中选择添加订单将无法解决问题。在我复制的这个特殊的
实例中，我们使用的是SQL 2005，但是在SQL 2000服务器上也看到了这个。我之前曾问过这个问题
我正在使用SELECT INTO语句，但是现在我们手动创建了
临时表（痛苦的屁股）并且仍然拥有同样的问题。最佳
案例场景适用于SELECT INTO。
任何想法？
pb648174写道：我正在执行以下基本语句：
创建临时表，＃TempPaging
插入#TempPaging（Col1，Col2）
选择Col1，Col2从SomeOtherTable订购Col2，Col1
从#TempPaging选择*
我现在不能提供一个可重现的场景而不把它变成一个200K的帖子，所以我希望有人会知道这个问题是什么..我找不到任何关于这个的事情在BOL。
基本上，当有大量数据（如超过几百行）时，订单会不时出现。是否有什么我需要做的事情来保证最初选择的订单？
这是非常重要的，它是从原来的选择
声明的顺序，因为这用于分页。在临时表中从第二个
选择添加订单将无法解决问题。在我已经复制的这个特定的实例中，我们使用的是SQL 2005，但是也在SQL 2000服务器上看到过这种情况。我以前曾问过这个问题
我正在使用SELECT INTO语句，但现在我们手动创建了临时表（痛苦的屁股），但仍然有同样的问题。最佳
案例场景适用于SELECT INTO。
任何想法？
表格不在逻辑上排序任何情况。你需要
在这里指定ORDER BY：
从#TempPaging中选择*
ORDER BY ...
也就是说，当你查询表时，而不是当你插入时。
请参阅下面的文章，了解一些可靠的分页技术（一个
示例使用与您相同的方法，以避免一个）：
http://www.aspfaq.com/show.asp?id=2120
-
David Portas， SQL Server MVP
只要有可能，请发布足够的代码来重现您的问题。
包含CREATE TABLE和INSERT语句通常会有所帮助。
说明您正在使用的SQL Server版本，并指定任何错误消息的内容
。
SQL Server联机丛书：
http://msdn2.microsoft.com/library /女士， SQL.90）.aspx
-
pb648174（goCon@webpaul.net ）写道：我执行了以下基本语句：
创建临时表，＃TempPaging
插入#TempPaging（Col1， Col2）
选择Col1，Col2从SomeOtherTable订购Col2，Col1
选择*来自#TempPaging
我现在不能提供可重现的方案使这个
成为一个200K的帖子，所以我希望有人知道这是什么问题..我在BOL找不到任何相关的内容。
基本上，当有大量数据（例如超过几百行）时，顺序会有所不同。是否有什么我需要做的事情来保证最初选择的订单？
这是非常重要的，它是从原来的选择
声明的顺序，因为这用于分页。在临时表中从第二个
选择添加订单将无法解决问题。
一旦数据在#TempPaging中，ORDER BY将导致订购
页面。但是如果#TempPaging没有正确加载
，那就无济于事了。
在我转载的这个特殊实例中，我们使用的是SQL 2005
但也见过这在SQL 2000服务器上。
如果您使用的是SQL 2005，最好使用row_number（）：
SELECT OrderID，CustomerID，OrderDate，rowno
FROM（SELECT OrderID，CustomerID，OrderDate，
rowno = row_number（）OVER
（PARTITION BY 1订单来自CustomerID，OrderID）
来自Northwind..Orders）AS x
WHERE rowno BETWEEN 100和200
订购rowno
在SQL 2000上，您可以使用带有IDENTITY列的临时表表，
并使用ORDER BY插入到该表中。我被告知这是保证可以工作的，但我似乎记得大卫声称
看到了相互矛盾的证词。
请注意，这仅适用于INSERT - 它*不适用于SELECT INTO。
-
Erland Sommarskog，SQL Server MVP，< a href =mailto：es **** @ sommarskog.se> es **** @ sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft .com / technet / pro ... ads / books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
是;拍摄你并替换你的程序员已经阅读了**任何** RDBMS书的第一页。您正在发布真实的baaaaad
问题。请花点时间赶上周末
你要求我们再为你做好工作。
SQL是一种定向语言。表 - 根据定义 - 没有订购
。这就是一套的本质。您是否阅读过Codd博士的RDBMS规则/查看信息原则：所有
关系在列中显示为值。订购是一个
的关系，所以你需要一个专栏。
如果你不知道Codd博士是谁或他的规则，然后你就像一个从未听说过欧几里德的几何学生。
I have the following basic statements being executed:
Create a temp table, #TempPaging
Insert Into #TempPaging (Col1, Col2)
Select Col1, Col2 From SomeOtherTable Order By Col2, Col1
Select * from #TempPaging
I can''t provide a reproduceable scenario right now without making this
into a 200K post, so I''m hoping someone will know what the issue is.. I
can''t find anything regarding this in BOL.
Basically the order is off a little bit every now and then when there
are large amounts of data, like more than a couple hundred rows. Is
there something I need to do to guarantee the originally selected
order?
This is very important that it be in order from the original select
statement as this is for paging. Adding an order by in the second
select from the temp table will not fix the problem. In this particular
instance that I have reproduced we are using SQL 2005 but have also
seen this on SQL 2000 servers. I had previously asked this question as
I was using a SELECT INTO statement, but now we are manually creating
the temp table (Pain in the ass) and still having the same issue. Best
case scenario is for it to work for a SELECT INTO.
Any ideas?
pb648174 wrote:I have the following basic statements being executed:
Create a temp table, #TempPaging
Insert Into #TempPaging (Col1, Col2)
Select Col1, Col2 From SomeOtherTable Order By Col2, Col1
Select * from #TempPaging
I can''t provide a reproduceable scenario right now without making this
into a 200K post, so I''m hoping someone will know what the issue is.. I
can''t find anything regarding this in BOL.
Basically the order is off a little bit every now and then when there
are large amounts of data, like more than a couple hundred rows. Is
there something I need to do to guarantee the originally selected
order?
This is very important that it be in order from the original select
statement as this is for paging. Adding an order by in the second
select from the temp table will not fix the problem. In this particular
instance that I have reproduced we are using SQL 2005 but have also
seen this on SQL 2000 servers. I had previously asked this question as
I was using a SELECT INTO statement, but now we are manually creating
the temp table (Pain in the ass) and still having the same issue. Best
case scenario is for it to work for a SELECT INTO.
Any ideas?
Tables are NOT logically ordered under any circumstances. You need to
specify ORDER BY here:
Select * from #TempPaging
ORDER BY ...
That is, when you QUERY the table, not when you INSERT.
See the following article for some reliable paging techniques (one
example uses the same method you have so avoid that one):
http://www.aspfaq.com/show.asp?id=2120
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--
pb648174 (go****@webpaul.net) writes:I have the following basic statements being executed:
Create a temp table, #TempPaging
Insert Into #TempPaging (Col1, Col2)
Select Col1, Col2 From SomeOtherTable Order By Col2, Col1
Select * from #TempPaging
I can''t provide a reproduceable scenario right now without making this
into a 200K post, so I''m hoping someone will know what the issue is.. I
can''t find anything regarding this in BOL.
Basically the order is off a little bit every now and then when there
are large amounts of data, like more than a couple hundred rows. Is
there something I need to do to guarantee the originally selected
order?
This is very important that it be in order from the original select
statement as this is for paging. Adding an order by in the second
select from the temp table will not fix the problem.
Once the data is in #TempPaging an ORDER BY will result in that
page being ordered. But that does not help if #TempPaging was not
loaded correctly.
In this particular instance that I have reproduced we are using SQL 2005
but have also seen this on SQL 2000 servers.
If you are on SQL 2005, the best is to use row_number():
SELECT OrderID, CustomerID, OrderDate, rowno
FROM (SELECT OrderID, CustomerID, OrderDate,
rowno = row_number() OVER
(PARTITION BY 1 ORDER BY CustomerID, OrderID)
FROM Northwind..Orders) AS x
WHERE rowno BETWEEN 100 AND 200
ORDER BY rowno
On SQL 2000 you can use a temp table table with an IDENTITY column,
and insert to that table with ORDER BY. I am told that this is
guaranteed to work, although I seem to recall that David claimed
to have seen conflicting testimony.
Note that this applies to INSERT only - it does *not* apply to SELECT INTO.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Yes; shot you and replace you with a programmer who has read the first
10 pages of **any** RDBMS book. You are posting realllllllly baaaaad
questions. Please take some time to catch up over the weekend before
you ask us to do your job for you again.
SQL is a set-orient language. Tables -- by definition -- have no
ordering. That is the nature of a set. Have you ever read Dr. Codd''s
12 rules for RDBMS/ Look up the Information Principle: all
relationships are shown as values in columns. Ordering is a
relationship, so you need a column(s) for it.
If you do not know who Dr. Codd is or his rules, then you are like a
Geometry student who never heard of Euclid.
这篇关于在INSERT INTO..SELECT中订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
