首页 \ 问答 \ 生成动态查询(Generate dynamic query)

生成动态查询(Generate dynamic query)

 我想动态生成一个查询。 我写了一些条件; 
 

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

DECLARE @MyTable TABLE(
    FormTypeID int NOT NULL,
    TableName NVARCHAR(100),
    pk NVARCHAR(50));

INSERT INTO @MyTable( FormTypeID, TableName, pk )
    VALUES  ( 0, N'Table1', 'ParentID'),
            ( 5, N'Table2', 'ID'),
            ( 5, N'Table3', 'ParentID'),
            ( 5, N'Table4','ParentID'),
            ( 3, N'Table5','ParentID')  

DECLARE @SQLJoin  varchar(8000) = 'LEFT JOIN (SELECT t.* FROM ParentTable t ' + @NewLineChar,
        @pk nvarchar(100);


SELECT @SQLJoin += ' INNER JOIN ' + TableName +' ON ' + TableName + '.' + pk + ' = t.ID' + @NewLineChar FROM @MyTable   
    WHERE FormTypeID IN (0,5)

PRINT @SQLJoin + ')';

 

 这是结果： 
 

LEFT JOIN (SELECT t.* FROM ParentTable t 
 INNER JOIN Table1 ON Table1.ParentID = t.ID
 INNER JOIN Table2 ON Table2.ID = t.ID
 INNER JOIN Table3 ON Table3.ParentID = t.ID
 INNER JOIN Table4 ON Table4.ParentID = t.ID
)

 

 我希望它看起来像嵌套连接取决于FormTypeID： 
 

LEFT JOIN (SELECT t.* FROM ParentTable t 
 INNER JOIN Table1 ON Table1.ParentID = t.ID)
LEFT JOIN (SELECT t.* FROM ParentTable t 
 INNER JOIN Table2 ON Table2.ID = t.ID
 INNER JOIN Table3 ON Table3.ParentID = t.ID
 INNER JOIN Table4 ON Table4.ParentID = t.ID
)


I want to generate a query dynamically. I wrote it with some conditions;
 

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

DECLARE @MyTable TABLE(
    FormTypeID int NOT NULL,
    TableName NVARCHAR(100),
    pk NVARCHAR(50));

INSERT INTO @MyTable( FormTypeID, TableName, pk )
    VALUES  ( 0, N'Table1', 'ParentID'),
            ( 5, N'Table2', 'ID'),
            ( 5, N'Table3', 'ParentID'),
            ( 5, N'Table4','ParentID'),
            ( 3, N'Table5','ParentID')  

DECLARE @SQLJoin  varchar(8000) = 'LEFT JOIN (SELECT t.* FROM ParentTable t ' + @NewLineChar,
        @pk nvarchar(100);


SELECT @SQLJoin += ' INNER JOIN ' + TableName +' ON ' + TableName + '.' + pk + ' = t.ID' + @NewLineChar FROM @MyTable   
    WHERE FormTypeID IN (0,5)

PRINT @SQLJoin + ')';

 

This is result:
 

LEFT JOIN (SELECT t.* FROM ParentTable t 
 INNER JOIN Table1 ON Table1.ParentID = t.ID
 INNER JOIN Table2 ON Table2.ID = t.ID
 INNER JOIN Table3 ON Table3.ParentID = t.ID
 INNER JOIN Table4 ON Table4.ParentID = t.ID
)

 

I want it to look like this with nested joins depending on FormTypeID:
 

LEFT JOIN (SELECT t.* FROM ParentTable t 
 INNER JOIN Table1 ON Table1.ParentID = t.ID)
LEFT JOIN (SELECT t.* FROM ParentTable t 
 INNER JOIN Table2 ON Table2.ID = t.ID
 INNER JOIN Table3 ON Table3.ParentID = t.ID
 INNER JOIN Table4 ON Table4.ParentID = t.ID
)


原文：https://stackoverflow.com/questions/33167978
更新时间：2023-05-14 12:05

最满意答案

 A.nnotate.com使用xpdf以给定的缩放级别将PDF页面的服务器端转换为PNG图像 - 这些是在浏览器中显示的内容。 
 

 文本突出显示是通过从PDF中提取文本位置，然后在页面图像顶部添加透明覆盖图，并在文字顶部放置绝对定位的html DIVS来完成的。 然后，注释使用ajax gui将注释附加到突出显示的文本。 
 

 其他格式（MS Word，PPT等）首先使用openoffice转换为PDF，然后转换为PDF格式的图像和文本叠加。 
 

 我认为其他HTML文档网站的做法类似于将PDF呈现为HTML（即页面图像+文字叠加作为透明div） - 另一种技巧是将PDF嵌入字体转换为HTML5 CSS字体，并使用绝对定位的文本div（并提取和定位图像）。 

A.nnotate.com does server-side conversion of PDF pages into PNG images at a given zoom level using xpdf - these are what get displayed in the browser.
 

The text highlighting is done by extracting the text positions from the PDF, then adding a transparent overlay on top of the page images with absolutely positioned html DIVS on top of the words. Annotations then use an ajax gui to attach notes to highlighted text.
 

Other formats (MS Word, PPT etc) are first converted to PDF using openoffice, then to images and text overlays as for PDFs.
 

I think the other HTML document sites do something similar for rendering PDFs as HTML (i.e. page images + word overlay as transparent divs) - an alternative trick is convert the PDF embedded fonts to HTML5 CSS fonts, and use absolutely positioned divs for the text (& extract and position the images too).

相关问答

更多