ORDER BY 在 SELECT @some_var= 中工作吗
SQL Server 2008r2 Express SP1。 我需要在 EXEC“动态 SQL”的某个字段中列出所有不同日期的列表。当然，日期的顺序应该正确。下面的代码工作正常，但我的老板说，在大表上 ORDER BY 在某些情况下不起作用，因为 SELECT 语句中的 @pivot_list =... 。他曾使用过SqlServer2000，但这段代码甚至无法编译。谁能告诉我这是否适用于 SQL Server 2008r2？
USE tempdb
GO
DECLARE @pivot_list varchar(max)
CREATE TABLE #TovarSales(FullDate varchar(8))
INSERT #TovarSales VALUES ('20101010')
INSERT #TovarSales VALUES ('20101210')
INSERT #TovarSales VALUES ('20091010')
INSERT #TovarSales VALUES ('20111111')
INSERT #TovarSales VALUES ('20050505')
SELECT @pivot_list = ISNULL(@pivot_list + ', ', '') + '[' + TS.FullDate + ']'
FROM #TovarSales TS
GROUP BY TS.FullDate
ORDER BY TS.FullDate
select @pivot_list
DROP TABLE #TovarSales
谢谢。
SQL Server 2008r2 Express SP1.
I need to make a sting with list of all distinct dates in some field for EXEC 'dynamic SQL'. Of course, dates should be in right order. The code below works fine but my boss says that on big tables ORDER BY wouldn't work in some cases because of @pivot_list =... in the SELECT statement. He has worked with SqlServer2000 and there this code even wouldn't be compiled. Can anybody tell me whether this will work on SQL Server 2008r2?
USE tempdb
GO
DECLARE @pivot_list varchar(max)
CREATE TABLE #TovarSales(FullDate varchar(8))
INSERT #TovarSales VALUES ('20101010')
INSERT #TovarSales VALUES ('20101210')
INSERT #TovarSales VALUES ('20091010')
INSERT #TovarSales VALUES ('20111111')
INSERT #TovarSales VALUES ('20050505')
SELECT @pivot_list = ISNULL(@pivot_list + ', ', '') + '[' + TS.FullDate + ']'
FROM #TovarSales TS
GROUP BY TS.FullDate
ORDER BY TS.FullDate
select @pivot_list
DROP TABLE #TovarSales
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这段代码可以在 SQL Server 2008 中运行。
结果是
This code DOES work in SQL Server 2008.
The result is