插入临时表时按不工​​作排序 [英] 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屋！