这是来新公司写的第一篇文章，使用LEFT JOIN ON LIKE处理一下这种问题：
SQL视图代码如下：
CREATE View [dbo].[VI_SearchCN] AS--搜索产品的文件(select upload.FileId as ID,upload.Name as Title,upload.Name as FileTxt,con.TypeId as TypeId,upload.Path as DownLoadPath, '文件' as SearchType,'0' as CategoryId,'0' as ParentCategoryId,upload.Sort,upload.CreateDate from Whir_Ext_Upload uploadleft joinWhir_U_Content conon ('*'+con.FileDownload+'*') like ('%*'+upload.Path+'*%')where con.FileDownload!='' and con.IsDel=0 and con.TypeId=2)UNION ALL--搜索产品(select con.Whir_U_Content_PId as ID,con.Title, '' as FileTxt, con.TypeId,'' as DownLoadPath,'产品' as SearchType,CategoryId,cat.ParentId as ParentCategoryId,con.Sort,con.CreateDate from Whir_U_Content ConLEFT JOINWhir_U_Content_Category CatON Cat.Whir_U_Content_Category_PId=con.CategoryIdwhere con.TypeId=2 and con.IsDel=0)UNION ALL--新闻select Whir_U_Content_PId as ID,Title,'' as FileTxt,TypeId,'' as DownLoadPath,'新闻' as SearchType,'0' as CategoryId,'0' as ParentCategoryId,Sort,CreateDate from Whir_U_Content where TypeId IN (13,14,15) and IsDel=0UNION ALL(--文件下载COA--select Whir_U_Content_PId as ID,Title,TypeId,'' as DownLoadPath,'COA' as SearchType,Sort,CreateDate from Whir_U_Content where TypeId=35 and IsDel=0select upload.FileId as ID,con.Title,upload.Name as FileTxt,con.TypeId as TypeId,upload.Path as DownLoadPath, 'COA' as SearchType,'0' as CategoryId,'0' as ParentCategoryId,con.Sort,con.CreateDate from Whir_Ext_Upload uploadleft joinWhir_U_Content conon ('*'+con.FileDownload+'*') like ('%*'+upload.Path+'*%')where con.FileDownload!='' and con.IsDel=0 and con.TypeId=35)GO
前端代码如下：
<wtl:list ID="mylist1" needpage="true" sql="select ID,Title,Filetxt,TypeId,DownLoadPath,SearchType,CategoryId,ParentCategoryId,CreateDate from VI_SearchCN where Title LIKE '%{0}%' order by Sort desc,CreateDate desc" sql0="{@key,false}"><wtl:if testtype="{$SearchType}" TestOperate="IN" testvalue="文件,COA"><successTemplate><dd><a href="{$uploadpath}{$DownLoadPath,parent,1}" download="{$Title,parent,1}"><span title="{$Title,70,parent,1}">【{$SearchType,parent,1}】{$Title,70,parent,1}</span><time>{$CreateDate,yyyy.MM.dd,parent,1}</time></a></dd></successTemplate></wtl:if><wtl:if testtype="{$SearchType}" TestOperate="Equals" testvalue="新闻"><successTemplate><dd><a href="{$syspath}news/info_{$TypeId,parent,1}.aspx?itemid={$ID,parent,1}"><span title="{$Title,70,parent,1}">【{$SearchType,parent,1}】{$Title,70,parent,1}</span><time>{$CreateDate,yyyy.MM.dd,parent,1}</time></a></dd></successTemplate></wtl:if><wtl:if testtype="{$SearchType}" TestOperate="Equals" testvalue="产品"><successTemplate><dd><a href="{$syspath}cpzx/info_{$TypeId,parent,1}.aspx?itemid={$ID,parent,1}&lcid={$CategoryId,parent,1}&bcid={$ParentCategoryId,parent,1}"><span title="{$Title,70,parent,1}">【{$SearchType,parent,1}】{$Title,70,parent,1}</span><time>{$CreateDate,yyyy.MM.dd,parent,1}</time></a></dd></successTemplate></wtl:if></wtl:list>
