SELECT ls.areaAddress AS areaAddress,(SELECT des FROM librarys_level WHERE VALUE=ls.libraryLevel) AS libraryLevel,ls.hallCode AS hallCode,ls.NAME
AS libraryName,
SUM(IF(isOut=1,(temp.price+temp.attachPrice),0)) AS inTotalprice,
SUM(IF(isOut=2,(temp.price+temp.attachPrice),0)) AS outTotalprice,
SUM(IF(isOut=1,1,0)) AS inTotalCount,
SUM(IF(isOut=2,1,0)) AS outTotalCount
FROM librarys AS ls
LEFT JOIN (
SELECT CASE
WHEN circulate.outHallCode =ls1.hallCode THEN 2
ELSE 1
END AS isOut,
circulate.outHallCode AS outHallCode,
circulate.inHallCode AS inHallCode,
book.price AS price,
book.attachPrice AS attachPrice,
sc.hallCode AS customerHallCode
FROM librarys AS ls1
LEFT JOIN system_customer AS sc ON sc.id
=ls1.customerId
LEFT JOIN library_circulate AS circulate ON (circulate.outHallCode=ls1.hallCode OR circulate.inHallCode=ls1.hallCode)
LEFT JOIN library_circulate_map AS map ON map.circulateId=circulate.id
LEFT JOIN library_books book ON book.id
=map.libraryBookId
WHERE circulate.circulate_status=6
) AS temp ON IF(isOut=1,temp.inHallCode=ls.hallCode,temp.outHallCode=ls.hallCode)
WHERE customerHallCode='AARH' GROUP BY ls.hallcode
一个复杂sql语句实例
最新推荐文章于 2024-08-15 17:33:47 发布