Цитата(ilyabvt @ 13.11.2011, 11:53)
Цитата
Мне из 3 колонок одних и тех же надо получить 4
Через UNION вы такого не сделаете. А вводить нулевую колонку - извращение.
Цитата
В первом запросе мне надо получить остатки на начало месяца, во втором на конец месяца
Ну тогда вам надо указывать диапазон, а не просто "<'30.11.2011'" в противном случае вы получаете не только за конец месяца, но вообще за весь месяц и за все предыдущие месяцы.
Покажите структуру этих трех таблиц и как они связаны.
Я разобрался все!
SELECT
query.material,
query.ed_izm,
SUM(query.count) AS count1,
SUM(query.count2) AS count2,
SUM(query.count3) AS count3,
SUM(query.count4) AS count4
FROM (SELECT
materials.name AS material,
SUM(o_sklad.count) AS count,
ed_izm.name AS ed_izm,
0 AS count2,
0 AS count3,
0 AS count4
FROM
public.o_sklad,
public.materials,
public.ed_izm
WHERE
o_sklad.id_material = materials.id
AND materials.id_ed_izm = ed_izm.id
AND o_sklad.date <='01.11.2011'
GROUP BY
materials.name,
ed_izm.name
UNION ALL
SELECT
materials.name AS material,
0 AS count,
ed_izm.name AS ed_izm,
SUM(o_sklad.count) AS count2,
0 AS count3,
0 AS count4
FROM
public.o_sklad,
public.materials,
public.ed_izm
WHERE
o_sklad.id_material = materials.id
AND materials.id_ed_izm = ed_izm.id
AND o_sklad.date BETWEEN '01.11.2011' AND '21.11.2011'
AND o_sklad.type_operacii = 1
GROUP BY
materials.name,
ed_izm.name
UNION ALL
SELECT
materials.name AS material,
0 AS count,
ed_izm.name AS ed_izm,
0 AS count2,
SUM(o_sklad.count * -1) AS count3,
0 AS count4
FROM
public.o_sklad,
public.materials,
public.ed_izm
WHERE
o_sklad.id_material = materials.id
AND materials.id_ed_izm = ed_izm.id
AND o_sklad.date BETWEEN '01.11.2011' AND '21.11.2011'
AND o_sklad.type_operacii = 2
GROUP BY
materials.name,
ed_izm.name
UNION ALL
SELECT
materials.name AS material,
0 AS count,
ed_izm.name AS ed_izm,
0 AS count2,
0 AS count3,
SUM(o_sklad.count) AS count4
FROM
public.o_sklad,
public.materials,
public.ed_izm
WHERE
o_sklad.id_material = materials.id
AND materials.id_ed_izm = ed_izm.id
AND o_sklad.date BETWEEN '01.11.2011' AND '31.11.2011'
GROUP BY
materials.name,
ed_izm.name
) AS query
GROUP BY
query.material,
query.ed_izm