求修改sql语句

Question

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

Answer 1

http://wenku.baidu.com/view/ec0c7a7a27284b73f2425029.html

Answer 2

你inner join中count的那个值都没用到，干嘛要count呢？实际上我没看懂你要干嘛

Answer 3

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

Answer 4

原来从一开始就是自己的思路错了，玩了一局dota才发现要查询的字段搞错了，谢谢各位