[mysql] 2x sum w jednym zapytaniu, Problem z poprawnością wyników zapytania Opcje

[sstolarczyk]

Witajcie,

Mam problem z utworzeniem odpowiedniego zapytania, uwzględniającego dwie sumy wartości. Z góry przepraszam za dłuższy post, ale staram się oszczędzić wszystkim czas, podając wszystkie szczegóły.

Mamy cztery tabele, które wyglądają tak:

1) Budżety

CREATE TABLE `wydatki`.`monthly_budget` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`amount` decimal(19,2) NOT NULL,
`from_date` datetime NOT NULL,
`name` varchar(255) COLLATE utf8_polish_ci NOT NULL,
`to_date` datetime NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `FK8FA263B722A1FFA5` (`user_id`),
CONSTRAINT `FK8FA263B722A1FFA5` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);

2) Kategorie wydatków

CREATE TABLE `wydatki`.`expense_category` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL,
`name` varchar(255) COLLATE utf8_polish_ci NOT NULL,
PRIMARY KEY (`id`)
);

3) Limity kwot wydatków (dla danej kategorii i budżetu)

CREATE TABLE `wydatki`.`budget_category_limit` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`amount` decimal(19,2) DEFAULT NULL,
`budget_id` bigint(20) NOT NULL,
`category_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `FKF002E174C4D9AC7` (`category_id`),
KEY `FKF002E17470341C9C` (`budget_id`),
CONSTRAINT `FKF002E17470341C9C` FOREIGN KEY (`budget_id`) REFERENCES `monthly_budget` (`id`),
CONSTRAINT `FKF002E174C4D9AC7` FOREIGN KEY (`category_id`) REFERENCES `expense_category` (`id`)
);

4) Wydatki

CREATE TABLE `wydatki`.`expense` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`amount` decimal(19,2) NOT NULL,
`budget_id` bigint(20) NOT NULL,
`category_id` bigint(20) NOT NULL,
`description` varchar(1000) COLLATE utf8_polish_ci NOT NULL,
`date_created` datetime NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `FKB1F4C85822A1FFA5` (`user_id`),
KEY `FKB1F4C858C4D9AC7` (`category_id`),
KEY `FKB1F4C85870341C9C` (`budget_id`),
CONSTRAINT `FKB1F4C85822A1FFA5` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
CONSTRAINT `FKB1F4C85870341C9C` FOREIGN KEY (`budget_id`) REFERENCES `monthly_budget` (`id`),
CONSTRAINT `FKB1F4C858C4D9AC7` FOREIGN KEY (`category_id`) REFERENCES `expense_category` (`id`)
);

Zapytanie do zbudowania:

Wyświetlić nazwę kategorii głównej (główna to taka, której parent_id = 0), sumę ustalonych limitów dla jej składowych podkategorii (czyli tych kategorii, których parent_id jest taki sam) i sumę kwot wydatków - tak samo, czyli pogrupowanych według kategorii głównych. I to wszystko dla konkretnego budżetu, którego numer ID znamy (tabela z limitami ma referencje budget_id, jak również tabela z wydatkami).

Czyli generalnie mając np. takie wpisy w bazie:

Budżet: Luty
Kategoria główna: Dom -> Podkategorie: Czynsz, Telefon
Limity dla tego budżetu: Czynsz - 1000 zł, Telefon - 100 zł
W kategorii Czynsz mamy dodany wydatek 500 zł, w kategorii Telefon mamy dodany wydatek 80 zł. Chcemy uzyskać taki wynik:

Nazwa głównej kategorii / Suma limitów / Aktualny stan
Dom / 1100 zł (czynsz + telefon) / 580 zł (czynsz + telefon)

Obecnie mam takie zapytanie:

select e.budget_id, ec_kat.id as catId, ec_kat.name, sum(bcl.amount) as sumLimit, sum(e.amount) as sumExpense from expense e
inner join monthly_budget mb on (mb.id = e.budget_id)
inner join expense_category ec_kat on (ec_kat.parent_id = 0)
left outer join expense_category ec_podkat on (ec_podkat.parent_id = ec_kat.id)
left outer join budget_category_limit bcl on (bcl.category_id = ec_podkat.id)

where mb.id = 1
and e.category_id = bcl.category_id
and bcl.budget_id = mb.id

group by ec_kat.id

Efekt: niby działa, ale w pewnym przypadku jednak nie. Gdy dodaję nowy wydatek w nowej podkategorii, suma limitów jest poprawna. Ale gdy dodam kolejny wydatek do tej samej podkategorii, suma limitów dla kategorii głównej zwiększa się o limit ustalony dla tej podkategorii (a nie powinna).

Pomożecie?

Ten post edytował sstolarczyk 12.02.2010, 12:51:34