sql-server - 根据存储在表中的表和列信息动态构建 SQL 查询

Question

有一个包含列和表名列表的表。

我必须编写一个查询以从 SELECT 查询中列出。

例子:我从 JSON 中提取的信息。现在我必须构建一个 SQL 语句。

ROW_ID  predicateName   EntityName
1       AGE            tbl_PR_AGE_VALIDATION_RULE
2       SALARY         tbl_PR_AGE_VALIDATION_RULE
3       GENDER         tbl_PR_AGE_VALIDATION_RULE
4       DATEOBIRTH     tbl_PR_AGE_VALIDATION_RULE
5       CITY           tbl_PR_AGE_VALIDATION_RULE
6       TEST1          tbl_PR_PAGE_VALIDATION_RULE
7       TEST2          tbl_PR_CITY_VALIDATION

年龄检查表的所有列表，如果它是哪个特定表的列，并为其创建一个别名。

tbl_PR_AGE_VALIDATION_RULE A

工资检查表的所有列表，如果它是哪个特定表的列，并为其创建一个 ALIAS B。

tbl_PR_PAGE_VALIDATION_RULE A

最终查询应该是

SELECT A.AGE,B.SALARY FROM tbl_PR_AGE_VALIDATION_RULE A LEFT JOIN tbl_PR_PAGE_VALIDATION_RULE B
ON A.ENTITY_ID =B.ENTITY_ID 

ENTITY_ID 所有表通用。

下面是我试过的。

DECLARE @TBL_PRD_ENT table (ROW_ID int IDENTITY(1, 1),
                            predicateName varchar(255),
                            EntityName varchar(255));
INSERT INTO @TBL_PRD_ENT
SELECT predicateNameEn,
       EntityName
FROM ##DTS_Parser_predicates P
     LEFT JOIN ##DTS_Parser_Datadefinition_Predicates DP ON P.predicateNameEn = DP.name
     INNER JOIN ##DTS_Parser_Datadefinition PD ON PD.ID = DP.ID;

SELECT *
FROM @TBL_PRD_ENT;
DECLARE @CT int,
        @ICT int,
        @I int = 1,
        @J int = 1;
SET @CT = (SELECT COUNT(1)FROM @TBL_PRD_ENT);
SET @ICT = (SELECT COUNT(DISTINCT EntityName)FROM @TBL_PRD_ENT);
SELECT @CT;
SELECT @ICT;
DECLARE @FINAL_TBL table (COL varchar(255),
                          TBL varchar(255));
WHILE @I <= @CT
BEGIN
    WHILE @J <= @ICT
    BEGIN
        INSERT INTO @FINAL_TBL
        SELECT TABLE_NAME + '.' + COLUMN_NAME,
               TABLE_NAME + '  ' + TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME = (SELECT predicateName FROM @TBL_PRD_ENT WHERE ROW_ID = @I)
          AND TABLE_NAME = (SELECT EntityName FROM @TBL_PRD_ENT WHERE ROW_ID = @I);
        SET @J = @J + 1;
    END;
    SET @I = @I + 1;
    SET @J = 1;
END;
DECLARE @SQL_COL nvarchar(MAX);
DECLARE @SQL_TBL nvarchar(MAX);

SELECT @SQL_COL = N'SELECT ' + STRING_AGG(COL, ',') + ' FROM '
FROM @FINAL_TBL;
WITH
CTE AS
    (SELECT DISTINCT
            TBL
     FROM @FINAL_TBL)
SELECT @SQL_TBL = STRING_AGG(TBL, ' LEFT JOIN ')
FROM CTE;




SELECT @SQL_COL,
       @SQL_TBL;

输出

SELECT TBL_PR_AGE_VALIDATION_RULE.AGE,TBL_PR_AGE_VALIDATION_RULE.SALARY,TBL_PR_AGE_VALIDATION_RULE.GENDER,TBL_PR_AGE_VALIDATION_RULE.DATEOBIRTH,TBL_PR_AGE_VALIDATION_RULE.CITY,tbl_PR_PAGE_VALIDATION_RULE.TEST1,tbl_PR_CITY_VALIDATION.TEST2 FROM 

TBL_PR_AGE_VALIDATION_RULE  TBL_PR_AGE_VALIDATION_RULE
tbl_PR_PAGE_VALIDATION_RULE tbl_PR_PAGE_VALIDATION_RULE
tbl_PR_CITY_VALIDATION      tbl_PR_CITY_VALIDATION

预期输出:

SELECT TBL_PR_AGE_VALIDATION_RULE.AGE,
       TBL_PR_AGE_VALIDATION_RULE.SALARY,
       TBL_PR_AGE_VALIDATION_RULE.GENDER,
       TBL_PR_AGE_VALIDATION_RULE.DATEOBIRTH,
       TBL_PR_AGE_VALIDATION_RULE.CITY,
       tbl_PR_PAGE_VALIDATION_RULE.TEST1,
       tbl_PR_CITY_VALIDATION.TEST2
FROM TBL_PR_AGE_VALIDATION_RULE TBL_PR_AGE_VALIDATION_RULE
     LEFT JOIN tbl_PR_PAGE_VALIDATION_RULE tbl_PR_PAGE_VALIDATION_RULE ON TBL_PR_AGE_VALIDATION_RULE.ENTITY_ID = tbl_PR_PAGE_VALIDATION_RULE.ENTITY_ID
     LEFT JOIN tbl_PR_CITY_VALIDATION tbl_PR_CITY_VALIDATION ON tbl_PR_PAGE_VALIDATION_RULE.ENTITY_ID = tbl_PR_CITY_VALIDATION.ENTITY_ID;

我无法在我的输出中注入(inject) LEFT JOIN ON ALIAS.ENTITY_ID=ALIAS.ENTITY_ID。请帮助。

Answer 1

试试这个，

DECLARE @TEMP_TABLE TABLE
(
    table_ID INT,
    predicateName VARCHAR(250),
    EntityName VARCHAR(250)
)

INSERT INTO @TEMP_TABLE
(
    table_ID,predicateName,EntityName
)
SELECT 1,'AGE','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'SALARY','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'GENDER','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'DATEOBIRTH','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 1,'CITY','tbl_PR_AGE_VALIDATION_RULE' UNION ALL
SELECT 2,'TEST1','tbl_PR_PAGE_VALIDATION_RULE' UNION ALL
SELECT 3,'TEST2','tbl_PR_CITY_VALIDATION'

DECLARE @VC_COLUMN_LIST VARCHAR(2000)='',@VC_TABLE_LIST VARCHAR(2000)=''

SELECT  @VC_COLUMN_LIST+=EntityName+'.'+predicateName+','+CHAR(10)
FROM @TEMP_TABLE

SELECT @VC_COLUMN_LIST=LEFT(@VC_COLUMN_LIST,LEN(@VC_COLUMN_LIST)-2)

;WITH CTE_TEMP AS
(
        SELECT DISTINCT table_ID, EntityName
        FROM @TEMP_TABLE
)
SELECT @VC_TABLE_LIST+=IIF(B.table_ID IS NULL,'','LEFT JOIN ')+A.EntityName+IIF(B.table_ID IS NULL,'',' ON '+A.EntityName+'.ENTITY_ID = '+B.EntityName+'.ENTITY_ID')+CHAR(10) 
FROM CTE_TEMP A
LEFT JOIN CTE_TEMP B ON A.table_ID=B.table_ID+1 

PRINT
(
    'SELECT '+@VC_COLUMN_LIST+CHAR(10)+
    'FROM '+@VC_TABLE_LIST
)