TSQL-從記錄集動態生成查詢

樣本資料：

DECLARE @Tbl_List TABLE
    (
        [PSchemaName] sysname
      , [PTableName]  sysname
      , [PColumnName] sysname
      , [FSchemaName] sysname
      , [FTableName]  sysname
      , [FColumnName] sysname
      , [ColumnOrder] TINYINT
    ) ;

INSERT INTO @Tbl_List
VALUES
    ( 'emp', 'emphdr1', 'id', 'emp', 'empdtl1', 'hdrid', 1 )
  , ( 'emp', 'emphdr2', 'id', 'emp', 'empdtl2', 'hdrid', 1 )
  , ( 'emp', 'emphdr2', 'key', 'emp', 'empdtl2', 'hdrkey', 2 )
  , ( 'emp', 'emphdr3', 'id', 'emp', 'empdtl3', 'hdrid', 1 )
  , ( 'emp', 'emphdr3', 'key1', 'emp', 'empdtl3', 'hdrkey1', 2 )
  , ( 'emp', 'emphdr3', 'key2', 'emp', 'empdtl3', 'hdrkey2', 3 )
  , ( 'emp', 'emphdr3', 'id', 'emp', 'empdtl4', 'hdrid', 1 )
  , ( 'emp', 'emphdr3', 'key1', 'emp', 'empdtl4', 'hdrkey1', 2 ) ;

PSchemaName PTableName  PColumnName FSchemaName FTableName  FColumnName ColumnOrder
emp         emphdr1     id          emp         empdtl1     hdrid       1
emp         emphdr2     id          emp         empdtl2     hdrid       1
emp         emphdr2     key         emp         empdtl2     hdrkey      2
emp         emphdr3     id          emp         empdtl3     hdrid       1
emp         emphdr3     key1        emp         empdtl3     hdrkey1     2
emp         emphdr3     key2        emp         empdtl3     hdrkey2     3
emp         emphdr3     id          emp         empdtl4     hdrid       1
emp         emphdr3     key1        emp         empdtl4     hdrkey1     2

目標：

動態創建/輸出一個 SELECT 陳述句 - 按照 ColumnOrder 中指定的順序在 (F/S)ColumnName 上將 FSchema/FTable 與 PSchema/PTable JOIN - 在新列中。

查詢將衍生為.. "SELECT [F].[{FColumnName}], [P].[{PColumnName}] FROM [{FSchemaName}].[{FTableName}] AS [F] JOIN [{PSchemaName}] .[{PTableName}] AS [P] ON [P].[{PColumnName}] = [F].[{FColumnName}] ;" （同樣，ON 子句是按照 [ColumnOrder] 欄位中指定的順序派生的）

預期輸出：

PSchemaName PTableName  FSchemaName FTableName  CMD
emp         emphdr1     emp         empdtl1     SELECT [F].[hdrid], [P].[id] FROM [emp].[empdtl1] AS [F] JOIN [emp].[emphdr1] AS [P] ON [P].[id] = [F].[hdrid] ;
emp         emphdr2     emp         empdtl2     SELECT [F].[hdrid], [F].[hdrkey], [P].[id], [P].[key] FROM [emp].[empdtl2] AS [F] JOIN [emp].[emphdr2] AS [P] ON [P].[id] = [F].[hdrid] AND [P].[key] = [F].[hdrkey] ;
emp         emphdr3     emp         empdtl3     SELECT [F].[hdrid], [F].[hdrkey1], [F].[hdrkey2], [P].[id], [P].[key1], [P].[key2] FROM [emp].[empdtl3] AS [F] JOIN [emp].[emphdr3] AS [P] ON [P].[id] = [F].[hdrid] AND [P].[key1] = [F].[hdrkey1] AND [P].[key2] = [F].[hdrkey2] ;
emp         emphdr3     emp         empdtl4     SELECT [F].[hdrid], [F].[hdrkey1], [P].[id], [P].[key1] FROM [emp].[empdtl3] AS [F] JOIN [emp].[emphdr4] AS [P] ON [P].[id] = [F].[hdrid] AND [P].[key1] = [F].[hdrkey1] ;

我的嘗試：

我還在想辦法。連接單個列可以通過 FOR XML 實作，不確定我將如何在兩列之間連接“=”并為與多列的連接添加“AND”。

