烦请高手指点如何优化下列代码

Question

SELECT     UnitName, UnitCode, UnitAdminTel, UnitTechnicGrade,
                          (SELECT     COUNT(*) AS Expr1
                            FROM          PeopleVillageInfo
                            WHERE      (StaffAttribute = '编内') AND (UnitInfo.UnitID = UnitID)) AS bn,
                          (SELECT     COUNT(*) AS Expr1
                            FROM          PeopleVillageInfo AS PeopleVillageInfo_1
                            WHERE      (StaffAttribute = '编外') AND (UnitInfo.UnitID = UnitID)) AS bw,
                          (SELECT     MedBedB
                            FROM          MedicalTreatment
                            WHERE      (MedID =
                                                       (SELECT     MAX(MedID) AS Expr1
                                                         FROM          MedicalTreatment AS MedicalTreatment_1
                                                         WHERE      (UnitID = UnitInfo.UnitID)))) AS cws,
                          (SELECT     AssetArea
                            FROM          AssetManage
                            WHERE      (AssetID =
                                                       (SELECT     MAX(AssetID) AS Expr1
                                                         FROM          AssetManage AS AssetManage_1
                                                         WHERE      (UnitID = UnitInfo.UnitID)))) AS AssetArea,
                          (SELECT     AssetBuild
                            FROM          AssetManage AS AssetManage_3
                            WHERE      (AssetID =
                                                       (SELECT     MAX(AssetID) AS Expr1
                                                         FROM          AssetManage AS AssetManage_1
                                                         WHERE      (UnitID = UnitInfo.UnitID)))) AS AssetBuild,
                          (SELECT     AssetOperation
                            FROM          AssetManage AS AssetManage_2
                            WHERE      (AssetID =
                                                       (SELECT     MAX(AssetID) AS Expr1
                                                         FROM          AssetManage AS AssetManage_1
                                                         WHERE      (UnitID = UnitInfo.UnitID)))) AS AssetOperation, UnitCorporation,
                          (SELECT     CommSum
                            FROM          CommonSanitation
                            WHERE      (CommID =
                                                       (SELECT     MAX(CommID) AS Expr1
                                                         FROM          CommonSanitation AS CommonSanitation_1
                                                         WHERE      (UnitID = UnitInfo.UnitID)))) AS CommSum
FROM         UnitInfo
用以上语句实现貌似有些复杂，望各位大侠指点

Answer 1

你这个是从无数个表分别获取数量，怕是只能用这样的子查询了。