插入临时表时按不工作排序 [英] Order by not working when insert in temp table
问题描述
我有一个查询,当我在 SQL Server 2012 中执行它时,ORDER BY
子句不起作用.请帮助我.问候.
I have a query, and when I execute it in SQL Server 2012, the ORDER BY
clause is not working. Please help me in this. Regards.
DECLARE @Data table (Id int identity(1,1), SKU varchar(10), QtyRec int,Expiry date,Rec date)
DECLARE @Qty int = 20
INSERT @Data
VALUES
('001A', 5 ,'2017-01-15','2015-11-14'),
('001A', 8 ,'2017-01-10','2015-11-14'),
('001A', 6 ,'2015-12-15','2015-11-15'),
('001A', 25,'2016-01-01','2015-11-16'),
('001A', 9 ,'2015-12-20','2015-11-17');
SELECT *
INTO #temp
FROM @Data
ORDER BY Id DESC
SELECT *
FROM #temp
推荐答案
SQL 表表示 无序 集合.这有什么不清楚的吗?
SQL tables represent unordered sets. Is there something unclear about this?
当您从表中SELECT
时,结果是无序.一个例外是当您在外部查询中使用 ORDER BY
时.因此,包含一个 ORDER BY
并且结果将是有序的.
When you SELECT
from a table, then the results are unordered. The one exception is when you use an ORDER BY
in the outer query. So, include an ORDER BY
and the results will be in order.
您可以通过引入聚集主键来消除排序的工作.
You can eliminate the work for the sort by introducing a clustered primary key.
create table #temp (
Id int identity(1,1) primary key clustered,
SKU varchar(10),
QtyRec int,
Expiry date,
Rec date
);
然后当你这样做时:
insert into #temp(SKU, QtyRec, Expiry, Rec)
select SKU, QtyRec, Expiry, Rec
from @Data
order by id;
#temp
中的聚集主键保证按照order by
指定的顺序.然后查询:
The clustered primary key in #temp
is guaranteed to be in the order specified by the order by
. Then the query:
select *
from #temp
order by id;
将使用聚集索引按顺序返回结果.不需要排序.
will return the results in order, using the clustered index. No sort will be needed.
这篇关于插入临时表时按不工作排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!