sql server缺少顺序 [英] sql server missing order
本文介绍了sql server缺少顺序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好
i有这样的数据
19 ,< span class =code-comment>' 全球'
156111 , ' Calvin'
153211 ,' James A. Garfield'
152111 ,' James Buchanan'
159211 ,' Lyndon B. Johnson'
150411 ,' 麦迪逊'
151211 , William Henry Harrison'
<温泉n class =code-digit> 154111 ,' William McKinley'
当我插入表变量时,它以下列格式显示但我想显示我如何插入表变量
< span class =code-digit> 156111 ,' Calvin'
19 ,' 全球'
153211 ,' James A. Garfield'
152111 ,' James Buchanan'
159211 ,' Lyndon B 。约翰逊'
150411 ,' 麦迪逊'
151211 ,< span class =code-comment>' William Henry Harrison'
154111 ,' William McKinley'
解决方案
尝试在表变量中创建标识列,请参阅 http:// msdn。 microsoft.com/en-us/library/ms186775.aspx [ ^ ]。
这将在插入每一行时自动生成唯一ID,然后当您从表中选择var ORDER时,该ID为
SQL标准不保证订单数据将被退回,除非使用order by明确排序。一些供应商提供(大多数情况下)将按输入顺序返回数据的实现。但是你不能也不应该依赖它。
你需要提供保存数据的表(或表变量),其中包含一个可用于指定顺序的字段。
实现此目标的最简单方法在T-SQL中,提供一个Identity列,每次添加行时都会自动递增。有关详细信息,请参阅上面Chill60发布的解决方案中的链接。
SET NOCOUNT ON
DECLARE @ Jim TABLE
(
OrderBy int identity ( 0 , 1 ),
KeyField int ,
ValueField varchar ( 50 )
)
INSERT INTO @ JIM (KeyField,ValueField) VALUES (< span class =code-digit> 19 ,' Global')
INSERT INTO @ JIM (KeyField,ValueField) VALUES ( 156111 ,' Calvin')
INSERT INTO @ JIM (KeyField,ValueField) VALUES ( 153211 ,' James A. Garfield')
< span class =code-keyword> INSERT INTO @ JIM (KeyField,ValueField) VALUES ( 152111 ,' James Buchanan')
INSERT INTO @ JIM (KeyField,ValueField) VALUES ( 159211 ,' Lyndon B. Johnson')
INSERT INTO @ JIM (KeyField,ValueField) VALUES ( 150411 , ' 麦迪逊')
INSERT INTO @ JIM (KeyField,ValueField) VALUES ( 151211 ,' William Henry Harrison')
INSERT INTO @ JIM (KeyField,ValueField) VALUES ( 154111 ,' William McKinley')
从 @ jim 订单中选择 * 按 orderby asc
会得到你:
OrderBy KeyField ValueField
----------- ------ ----- --------------------------------------------- -----
0 19全球
1 156111 Calvin
2 153211 James A. Garfield
3 152111 James Buchanan
4 159211 Lyndon B. Johnson
5 150411麦迪逊
6 151211威廉亨利哈里森
7 154111威廉McKinley
DECLARE @Jim TABLE
(
KeyField int ,
ValueField varchar ( 50 )
)
< span class =code-keyword> INSERT INTO @ Jim
< span class =code-keyword>选择 19 ,' 全局'
UNION
选择 < span class =code-digit> 156111 ,' Calvin'
UNION
选择 153211 , ' James A. Garfield'
UNION
选择 152111 ,' James Buchanan'
UNION
选择 159211 ,' Lyndon B. Johnson'
UNION
选择 150411 ,' 麦迪逊'
UNION
选择 151211 ,' William Henry Harrison'
UNION
< span class =code-keyword>选择 154111 ,' William McKinley'
选择 * FROM @ Jim
Hi all
i have data like this
19 , 'Global'
156111 , 'Calvin'
153211 , 'James A. Garfield '
152111 , 'James Buchanan'
159211 , 'Lyndon B. Johnson'
150411 , 'Madison'
151211 , 'William Henry Harrison'
154111 , 'William McKinley'
when i insert into table variable its displaying in following format but i want display how i inserted in table variable
156111 , 'Calvin'
19 , 'Global'
153211 , 'James A. Garfield'
152111 , 'James Buchanan'
159211 , 'Lyndon B. Johnson'
150411 , 'Madison'
151211 , 'William Henry Harrison'
154111 , 'William McKinley'
解决方案
Try creating an identity column in your table variable see http://msdn.microsoft.com/en-us/library/ms186775.aspx[^].
This will automatically generate a unique id as each row is inserted, then when you select from the table var ORDER the results by that id
The SQL standard requires no guarantee about the order data will be returned in unless it is explicitly ordered using "order by". Some vendors' provide implementations that (most of the time) will return data in the order it was entered. However you cannot and should not rely on this.
You need to provide the table (or table variable) holding the data with a field that can be used to specify order.
The simplest way of achieving this In T-SQL is to provide an Identity column which will automatically increment every time a row is added. See the link in the solution posted by Chill60 above for more information.
SET NOCOUNT ON DECLARE @Jim TABLE ( OrderBy int identity(0,1), KeyField int, ValueField varchar(50) ) INSERT INTO @JIM (KeyField, ValueField) VALUES (19 , 'Global') INSERT INTO @JIM (KeyField, ValueField) VALUES (156111 , 'Calvin') INSERT INTO @JIM (KeyField, ValueField) VALUES (153211 , 'James A. Garfield ') INSERT INTO @JIM (KeyField, ValueField) VALUES (152111 , 'James Buchanan') INSERT INTO @JIM (KeyField, ValueField) VALUES (159211 , 'Lyndon B. Johnson') INSERT INTO @JIM (KeyField, ValueField) VALUES (150411 , 'Madison') INSERT INTO @JIM (KeyField, ValueField) VALUES (151211 , 'William Henry Harrison') INSERT INTO @JIM (KeyField, ValueField) VALUES (154111 , 'William McKinley') select * from @jim order by orderby asc
Will get you:
OrderBy KeyField ValueField ----------- ----------- -------------------------------------------------- 0 19 Global 1 156111 Calvin 2 153211 James A. Garfield 3 152111 James Buchanan 4 159211 Lyndon B. Johnson 5 150411 Madison 6 151211 William Henry Harrison 7 154111 William McKinley
DECLARE @Jim TABLE ( KeyField int, ValueField varchar(50) ) INSERT INTO @Jim Select 19 , 'Global' UNION Select 156111, 'Calvin' UNION Select 153211 , 'James A. Garfield ' UNION Select 152111 , 'James Buchanan' UNION Select 159211 , 'Lyndon B. Johnson' UNION Select 150411 , 'Madison' UNION Select 151211 , 'William Henry Harrison' UNION Select 154111 , 'William McKinley' Select * FROM @Jim
这篇关于sql server缺少顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文