求批改sql语句

求修改sql语句
sql语句1：



SELECT ROW_NUMBER()OVER(order by COUNT(bookid) desc) as RowNO,COUNT(bookid)AS borrowNum ,bookid,BookTitle,Author,PressName FROM  

						(

							SELECT bookid,BookTitle,Author,PressName,BorrowDate,BookClassCode FROM T_BorrowInfo,T_BaseBookInfo,T_Press where BookID=T_BaseBookInfo.GUID and T_BaseBookInfo.PressCode=T_Press.PressCode

							 UNION ALL 

							SELECT bookid,BookTitle,Author,PressName,BorrowDate,BookClassCode FROM T_BorrowInfohsty,T_BaseBookInfo,T_Press where BookID=T_BaseBookInfo.GUID and T_BaseBookInfo.PressCode=T_Press.PressCode

						) borrowInfo 

						GROUP BY BookID,BookTitle,Author,PressName ORDER BY borrowNum DESC

结果如下：

---------------------------
修改语句1变语句2（就加入inner join部分）：



SELECT ROW_NUMBER()OVER(order by COUNT(bookid) desc) as RowNO,COUNT(bookid)AS borrowNum ,bookid,BookTitle,Author,PressName FROM  

						(

							SELECT bookid,BookTitle,Author,PressName,BorrowDate,BookClassCode FROM T_BorrowInfo,T_BaseBookInfo,T_Press where BookID=T_BaseBookInfo.GUID and T_BaseBookInfo.PressCode=T_Press.PressCode

							 UNION ALL 

							SELECT bookid,BookTitle,Author,PressName,BorrowDate,BookClassCode FROM T_BorrowInfohsty,T_BaseBookInfo,T_Press where BookID=T_BaseBookInfo.GUID and T_BaseBookInfo.PressCode=T_Press.PressCode

						) borrowInfo 

						inner JOIN (

								SELECT COUNT(bookguid)  AS StorageAmount, bookguid ,T_BookStorage.CollectDeptCode

								FROM   T_BookStorage

								GROUP BY BookGUID,CollectDeptCode

								) a

								on borrowInfo.BookID=a.BookGUID

						GROUP BY BookID,BookTitle,Author,PressName ORDER BY borrowNum DESC

结果：

如何才能让第二列的数字不要和inner join的相乘，我要的结果是加入“inner join”语句后，borrowNum那一列还是按照第一张图那样。sql语言实在薄弱，语文也没学好，可能表达不是很清楚，各位大侠不要见怪。

sql

------解决方案--------------------
http://wenku.baidu.com/view/ec0c7a7a27284b73f2425029.html
------解决方案--------------------
你inner join中count的那个值都没用到，干嘛要count呢？实际上我没看懂你要干嘛
------解决方案--------------------
try this,



SELECT ROW_NUMBER() OVER(order by COUNT(bookid) desc) as RowNO,

       COUNT(distinct bookid)AS borrowNum,

       bookid,BookTitle,Author,PressName 

FROM  

(SELECT bookid,BookTitle,Author,PressName,BorrowDate,BookClassCode 

   FROM T_BorrowInfo,T_BaseBookInfo,T_Press 

   where BookID=T_BaseBookInfo.GUID and T_BaseBookInfo.PressCode=T_Press.PressCode

 UNION ALL 

 SELECT bookid,BookTitle,Author,PressName,BorrowDate,BookClassCode 

   FROM T_BorrowInfohsty,T_BaseBookInfo,T_Press 

   where BookID=T_BaseBookInfo.GUID and T_BaseBookInfo.PressCode=T_Press.PressCode

) borrowInfo

inner join

(SELECT COUNT(bookguid) AS StorageAmount,bookguid,CollectDeptCode

   FROM T_BookStorage

   GROUP BY BookGUID,CollectDeptCode

) a on borrowInfo.BookID=a.BookGUID

GROUP BY BookID,BookTitle,Author,PressName 

ORDER BY borrowNum DESC

------解决方案--------------------
看不懂，呵呵，太复杂了，我是刚刚学习的，但是进来基础篇的论坛都那么难求批改sql语句

相关推荐