万户网络知识库 > 常见问题 > 常用简单SQL
常用简单SQL
更新时间：2019-03-12 09:51:36 访问量：
链表查询：
select *,(select top 1 CategoryName from Whir_U_Content_Category where Whir_U_Content_Category_PID=CategoryId) as CategoryName from Whir_U_Content +条件
select top 1 *,(select top 1 columnurl from Whir_sit_ColumnInfor where columnid=typeid) as columnurl from Whir_U_Content where typeid=1775 and isdeleted=0 order by Sort desc
排除第一条，指定第二条：
select top 1 * from Whir_U_Content where typeID in(12,13,14,15,16)and isShow=1 and whir_U_Content_PID not in
(select top 1 whir_U_Content_PID from Whir_U_Content where typeID in(12,13,14,15,16)and isShow=1 order by sort desc) order by sort desc
查询带图片的头五条
select top 5 * from Whir_U_Content where typeid=25 and isdel=0 and ImageUrl is not null and ImageUrl != '' and replace(ImageUrl,' ','') !='' Order by Createdate desc,sort desc
链表查询：
select top 10 a.*,b.columnalias from whir_u_content as a,whir_sit_columninfor as b where a.typeid='1589' and a.typeid=b.columnid and a.Isdeleted=0 order by a.teDate desc
select a.*,b.columnurl from Whir_U_Product as a join whir_sit_ColumnInfor as b on a.typeid=b.columnid where a.isdeleted=0 and a.istop=1 and a.typeid in (1593,1594) order by a.createdate desc
查询二级类别的名称，三级类别的连接
select *,(select top 1 Whir_U_Product_Category_PID from Whir_U_Product_Category as a where parentid=Whir_U_Product_Category.Whir_U_Product_Category_PID order by a.sort desc ) as firstid,(select top 1 CategoryName from Whir_U_Product_Category as a where parentid=Whir_U_Product_Category.Whir_U_Product_Category_PID order by a.sort desc ) as SubCategoryName from Whir_U_Product_Category
where isDel=0 and typeID=26 and parentID=0 order by sort desc,createdate desc
多个参数可复制：
,(select top 1 Whir_U_Product_Category_PID from Whir_U_Product_Category as a where parentid=Whir_U_Product_Category.Whir_U_Product_Category_PID order by a.sort desc ) as firstid
把子类别的信息放到父级类别里面：
select * from Whir_U_Content where CategoryId in (select Whir_U_Content_Category_pid from Whir_U_Content_Category where parentid=1)
首页简单信息，带一级类别ID，父级类别ID
select top 3 Whir_U_Content_Category.parentid as parentsid,Whir_U_Content.* from Whir_U_Content left join Whir_U_Content_Category
on Whir_U_Content.categoryid=Whir_U_Content_Category.Whir_U_Content_Category_PID
where Whir_U_Content.typeid=48 and Whir_U_Content.isDel=0
order by Whir_U_Content.createdate desc
实现一级类别陈列所有包含的子集的产品，子集单独筛选子集所属产品
--------------------------------------------------
左侧菜单(传ID)
<wtl:list sql="select * from Whir_U_Product_Category where typeID=17 and isDel=0 and parentID=0 order by sort desc,createdate desc">
<li id="menu{$Whir_U_Product_Category_PID}">
<a href="list_6.aspx?PID={$Whir_U_Product_Category_PID}" title="{$CategoryName}">{$CategoryName,14}</a>
<dl class="submenu" style=" display:none;">
<wtl:list sql="select * from Whir_U_Product_Category where typeID=17 and isDel=0 and parentID={0} order by sort desc,createdate desc" sql0="{$Whir_U_Product_Category_PID}">
<dt id="submenu{$Whir_U_Product_Category_PID}"><a href="list_6.aspx?PID={$parentID}&CategoryID={$Whir_U_Product_Category_PID}">{$CategoryName}</a></dt>
</wtl:list>
</dl>
</li>
</wtl:list>
产品列表（获取ID）
<wtl:if testtype="{@CategoryID}" TestOperate="Empty">
<successTemplate>
<wtl:list id="product_list" needpage="true" where="CategoryID in (select Whir_U_Product_Category_PID from Whir_U_Product_Category where parentID={0} or Whir_U_Product_Category_PID = {0} and isDel=0) and isDel=0" order="sort desc,createdate desc" where0="{@PID,false,0}">
<li>
<div class="pic"><a href="{$pageurl}"><img src="{$uploadpath}{$imageurl}" /></a></div>
<h1><a href="{$pageurl}">{$ProductName}</a></h1>
</li>
</wtl:list>
</successTemplate>
<failureTemplate>
<wtl:list id="product_list" needpage="true" where="CategoryID={0} and isDel=0" order="sort desc,createdate desc" where0="{@CategoryID,false,0}">
<li>
<div class="pic"><a href="{$pageurl}"><img src="{$uploadpath}{$imageurl}" /></a></div>
<h1><a href="{$pageurl}">{$ProductName}</a></h1>
</li>
</wtl:list>
</wtl:if>
方案二：
<wtl:list sql="select top 1 * from Whir_U_Product_Category where typeID=60 and parentid=0 and isDel=0 order by sort desc,createdate desc">
<wtl:list id="ProList" sql="select * from Whir_U_Product where typeID=43 and isDel=0 and CategoryID in (select Whir_U_Product_Category_PID from Whir_U_Product_Category where (Whir_U_Product_Category_PID ={0} or ParentPath like '%{0}%') and isDel=0) order by sort desc,createdate desc" sql0="{$Whir_U_Product_Category_PID}">
<li>
<table border="0" cellspacing="0" cellpadding="0" height="161" align="center" width="223">
<tr>
<td align="center" valign="middle"><A href="{$PageUrl}&CategoryID={$CategoryID}"><img src="{$uploadpath}{$ImageUrl}" alt="{$ProductName}"></A></td>
</tr>
</table>
<h1><A href="{$PageUrl}&CategoryID={$CategoryID}">{$ProductName}</A></h1>
<div class="info">{$ProductInfo,100}</div>
</li>
</wtl:list>
</wtl:list>
整站查询：
sql="select *,(select top 1 path from Whir_dev_column where columnid=typeid)+'/info_'+typeid+'.aspx?itemid='+cast (whir_U_Content_PID as nvarchar(50)) as path from (select Whir_U_Content_pid,typeid,title,createdate,categoryid,isdel,imageurl,Hits from Whir_U_Content union all select Whir_U_product_pid,typeid,productname,createdate,categoryid,isdel,imageurl,Hits from Whir_U_product) as tablea where typeid not in(65,15) and isdel=0 and title like '%{0}%'" sql0="{@key,true,0}"
组合表查询
select * from (select *,(select top 1 CategoryName from Whir_U_Content_Category where isDel=0 and CategoryID=Whir_U_Content_Category_PID ) as CName
from Whir_U_Content where typeid not in (45,46,47,48,49,78) and isDel=0 )as tb1 where title like '%新天地%' or CName like '%新天地%' order by sort desc,createdate desc
sql替换数据库字段中的字符
替换Whir_U_content 表中字段content, 内容“wsbz201408047517”替换为“wsbzyyj201408047517”
update Whir_U_content set content=REPLACE(CONVERT(nvarchar(MAX), content), 'wsbz201408047517','wsbzyyj201408047517')