汇总一个棘手的SQL查询

本文介绍了汇总一个棘手的SQL查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用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;