chur » 26 сен 2004, 05:52
Предлагаю результат после UNION сгруппировать еще раз.
Код: Выделить всё
SELECT Z, N, A, SUM(IIF(F=1, S, 0)) AS ROZN, SUM(IIF(F=2, S, 0)) AS OPT
FROM
(
SELECT ZD_BID AS Z, B_NAZV AS N, B_AVT AS A, SUM(ZD_CNT) AS S, 1 AS F FROM Book, Zakaz_M, Zakaz_D WHERE (ZM_ID=ZD_ZID) AND
(ZM_DATA_SOZD BETWEEN '01.01.2004' AND '31.12.2004') AND (B_ID=ZD_BID) GROUP BY ZD_BID, B_NAZV, B_AVT
UNION
SELECT ZOD_BID AS Z, B_NAZV AS N, B_AVT AS A, SUM(ZOD_CNT) AS S, 2 AS F FROM Book, ZakazO_M, ZakazO_D WHERE (ZOM_ID=ZOD_ZOMID) AND
(ZOM_DATA_SOZD BETWEEN '01.01.2004' AND '31.12.2004') AND (B_ID=ZOD_BID) GROUP BY ZOD_BID, B_NAZV, B_AVT
)
GROUP BY Z, N, A
Предлагаю результат после UNION сгруппировать еще раз.[code]
SELECT Z, N, A, SUM(IIF(F=1, S, 0)) AS ROZN, SUM(IIF(F=2, S, 0)) AS OPT
FROM
(
SELECT ZD_BID AS Z, B_NAZV AS N, B_AVT AS A, SUM(ZD_CNT) AS S, 1 AS F FROM Book, Zakaz_M, Zakaz_D WHERE (ZM_ID=ZD_ZID) AND
(ZM_DATA_SOZD BETWEEN '01.01.2004' AND '31.12.2004') AND (B_ID=ZD_BID) GROUP BY ZD_BID, B_NAZV, B_AVT
UNION
SELECT ZOD_BID AS Z, B_NAZV AS N, B_AVT AS A, SUM(ZOD_CNT) AS S, 2 AS F FROM Book, ZakazO_M, ZakazO_D WHERE (ZOM_ID=ZOD_ZOMID) AND
(ZOM_DATA_SOZD BETWEEN '01.01.2004' AND '31.12.2004') AND (B_ID=ZOD_BID) GROUP BY ZOD_BID, B_NAZV, B_AVT
)
GROUP BY Z, N, A[/code]