MySQL汇总问题

Question

我的客户有一个现有的 Dot Net 软件，我正在将其迁移到 PHP 中。我正在根据此做一份报告

以下查询完全有效，但问题在于分公司总计、单位总计、船舶总计、面积总计、区域总计和总计的汇总

select sh.NAME ShipName,sh.AREA_ID, pu.NAME PostingName, b.BRANCH_NAME Branch, r.RANK_NAME Rank, p.Name Part, count(SAILORID)Borne,

(SELECT SUM(us.SanctionNo)sanction FROM unitwisesanction us WHERE us.RankID = s.RANKID AND us.PostingUnitID = s.POSTINGUNITID
AND us.PartIIID = s.FIRSTPARTID GROUP BY us.PostingUnitID,us.RankID,us.PartIIID) sanction,

(select count(t.TransferID)TotalOut from transfer t left join sailor ts on t.SailorID = ts.SAILORID where ts.RANKID = s.RANKID
and ts.FIRSTPARTID = s.FIRSTPARTID and t.PostingUnitID = s.POSTINGUNITID )TotalIn,

(select count(t.TransferID)TotalOut from transfer t left join sailor ts on t.SailorID = ts.SAILORID where ts.RANKID = s.RANKID
and ts.FIRSTPARTID = s.FIRSTPARTID and ts.POSTINGUNITID = s.POSTINGUNITID )TotalOut

from sailor s
left join bn_branch b on s.BRANCHID = b.BRANCH_ID
left join bn_rank r on s.RANKID = r.RANK_ID
left join partii p on s.FIRSTPARTID = p.PartIIID
left join bn_daogroup a on b.DAO_GROUPID = a.GROUP_ID
left join bn_ship_establishment sh on s.SHIPESTABLISHMENTID = sh.SHIP_ESTABLISHMENTID
left join bn_posting_unit pu on s.POSTINGUNITID = pu.POSTING_UNITID
where SAILORSTATUS = 1  AND s.ZONEID IN (1) AND s.AREAID IN (5) AND s.SHIPESTABLISHMENTID IN (53,125)  AND a.GROUP_ID IN (1,2,3,4,5,7)
group by s.POSTINGUNITID, RANKID, FIRSTPARTID

有人可以帮助我吗？如何为所有总计生成额外行

Answer 1

我已经通过以下查询解决了这个问题

SELECT coalesce(ShipName, "Ship Total")ShipName, AREA_ID, coalesce(PostingName,"Unit Total")PostingName, coalesce(Branch, "Branch Total")Branch, Rank, Part,
      SUM(Borne) Borne, SUM(sanction) sanction, SUM(TotalIn) TotalIn, SUM(TotalOut) TotalOut
from(
  select s.POSTINGUNITID, RANKID, FIRSTPARTID, sh.NAME ShipName,sh.AREA_ID, pu.NAME PostingName, b.BRANCH_NAME Branch, r.RANK_NAME Rank, p.Name Part, count(SAILORID)Borne,

  (SELECT SUM(us.SanctionNo)sanction FROM unitwisesanction us WHERE us.RankID = s.RANKID AND us.PostingUnitID = s.POSTINGUNITID
  AND us.PartIIID = s.FIRSTPARTID GROUP BY us.PostingUnitID,us.RankID,us.PartIIID) sanction,

  (select count(t.TransferID)TotalOut from transfer t left join sailor ts on t.SailorID = ts.SAILORID where ts.RANKID = s.RANKID
  and ts.FIRSTPARTID = s.FIRSTPARTID and t.PostingUnitID = s.POSTINGUNITID )TotalIn,

  (select count(t.TransferID)TotalOut from transfer t left join sailor ts on t.SailorID = ts.SAILORID where ts.RANKID = s.RANKID
  and ts.FIRSTPARTID = s.FIRSTPARTID and ts.POSTINGUNITID = s.POSTINGUNITID )TotalOut

  from sailor s
  left join bn_branch b on s.BRANCHID = b.BRANCH_ID
  left join bn_rank r on s.RANKID = r.RANK_ID
  left join partii p on s.FIRSTPARTID = p.PartIIID
  left join bn_daogroup a on b.DAO_GROUPID = a.GROUP_ID
  left join bn_ship_establishment sh on s.SHIPESTABLISHMENTID = sh.SHIP_ESTABLISHMENTID
  left join bn_posting_unit pu on s.POSTINGUNITID = pu.POSTING_UNITID
  where SAILORSTATUS = 1  AND s.ZONEID IN (1) AND s.AREAID IN (5) AND s.SHIPESTABLISHMENTID IN (53,125)  AND a.GROUP_ID IN (1,2,3,4,5,7)
  group by s.POSTINGUNITID, RANKID, FIRSTPARTID
) a
GROUP BY ShipName, PostingName, Branch WITH ROLLUP;