日期:2014-05-17 浏览次数:20501 次
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
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
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_BaseBookIn