高手!!1,判断过程名,好风格 2,ISNULL替换函数的使用 3,set nocount off 过程不显示 4,程序风格不象初手写的
SQL语句非常流畅简洁,不过我觉得有两句话修改一下写法看起来会更清晰更规范:,rating = (select isNull(sum(rating),0) from critiques where workId=b.WorkId) 改成: ,(select isNull(sum(rating),0) from critiques where workId=b.WorkId)AS rating 同理,workNum = (select isNull(count(workId),0) from works where userId=a.UserId) 改成 (select isNull(count(workId),0) from works where userId=a.UserId) AS workNum
这个sp写得比较清晰,比较规范。 肯定不是新手所为,作者应该至少有1年的功底。 如果非要找毛病的话,那幺要把: if exists (select * from sysobjects where id = object_id('upWorksGetList')) drop procedure upWorksGetList 这句改成 if exists (select * from sysobjects where id = object_id('upWorksGetList') and xtype = 'P') drop procedure upWorksGetList但是因为这个sp的商务逻辑实在太简单,因此也无法看出作者是否高手。
程序写的很规范! 不过平时语句都是一气下来,也没有机会整的这么工整哦!除非是应聘的时候! set nocount off 是显示结果 嘿嘿!是有一定熟练程度的!where a.UserId = isNull(@UserId,a.UserId) and b.albumId = isNull(@AlbumId,b.albumId) 这个地方处理不错!不过COUNT(),SUM()还会返回NULL?
查询语句可以这样改 Select (b.workId) ,(a.userId ) ,(a.lastName) ,(a.firstName) ,C.workNum ,(b.albumId) ,(b.[fileName]) ,(b.title) ,ISNULL(B.rating,0) ,(b.res)from Users a INNER JOIN (select userId, count(workId) AS workNum from works GROUP BY userId) C on a.UserId = C.UserId Left OUTER Join (select A.UserId,A.albumId,A.[fileName],A.b.title,A.res,A.workId,sum(B.rating) AS rating from Works A INNER JOIN critiques B ON A.WorkID=B.WorkID GROUP BY A.UserId,A.albumId,A.[fileName],A.b.title,A.res,A.workId) B on a.UserId = b.UserId AND b.albumId = isNull(@AlbumId,b.albumId) where a.UserId = isNull(@UserId,a.UserId)
你可以把查询的语句这样改一下 Select b.workId,a.userId ,a.lastName,a.firstName,C.workNum,b.albumId ,b.[fileName],b.title,ISNULL(B.rating,0) AS rating ,b.resfrom Users a INNER JOIN (select userId, count(workId) AS workNum from works GROUP BY userId) C on a.UserId = C.UserId Left OUTER Join (select A.UserId,A.albumId,A.[fileName],A.b.title,A.res,A.workId,sum(B.rating) AS rating from Works A INNER JOIN critiques B ON A.WorkID=B.WorkID GROUP BY A.UserId,A.albumId,A.[fileName],A.b.title,A.res,A.workId) B on a.UserId = b.UserId AND b.albumId = isNull(@AlbumId,b.albumId)where a.UserId = isNull(@UserId,a.UserId)
他在前面求和的语句中加入的查询的条件,而他的条件是关系到下面的表的条件,这样就会造成SQL Server在做查询计划的时候出现循环的情况,就是说每当下面的语句查询出一条记录时,就要相应的对works 和 critiques 表进行一次统计,想一想,这样做的开销有多大?上面我写的语句还是罗索 现在更正Select b.workId,a.userId ,a.lastName,a.firstName, ISNULL(B.workNum,0) AS workNum, b.albumId ,b.[fileName],b.title, ISNULL(B.rating,0) AS rating ,b.resfrom Users a Left OUTER Join (select A.UserId,A.albumId,A.[fileName], A.b.title,A.res,A.workId, sum(ISNULL(B.rating,0)) AS rating , count(workId) AS workNum from Works A LEFT OUTER JOIN critiques B ON A.WorkID=B.WorkID GROUP BY A.UserId,A.albumId,A.[fileName], A.b.title,A.res,A.workId) B on a.UserId = b.UserId AND b.albumId = isNull(@AlbumId,b.albumId)
另外,如果你在Works 表的USERID 和 critiques 表的workId 有索引 这样写实最快的 Select b.workId,a.userId ,a.lastName,a.firstName, ISNULL(C.workNum,0) AS workNum, b.albumId ,b.[fileName],b.title, ISNULL(C.rating,0) AS rating ,b.resfrom Users a Left OUTER Join Works B ON A.USERID=B.USERID AND b.albumId = isNull(@AlbumId,b.albumId) LEFT OUTER JOIN (select A.UserId, sum(ISNULL(B.rating,0)) AS rating , count(workId) AS workNum from Works A LEFT OUTER JOIN critiques B ON A.WorkID=B.WorkID GROUP BY A.UserId,B.workId) C on a.UserId = C.UserId where a.UserId = isNull(@UserId,a.UserId)
2,ISNULL替换函数的使用
3,set nocount off 过程不显示
4,程序风格不象初手写的
改成:
,(select isNull(sum(rating),0) from critiques where workId=b.WorkId)AS rating 同理,workNum = (select isNull(count(workId),0) from works where userId=a.UserId)
改成
(select isNull(count(workId),0) from works where userId=a.UserId)
AS workNum
肯定不是新手所为,作者应该至少有1年的功底。
如果非要找毛病的话,那幺要把:
if exists (select * from sysobjects where id = object_id('upWorksGetList'))
drop procedure upWorksGetList
这句改成
if exists (select * from sysobjects where id = object_id('upWorksGetList') and xtype = 'P')
drop procedure upWorksGetList但是因为这个sp的商务逻辑实在太简单,因此也无法看出作者是否高手。
不过平时语句都是一气下来,也没有机会整的这么工整哦!除非是应聘的时候!
set nocount off
是显示结果
嘿嘿!是有一定熟练程度的!where a.UserId = isNull(@UserId,a.UserId) and
b.albumId = isNull(@AlbumId,b.albumId)
这个地方处理不错!不过COUNT(),SUM()还会返回NULL?
Select
(b.workId)
,(a.userId )
,(a.lastName)
,(a.firstName)
,C.workNum
,(b.albumId)
,(b.[fileName])
,(b.title)
,ISNULL(B.rating,0)
,(b.res)from Users a INNER JOIN
(select userId, count(workId) AS workNum from works GROUP BY userId) C
on a.UserId = C.UserId
Left OUTER Join
(select A.UserId,A.albumId,A.[fileName],A.b.title,A.res,A.workId,sum(B.rating) AS rating
from Works A INNER JOIN critiques B ON A.WorkID=B.WorkID
GROUP BY A.UserId,A.albumId,A.[fileName],A.b.title,A.res,A.workId) B
on a.UserId = b.UserId AND b.albumId = isNull(@AlbumId,b.albumId)
where a.UserId = isNull(@UserId,a.UserId)
Select b.workId,a.userId ,a.lastName,a.firstName,C.workNum,b.albumId
,b.[fileName],b.title,ISNULL(B.rating,0) AS rating
,b.resfrom Users a INNER JOIN
(select userId, count(workId) AS workNum from works GROUP BY userId) C
on a.UserId = C.UserId
Left OUTER Join
(select A.UserId,A.albumId,A.[fileName],A.b.title,A.res,A.workId,sum(B.rating) AS rating
from Works A INNER JOIN critiques B ON A.WorkID=B.WorkID
GROUP BY A.UserId,A.albumId,A.[fileName],A.b.title,A.res,A.workId) B
on a.UserId = b.UserId AND b.albumId = isNull(@AlbumId,b.albumId)where a.UserId = isNull(@UserId,a.UserId)
ISNULL(B.workNum,0) AS workNum,
b.albumId ,b.[fileName],b.title,
ISNULL(B.rating,0) AS rating ,b.resfrom Users a Left OUTER Join
(select A.UserId,A.albumId,A.[fileName],
A.b.title,A.res,A.workId,
sum(ISNULL(B.rating,0)) AS rating ,
count(workId) AS workNum
from Works A LEFT OUTER JOIN critiques B ON A.WorkID=B.WorkID
GROUP BY A.UserId,A.albumId,A.[fileName],
A.b.title,A.res,A.workId) B
on a.UserId = b.UserId AND b.albumId = isNull(@AlbumId,b.albumId)
这样写实最快的
Select b.workId,a.userId ,a.lastName,a.firstName,
ISNULL(C.workNum,0) AS workNum,
b.albumId ,b.[fileName],b.title,
ISNULL(C.rating,0) AS rating ,b.resfrom Users a Left OUTER Join
Works B ON A.USERID=B.USERID AND b.albumId = isNull(@AlbumId,b.albumId)
LEFT OUTER JOIN
(select A.UserId,
sum(ISNULL(B.rating,0)) AS rating ,
count(workId) AS workNum
from Works A LEFT OUTER JOIN critiques B ON A.WorkID=B.WorkID
GROUP BY A.UserId,B.workId) C
on a.UserId = C.UserId where a.UserId = isNull(@UserId,a.UserId)
2)Rating 要求是作品的评分总数;