sqlserver 字符串拼接及拆开联表查询的问题

a.*,e.NoRead,e.AlRead,ToUserName= STUFF(( SELECT ‘,‘ + Name FROM Users d where PATINDEX(‘%,‘ + RTRIM(Account) + ‘,%‘,‘,‘ + a.ToUserID + ‘,‘)>0 ORDER BY PATINDEX(‘%,‘ + RTRIM(Account) + ‘,%‘,‘,‘ + a.ToUserID + ‘,‘) --加上排序不变（如:2,1,3） FOR XML PATH(‘‘)), 1, 1,‘‘),
case a.NoticeTypeID when 2 then ‘调查问卷‘ when 3 then ‘奖品订单‘ else ‘普通通知‘ end as NoticeTypeName,b.AwardOrderTitle,
b.AwardOrderDescription,c.QuestionnaireTitle from Notices a left join AwardOrders b on a.AwardOrderID=b.ID and b.status=1 left join QuestionnairesInfor c on a.QuestionnaireID=c.ID and c.status=1 left join (select NoticeID, COUNT(1)as sumcount,NoRead=sum(case [status] when 1 then 1 else 0 end),
AlRead= sum(case [status] when 2 then 1 else 0 end)
from UserNotices group by NoticeID) e on a.NoticesID=e.NoticeID where a.status=1

 

二、根据字段ToUserID中存储的以逗号隔开的用户Guid类型的ID字符串，将通知发送到用户通知表：

1、需求：将通知分发给各个用户（根据用户分条保存到用户表）

2、数据库设计同一

3、sql发送通知：

<span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> UserNotices(ID,NoticeID,Noticetype,AwardsOrderID,QuestionnaireID,CreatManAccount,CreatManName,CreatDate,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">status</span><span style="color: #ff0000;">]</span><span style="color: #000000;">) 
( </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">NEWID</span>(),NoticesID,NoticeTypeID,AwardOrderID,QuestionnaireID,b.Account,b.Name,<span style="color: #ff00ff;">GETDATE</span>(),<span style="color: #800000; font-weight: bold;">1</span> <br><span style="color: #0000ff;">from</span> Notices a  <span style="color: #808080;">left</span> <span style="color: #808080;">join</span> Users b <span style="color: #0000ff;">on</span>  <span style="color: #ff00ff;">PATINDEX</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">RTRIM</span>(Account) <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,%</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> a.ToUserID <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span>)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">0</span>  <br><span style="color: #0000ff;">where</span> a.NoticesID<span style="color: #808080;">=</span><span style="color: #008000;">@NoticeID</span> )   <span style="color: #008080;">--</span><span style="color: #008080;">@NoticeID为参数通知ID</span>

 

sqlserver 字符串拼接及拆开联表查询的问题

标签：