统计报名机构的作品信息

SELECT
    c.areaName AS province,
    d.areaName AS city,
    b.`name` AS school,
    a.NAME,
    a.department,
    a.position,
    a.telephone,
    a.mobile,
    a.reader_act_id,
    a.reader_org_id,
    ( SELECT count( c.lib_id ) FROM yuedu_creations AS c WHERE c.lib_id = a.id AND is_rec = 1 ) AS rec,
    e.title AS act_title,
    FROM_UNIXTIME( e.start_at ) AS act_start,
    FROM_UNIXTIME( e.end_at ) AS act_end,
    e.join_num as act_join_user,
    (ifnull(( SELECT sum( f.view_count ) FROM creations AS f WHERE a.reader_act_id = f.act_id ), 0) + e.view_count ) AS act_view,
    (ifnull(( SELECT count( f.id ) FROM creations AS f WHERE a.reader_act_id = f.act_id ), 0)) AS creation_count,
    ( ifnull(SELECT sum( f.like_count ) FROM creations AS f WHERE a.reader_act_id = f.act_id, 0) ) AS creation_like,
    ( ifnull(SELECT sum( f.view_count ) FROM creations AS f WHERE a.reader_act_id = f.act_id, 0) ) AS creation_view
FROM
    admins AS a
    LEFT JOIN university AS b ON a.university_id = b.id
    LEFT JOIN region AS c ON a.province_id = c.areaId
    LEFT JOIN activities AS e ON a.reader_act_id = e.id
    LEFT JOIN region AS d ON a.city_id = d.areaId 
WHERE
    a.`level` = '0' 
ORDER BY
    creation_count DESC