我有下表:
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
我有一个包含名为
category
的条目的表: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
并返回附加值和按父类别分组(类似于
cashbook
),例如:Category-Father | Total
Personal | 560,55
Home | 708
你能帮我整理一下这个问题吗??我做了一些样本查询
递归,但我很迷路。我的系统是用PHP(Yii
框架)。
更新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
最后,我怎样才能得到月份和当前年份?
我试过但没成功:
WHERE u.id = 3 AND MONTH(date) = 05 AND YEAR(date) = 2016
最佳答案
可以是同一个表的内部联接,也可以是使用临时表的组
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 - Mysql-仅对父类别进行递归查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/36972638/