IF (bShowSaledNum = 1) THEN
SET @MyQuery =
CONCAT(
"Update Tab Set SaledNum = (select Num From (
select BuildingNo, count(FramediaNo) As Num From (
Select DISTINCT FA.BuildingNo, FA.FramediaNo From dosn_csFramediaAssign FA
Left Join Tab T On FA.BuildingNo = T.BuildingNo
Left Join dosn_csFramedia F On FA.FramediaNo = F.FramediaNo
Where FA.DeleteFlag = 0 and FA.Status <> 2 and F.DeleteFlag = 0 ",
companyWhere,
" and FA.DesignStatus = 2 and FA.BeginDate <= '",
endDate,
"' and FA.EndDate >= '",
beginDate,
"')R group by BuildingNo) kk where Tab.BuildingNo = kk.BuildingNo)");
PREPARE msql FROM @MyQuery ;
EXECUTE msql ;
END IF;
IF (bShowKeepNum = 1) THEN
SET @MyQuery =
CONCAT(
"Update Tab Set KeepNum =(select Num From (
select BuildingNo, count(FramediaNo) As Num From (
Select DISTINCT FA.BuildingNo, FA.FramediaNo From dosn_csFramediaAssign FA
Left Join Tab T On FA.BuildingNo = T.BuildingNo
Left Join dosn_csFramedia F On FA.FramediaNo = F.FramediaNo
Where FA.DeleteFlag = 0 and FA.Status <> 2 and F.DeleteFlag = 0
and FA.DesignStatus = 1 ",
companyWhere,
" and FA.BeginDate <= '",
endDate,
"' and FA.EndDate >= '",
beginDate,
"')R group by BuildingNo) kk Where Tab.BuildingNo = KK.BuildingNo)");
PREPARE msql FROM @MyQuery ;
EXECUTE msql ;
END IF;
SET @MyQuery =
CONCAT(
"INSERT INTO CT(FramediaNo,BuildingNo,UnitNo,ElevatorNo)
SELECT FramediaNo,BuildingNo,UnitNo,ElevatorNo FROM (
SELECT DISTINCT A.FramediaNo AS FramediaNo ,A.BuildingNo, A.UnitNo, A.ElevatorNo FROM (SELECT DISTINCT F.FramediaNo,F.BuildingNo, F.UnitNo, F.ElevatorNo
FROM dosn_csFramedia F
WHERE F.DeleteFlag = 0 ",
companyWhere,
" AND F.InstallStatus <> 2
AND F.WarningDate >= '",
endDate,
"') A
UNION ALL
SELECT DISTINCT B.FramediaNo AS FramediaNo ,B.BuildingNo, B.UnitNo, B.ElevatorNo FROM (SELECT DISTINCT FA.FramediaNo,F.BuildingNo, F.UnitNo, F.ElevatorNo
FROM dosn_csFramediaAssign FA
LEFT JOIN
dosn_csFramedia F
ON FA.FramediaNo = F.FramediaNo
WHERE FA.DeleteFlag = 0 ",
companyWhere,
" and F.InstallDate <= '",
beginDate,
"' and F.InstallStatus = 1 and FA.BeginDate <= '",
endDate,
"' and FA.EndDate >= '",
beginDate,
"' AND ( FA.Status = 3
OR FA.DesignStatus = 1
OR FA.DesignStatus = 2)) B
)TEMP GROUP BY FramediaNo HAVING COUNT(FramediaNo) = 1");
PREPARE msql FROM @MyQuery ;
EXECUTE msql ;
UPDATE Tab
SET ElevatorNums =
(SELECT B.Num
FROM (SELECT BuildingNo, COUNT(ElevatorNo) AS Num
FROM (SELECT DISTINCT BuildingNo, ElevatorNo
FROM CT) X
GROUP BY BuildingNo) B
WHERE Tab.BuildingNo = B.BuildingNo);
UPDATE Tab
SET ElevatorNumsIn =
(SELECT B.Num
FROM (SELECT BuildingNo, COUNT(ElevatorNo) AS Num
FROM (SELECT DISTINCT
T.BuildingNo, T.ElevatorNo
FROM CT T
LEFT JOIN
dosn_csElevator E
ON T.ElevatorNo = E.ElevatorNo
WHERE E.LAttribute = '10') X
GROUP BY BuildingNo) B
WHERE Tab.BuildingNo = B.BuildingNo);
UPDATE Tab
SET ElevatorNumsOut =
(SELECT B.Num
FROM (SELECT BuildingNo, COUNT(ElevatorNo) AS Num
FROM (SELECT DISTINCT
T.BuildingNo, T.ElevatorNo
FROM CT T
LEFT JOIN
dosn_csElevator E
ON T.ElevatorNo = E.ElevatorNo
WHERE E.LAttribute <> '10') X
GROUP BY BuildingNo) B
WHERE Tab.BuildingNo = B.BuildingNo);
UPDATE Tab
SET UnitNums =
(SELECT B.Num
FROM (SELECT BuildingNo, COUNT(UnitNo) AS Num
FROM (SELECT DISTINCT BuildingNo, UnitNo
FROM CT) X
GROUP BY BuildingNo) B
WHERE Tab.BuildingNo = B.BuildingNo);
UPDATE Tab
SET UnitNumsIn =
(SELECT B.Num
FROM (SELECT BuildingNo, COUNT(UnitNo) AS Num
FROM (SELECT DISTINCT T.BuildingNo, T.UnitNo
FROM CT T
LEFT JOIN
dosn_csElevator E
ON T.ElevatorNo = E.ElevatorNo
WHERE E.LAttribute = '10') X
GROUP BY BuildingNo) B
WHERE Tab.BuildingNo = B.BuildingNo);
UPDATE Tab
SET UnitNumsOut =
(SELECT B.Num
FROM (SELECT BuildingNo, COUNT(UnitNo) AS Num
FROM (SELECT DISTINCT T.BuildingNo, T.UnitNo
FROM CT T
LEFT JOIN
dosn_csElevator E
ON T.ElevatorNo = E.ElevatorNo
WHERE E.LAttribute <> '10') X
GROUP BY BuildingNo) B
WHERE Tab.BuildingNo = B.BuildingNo);
UPDATE Tab
SET IdeNumIn =
(SELECT B.Num
FROM (SELECT BuildingNo, COUNT(FramediaNo) AS Num
FROM (SELECT DISTINCT
T.BuildingNo, T.FramediaNo
FROM CT T
LEFT JOIN
dosn_csElevator E
ON T.ElevatorNo = E.ElevatorNo
WHERE E.LAttribute = '10') X
GROUP BY BuildingNo) B
WHERE Tab.BuildingNo = B.BuildingNo);
UPDATE Tab
SET IdeNumOut =
(SELECT B.Num
FROM (SELECT BuildingNo, COUNT(FramediaNo) AS Num
FROM (SELECT DISTINCT
T.BuildingNo, T.FramediaNo
FROM CT T
LEFT JOIN
dosn_csElevator E
ON T.ElevatorNo = E.ElevatorNo
WHERE E.LAttribute <> '10') X
GROUP BY BuildingNo) B
WHERE Tab.BuildingNo = B.BuildingNo);
UPDATE Tab
SET FramediaSale =
IFNULL(FramediaInCount, 0)
+ IFNULL(FramediaOutCount, 0)
- IFNULL(CanNotSaled, 0),
IdeNum =
IFNULL(FramediaInCount, 0)
+ IFNULL(FramediaOutCount, 0)
- IFNULL(NotIdleNum, 0);
SELECT COUNT(1) INTO total
FROM Tab T
LEFT JOIN dosn_csBuilding BU
ON T.BuildingNo = BU.BuildingNo
LEFT JOIN Pub_Resource R_L
ON BU.BuildingLevel = R_L.Id
AND R_L.TypeId = 'CMIS_CMBuildingLevel'
LEFT JOIN pub_resource R
ON BU.District = R.Id
AND BU.City = R.ParentId
AND R.TypeId = 'cm006'
LEFT JOIN dosn_employee O
ON BU.Vindicator = O.employee_id;
END$$DELIMITER ;[/code]
OR 建立另外1个临时表
MYSQL的限制