Mysql - 仅针对父类别的递归查询 [英] Mysql - Recursive Query for only the parent categories

问题描述

我有下表category:

id | desc_cat       | parent_id
19 | Personal       | (null)
20 | Credit Card    | 19
21 | Academy        | 19
22 | Home           | (null)
23 | Water          | 22
24 | Energy         | 22
25 | Rent           | 22

我有一个表，其中包含名称为 cashbook 的条目:

And I have a table containing entries with name cashbook:

id  | value    | category_id | date
177 | 480.55   | 20          | 2016-05-01
178 | 100.00   | 24          | 2016-05-04
179 | 580.00   | 25          | 2016-05-05
180 | 80.00    | 21          | 2016-05-09
181 | 28.00    | 23          | 2016-05-11

我需要在 cashbook 预约并返回增加的值和按父类别分组(类似于 null)，例如:

I need to make an appointment in cashbook and return the added values and grouped by the parent category (which is like null), for example:

Category-Father | Total
Personal        | 560,55
Home            | 708

你能帮我组装这个查询吗??我做了一些示例查询递归，但我很迷茫.我的系统是用 PHP (Yii框架).

Can you help me assemble this query ?? I took some sample queries Recursive but I'm very lost. My system is being made in PHP (Yii framework).

更新 1

我这样做了:

select a.desc_category as segment, sum(b.value) as total 
from category as a
inner join category as c on (a.parent_id = c.id_category and c.parent_id is not null)
inner join cashbook as b on (b.category_id = a.id_category)
INNER JOIN user as u ON b.user_id = u.id
WHERE u.id = 29
group by a.desc_category

工作:

SELECT y.desc_category as segment, sum( x.value) as total FROM (
SELECT category.id_category, category.desc_category, category.parent_id , c.value AS value
FROM category
INNER JOIN cashbook AS c ON category.id_category = c.category_id )AS x 
INNER JOIN category AS y ON x.parent_id = y.id_category
INNER JOIN user AS u ON y.user_id = u.id
WHERE u.id = 3
GROUP BY y.desc_category

最后，我如何获得仅当月和当前年份?

Finally, how do I get ONLY THE MONTH and Current year?

我试过没用:

WHERE u.id = 3 AND MONTH(date) = 05 AND YEAR(date) = 2016

推荐答案

可以是同一个表和一个组使用临时表的内连接

Could be a inner join o the same table and a group by using a temp table

select sum( x.value), y.desc_cat from (
select category.id, category.desc, category.parent_id , cashbook.value as value
from category
inner join category.id = cashbook.category_id )  as x 
inner join category as y on x.parent_id = y.id
group by y.desc_cat

这篇关于Mysql - 仅针对父类别的递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！