uj5u.com熱心網友回復：

使用Group By和String_Agg函式，您可以生成所需的輸出。

SELECT PSchemaName,PTableName,FSchemaName,FTableName,
      CONCAT('SELECT ',fselectcol,',',pselectcol,' FROM ',FTableName ,' f JOIN ',PTableName,' p ON ',joincol) AS CMD
FROM
(SELECT
       MAX(PSchemaName) AS PSchemaName,
       MAX(PTableName) AS PTableName,
       MAX(FSchemaName) AS FSchemaName,
       FTableName,
       STRING_AGG('p.'   PColumnName,',') pselectcol,
       STRING_AGG('f.'   FColumnName,',') fselectcol,
       STRING_AGG('p.'   PColumnName   ' = f.'   FColumnName,' AND ') joincol
FROM @Tbl_List
GROUP BY FTableName) t

編輯： SQL-SERVER 2016不支持String_Agg，所以使用XML PATH

SELECT  
       MAX(PSchemaName) AS PSchemaName,
       MAX(PTableName) AS PTableName,
       MAX(FSchemaName) AS FSchemaName,
       FTableName,
       CONCAT('SELECT ',MAX(fselectcol),',',MAX(pselectcol),' FROM ',FTableName ,' f JOIN ',MAX(PTableName),' p ON ', SUBSTRING(MAX(joincol),0,LEN(MAX(joincol)) - 3)) AS CMD
FROM

(SELECT t1.*,
  STUFF(
    (SELECT ',p.'   PColumnName
     FROM @Tbl_List AS t2
     WHERE t2.FTableName = t1.FTableName
     FOR XML PATH('')), 1, 1, NULL) AS pselectcol,
   STUFF(
    (SELECT ',f.'   FColumnName
     FROM @Tbl_List AS t2
     WHERE t2.FTableName = t1.FTableName
     FOR XML PATH('')), 1, 1, NULL) AS fselectcol,
   STUFF(
    (SELECT 'p.'   PColumnName   ' = f.'   FColumnName,' AND '
     FROM @Tbl_List AS t2
     WHERE t2.FTableName = t1.FTableName
     FOR XML PATH('')), 1, 0, NULL) AS joincol
FROM @Tbl_List AS t1) T
GROUP BY FTableName

db<>fiddle 中的演示- sql server 2016

db<>fiddle 中的演示

uj5u.com熱心網友回復：

混合使用 Cross Apply 和 For Xml 來生成查詢。

SELECT 
  [PSchemaName], [PTableName], [FSchemaName], [FTableName]
, [Cmd] = CONCAT('SELECT ', [Fields], char(10), 
                 'FROM ', [From], char(10), 
                 'JOIN ', [Join], char(10), [On])
FROM
(
  SELECT
     [FSchemaName], [FTableName], [PSchemaName], [PTableName]
   , [From] = CONCAT(QUOTENAME([FSchemaName]), '.', QUOTENAME([FTableName]), ' AS [F] ') 
   , [Join] = CONCAT(QUOTENAME([PSchemaName]), '.', QUOTENAME([PTableName]), ' AS [P] ') 
  FROM @Tbl_List
  GROUP BY 
     [FSchemaName], [FTableName], [PSchemaName], [PTableName]
) t
CROSS APPLY (
  SELECT 
    [Fields] = STUFF(
    (SELECT ', [F].'  QUOTENAME([FColumnName])
            ', [P].'  QUOTENAME([PColumnName])
     FROM @Tbl_List t2
     WHERE t2.FTableName = t.FTableName
     FOR XML PATH('')), 1, 2, NULL)
  , [On] = '  ON'   STUFF(
    (SELECT  ' AND [P].'  QUOTENAME([PColumnName])
          ' = [F].'  QUOTENAME([FColumnName])  char(10)
     FROM @Tbl_List t2
     WHERE t2.FTableName = t.FTableName
     FOR XML PATH('')), 1, 4, NULL)
  from @Tbl_List t2
  where t2.FSchemaName = t.FSchemaName
    and t2.FTableName = t.FTableName
) ca

關于db<>fiddle 的演示在這里

標籤：sql sql-server 查询语句 sql-server-2016

上一篇：SPLIT_PART帶有2個帶OR條件的分隔符

下一篇：如何根據動態標準計算運行最小值？