MySQL统计数据的总条数，已使用和未使用的

MySQL： 表storage_room,storage_rack

room:

rack:

sql方法一:

//经测试，此条sql 的count(*)的统计数据不准确，如需统计需另外自己写，我的是能测试即可

SELECT a.*,b.id,b.storage_room_id,b.status,count(*),count(CASE WHEN status=2 THEN 0 END) qy,count(CASE WHEN status=3 THEN 0 END) wqy FROM wd_storage_room a LEFT JOIN wd_storage_rack b ON a.id = b.storage_room_id //此条sql是为了总和到上面的sql而写的 SELECT count(*),count(CASE WHEN status=2 THEN 0 END) qy,count(CASE WHEN status=3 THEN 0 END) wqy FROM wd_storage_rack

sql方法二：//此条是在jeecgboot框架下的sql

SELECT
    a.*,
    (SELECT COUNT(ac.id) FROM wd_storage_rack ac WHERE ac.storage_room_id = a.id) AS storageRackAllNum,
    (SELECT COUNT(ac.id) FROM wd_storage_rack ac WHERE ac.storage_room_id = a.id AND ac.`status` = 2) AS storageRackUseNum,
    (SELECT COUNT(ac.id) FROM wd_storage_rack ac WHERE ac.storage_room_id = a.id AND ac.`status` = 3) AS storageRackUnUseNum
FROM
    wd_storage_room a
ORDER BY create_time DESC

新改的sql

SELECT
    a.id,a.storage_rack_id,a.code,a.title,a.site,a.enable,
    count(distinct b.id) as storageRackAllNum,
    count(distinct CASE WHEN b.status=1 THEN 0 END) as storageRackUseNum,
    count(distinct CASE WHEN b.status=2 THEN 0 END) as storageRackUnUseNum
FROM
((SELECT * FROM wd_storage_room ) a
left join (SELECT * FROM wd_storage_rack ) b
on  FIND_IN_SET(b.id, a.storage_rack_id))
<if test="storageRoom.title != null and storageRoom.title != ">
    where
    a.title=${storageRoom.title}
</if>
group by a.id