34,594
社区成员
发帖
与我相关
我的任务
分享
select CustomedSer.CustomedSerID,CustomedSer.userid,min(CustomedSercreatetime) as 'CustomedSercreatetime',
min(username) as 'username',min(CustomedSerstatus) as 'CustomedSerstatus',
--GraphicsModel=stuff((select ','+GraphicsModel from GraphicsInfo where CustomedSer.CustomedSerID=GraphicsInfo.CustomedSerID for xml path('')),1,1,''),
min(CustomedSerupdatetime) as 'CustomedSerupdatetime',min(username) as 'username'
from (select * from userinfo as u1 where u1.userapprovalid=72 and u1.usertype='Service' and userid in (select userid from CustomedSer)) as u
left join CustomedSer on CustomedSer.userid=u.userid
left join GraphicsInfo on CustomedSer.CustomedSerID=GraphicsInfo.CustomedSerID
group by CustomedSer.CustomedSerID, CustomedSer.userid,u.userapprovalid,u.usertype
having u.userapprovalid=72 and u.usertype='Service'
order by CustomedSerstatus
这样0s了select CustomedSer.CustomedSerID,CustomedSer.userid,min(CustomedSercreatetime) as 'CustomedSercreatetime',
min(username) as 'username',min(CustomedSerstatus) as 'CustomedSerstatus',
GraphicsModel=stuff((select ','+GraphicsModel from GraphicsInfo where CustomedSer.CustomedSerID=GraphicsInfo.CustomedSerID for xml path('')),1,1,''),
min(CustomedSerupdatetime) as 'CustomedSerupdatetime',min(username) as 'username'
from userinfo as u left join CustomedSer on CustomedSer.userid=u.userid
left join GraphicsInfo on CustomedSer.CustomedSerID=GraphicsInfo.CustomedSerID
group by CustomedSer.CustomedSerID, CustomedSer.userid,u.userapprovalid,u.usertype
having u.userapprovalid=72 and u.usertype='Service'
order by CustomedSerstatus
7s
SELECT CustomedSer.CustomedSerID ,
CustomedSer.userid ,
MIN(CustomedSercreatetime) AS 'CustomedSercreatetime' ,
MIN(username) AS 'username' ,
MIN(CustomedSerstatus) AS 'CustomedSerstatus' ,
GraphicsModel = STUFF(( SELECT ',' + GraphicsModel
FROM GraphicsInfo
WHERE CustomedSer.CustomedSerID = GraphicsInfo.CustomedSerID
FOR
XML PATH('')
), 1, 1, '') ,
MIN(CustomedSerupdatetime) AS 'CustomedSerupdatetime' ,
MIN(username) AS 'username' ,
SUM(num) AS TotalNum
FROM CustomedSer
LEFT JOIN userinfo AS u ON CustomedSer.userid = u.userid
LEFT JOIN GraphicsInfo ON CustomedSer.CustomedSerID = GraphicsInfo.CustomedSerID
WHERE u.userapprovalid = 72
AND u.usertype = 'Service'
GROUP BY CustomedSer.CustomedSerID ,
CustomedSer.userid ,
u.userapprovalid ,
u.usertype
这个15sselect CustomedSer.CustomedSerID,CustomedSer.userid,min(CustomedSercreatetime) as 'CustomedSercreatetime',
min(username) as 'username',min(CustomedSerstatus) as 'CustomedSerstatus',
GraphicsModel=stuff((select ','+GraphicsModel from GraphicsInfo where CustomedSer.CustomedSerID=GraphicsInfo.CustomedSerID for xml path('')),1,1,''),
min(CustomedSerupdatetime) as 'CustomedSerupdatetime',min(username) as 'username',sum(num) as TotalNum
from (select * from CustomedSer where userid in (select userid from userinfo as u1 where u1.userapprovalid=72 and u1.usertype='Service')) as CustomedSer
left join userinfo as u on CustomedSer.userid=u.userid
left join GraphicsInfo on CustomedSer.CustomedSerID=GraphicsInfo.CustomedSerID
group by CustomedSer.CustomedSerID,CustomedSer.userid
执行照样需要15s 没弄过数据优化 不知道有什么工具测试问题在哪里