使用 ORDER BY 從 SELECT 創建 Azure Synapse 臨時表

我有以下語句在我省略ORDER BY子句時有效，或者如果我將其作為選擇執行並省略創建表部分，但我需要兩者來確保我生成的密鑰的順序正確

有任何想法嗎？

消息 104381​​，級別 16，狀態 1，第 18 行

ORDER BY 子句在視圖、CREATE TABLE AS SELECT、INSERT SELECT、SELECT INTO、內聯函式、派生表、子查詢和公用表表達式中無效，除非 TOP 或 FOR XML 是也指定。

CREATE TABLE #demo
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT 
      ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID,
      schemas.name as [schema], 
      tables.name as [table], 
      columns.column_id as [ordinal],
      columns.name as [column],
      types.name as [type]
FROM SYS.COLUMNS
 inner join sys.types
         on  types.system_type_id = columns.system_type_id
 inner join sys.tables
         on  tables.object_id = columns.object_id
 inner join sys.schemas
         on  schemas.schema_id = tables.schema_id
order by schemas.name, 
      tables.name, 
      columns.column_id 

我想這對你有用：

CREATE TABLE #demo
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT 
   ROW_NUMBER() OVER (
       ORDER BY S.[name], T.[name], C.column_id) AS ID,
   S.[name] as [schema], 
   T.[name] as [table], 
   C.[column_id] as [ordinal],
   C.[name] as [column],
   TY.[name] as [type]
FROM sys.columns AS C
JOIN sys.types AS TY
   ON TY.system_type_id = C.system_type_id
JOIN sys.tables AS T
   ON T.[object_id] = C.[object_id]
JOIN sys.schemas AS S
   ON S.[schema_id] = T.[schema_id];

關鍵區別是將排序移動到ROW_NUMBER視窗函式，以確定分配數字的順序。

引用自：https://dba.stackexchange.com/questions/290838