SQL 查询学校借阅前 50 图书

工作记录，方便以后参考 (优化之前人写的方法，之前在代码里遍历，效率太低了 )

1. 借阅前50的书

• 前 50

SELECT fkBook,COUNT(*) AS Number FROM TbBorrowRecord WHERE fkBook IS NOT NULL AND  fkSchool='0004' AND (nState=2 OR nState=3 OR nState=4 OR nState=5 OR nState=7) GROUP BY fkBook ORDER BY Number DESC LIMIT 0,50

前50.png

• 左联显示明细

 SELECT r.*,b.SImg,b.SName,b.sISBN,b.SPress,b.sLabelName AS SLabel FROM  
(SELECT fkBook,COUNT(*) AS Number FROM TbBorrowRecord WHERE fkBook IS NOT NULL AND  fkSchool='0004' AND (nState=2 OR nState=3 OR nState=4 OR nState=5 OR nState=7) GROUP BY fkBook ORDER BY Number DESC LIMIT 0,50 ) r LEFT JOIN TbBook b ON r.fkBook = b.pkCode

联查.png