汇总一个棘手的SQL查询 [英] Putting together a tricky SQL query

问题描述

我正在使用SQL 2008机器，似乎无法使查询正常工作.
我的SQL查询是:

I am working on a SQL 2008 machine and cannot seem to get the query to work.
My SQL query is :

select q.Document DOC from references q, equiprates e where e.MachineID=q.UnitID'   

q.Document取消的行是:

The rows retruned by q.Document is:

5570_RESTAURANT.pdf  
5650_RESTAURANT.pdf  
5110_RESTAURANT.pdf 

但是，我需要表行如下:

However, I need the table rows to be as follows:

餐厅文件

<a href="Javascript:ViewPDFDoc('5570_RESTAURANT.pdf')" class="Link">5570_RESTAURANT.pdf</a>  
<a href="Javascript:ViewPDFDoc('5650_RESTAURANT.pdf')" class="Link">5570_RESTAURANT.pdf</a>  
<a href="Javascript:ViewPDFDoc('5110_RESTAURANT.pdf')" class="Link">5570_RESTAURANT.pdf</a>  

因此，我尝试按以下格式设置我的选择字符串:

So I am trying to format my selecct string as follows:

Select @sSQL = 'select q.Document DOC, ''<a href="Javascript:ViewFile('''''+q.Document+''''''')" class="Link">''+q.Document+''</a>'' ''Restaurant Document'',
from references q, equiprates e 
where e.MachineID=q.UnitID'  

我的错误消息是:

Msg 4104，第16级，状态1，第3行

Msg 4104, Level 16, State 1, Line 3

多部分标识符"q.Document"无法绑定.

The multi-part identifier "q.Document" could not be bound.

有什么办法解决这个问题吗?
我尝试过Google，但没有运气.

Any ideas how to resolve this?
I tried google, but no luck.

推荐答案

您的单引号是错误的(我还建议您改用更现代的INNER JOIN语法).但是，为什么应用程序不能简单地在DOC列周围添加HTML?在服务器上添加所有HTML，并通过网络发送所有这些字节，似乎很浪费时间(显然，要复杂得多).

Your single quotes are just wrong (I also recommend shifting to more modern INNER JOIN syntax). But why can't the application simply add the HTML around the DOC column? Seems wasteful (never mind more complex, obviously) to add all that HTML at the server, and send all those bytes over the wire.

DECLARE @sSQL NVARCHAR(MAX);

SET @sSQL = N'SELECT 
  DOC = q.Document, 
  [Restaurant Document] = ''<a href="Javascript:ViewFile('''''' 
    + q.Document + '''''');" class="Link">'' 
    + q.Document + ''</a>''
  FROM references AS q 
  INNER JOIN equiprates AS e
  ON q.UnitID = e.MachineID';

PRINT @sSQL;

这篇关于汇总一个棘手的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！