Get the count of the number of items under the children categories of a given category in MySQL
Say we have this tables
t1
--------------------------------
category_id | name | lft | rgt
--------------------------------
1 cat1 1 8
2 cat2 2 3
3 cat3 4 7
4 cat4 5 6
t2
-------------------------------
item_id | category_id
--------------------------------
1 1
2 2
3 3
4 4
Is there a way in MySQL to get the number of items a category has(including those items that belong to its children)? Something like this...
-------------------------------
category_id | item_count
--------------------------------
1 开发者_开发百科 4
2 1
3 2
4 1
I have trouble connecting the query that gets the category_ids and the query that gets the count of the children categories.
SELECT category_id FROM t1 WHERE <some conditions here>
SELECT
COUNT(*) AS item_count,
category_id
FROM
t2
WHERE
t2.category_id IN
(
SELECT
node.category_id
FROM
t1 AS node,
t1 AS parent
WHERE
node.lft BETWEEN parent.lft AND parent.rgt
AND parent.category_id = 5 <---- How do I pass the category ids of the first query
here?
)
SELECT a.category_id, COUNT(a.item_id) as itemcount
FROM itemTable a
GROUP BY a.category_id;
All you need to do is to find out how you can add the ones that don't have any items to this.
EDIT: Okay, so the problem is getting a value into a subselect... I'm not an expert at SQL, but I suggest creating a view.
SELECT category_id FROM t1 WHERE <some conditions here>
CREATE VIEW temp AS
SELECT
node.category_id as nc_id, parent.category_id as pc_id
FROM
t1 AS node,
t1 AS parent
WHERE
node.lft BETWEEN parent.lft AND parent.rgt;
VIEW temp
nc_id | pc_id
1 1
2 1
3 1
4 1
2 2
3 2
4 3
t2
itemid| catid
1 1
2 2
3 3
4 4
CREATE VIEW temp2 AS
SELECT
*
FROM
t2, temp
WHERE t2.category_id = temp.nc_id OR t2.category_id = temp.pc_id
cat_id| itemid |nc_id | pc_id
1 1 1 1
1 1 2 1
1 1 3 1
1 1 4 1
2 2 2 1
2 2 2 2
2 2 3 2
3 3 3 1
3 3 3 2
3 3 4 3
4 4 4 1
4 4 4 3
CREATE VIEW temp3 AS
SELECT cat_id, itemid, nc_id, pc_id
FROM temp2
GROUP BY item_id, nc_id;
temp3:
cat_id| itemid |nc_id | pc_id
1 1 1 1
1 1 2 1
1 1 3 1
1 1 4 1
2 2 2 1
2 2 3 2
3 3 3 1
3 3 4 3
4 4 4 1
SELECT count(itemid) AS itemcount, cat_id
FROM temp3
GROUP BY cat_id;
itemcount |cat_id
4 1
2 2
2 3
1 4
DROP VIEW temp3;
DROP VIEW temp2;
DROP VIEW temp;
And thus concludes my answer. I hope it works, and I also hope you can clean my mess up (in terms of optimizing what I wrote)
精彩评论