SELECT a.*, FORMAT((@i:+1),0) as i FROM (
SELECT
drt.typeCode,
drt.`name` type,
GROUP_CONCAT(br.roomName) roomName,
COUNT(br.id) number,
SUM(br.architectureAcreage) architectureAcreage,
SUM(br.useAcreage) useAcreage,
GROUP_CONCAT(su.`name`) useCompanys,
GROUP_CONCAT(bf.floorNumber) floorNumber
FROM
biz_room br
LEFT JOIN dic_room_type drt ON br.roomNature = drt.`code`
LEFT JOIN biz_room_unit bru ON bru.roomId = br.id
LEFT JOIN sys_unit su ON bru.unitId = su.id
LEFT JOIN biz_floor bf ON bf.id = br.floorId
LEFT JOIN biz_building bb ON bb.id = bf.buildingId
WHERE
br.roomType = ‘01‘
AND (
drt.typeCode = ‘04‘
OR drt.typeCode = ‘02‘
OR drt.typeCode = ‘03‘
)
AND br.useType = ‘1‘
AND bb.id =‘E68DEB62799149DD8B45EC55D79426AB‘
GROUP BY
drt.typeCode) a,(select @i:=0) as it
来源: