MYSQL查询通过在逗号分隔的字符串中查找其id来计算项的后代

My MYSQL database uses a tree-like system where each item can have an arbitrary number of descendants. Each item has a regular INT 'parent' column containing its parent as well as a VARCHAR 'parents' column which consists of a comma-separated string containing all of its ancestor's ids.

我的MYSQL数据库使用树状系统,其中每个项目可以具有任意数量的后代。每个项目都有一个包含其父项的常规INT“父级”列以及一个包含逗号分隔字符串的VARCHAR“父级”列,该字符串包含其所有祖先的ID。

id    parent    parents
-------------------------------
1     0         0
2     1         0,1
3     1         0,1
4     3         0,1,3

I need to get a list of all items, each of them with their total number of descendants counted up. Here is the query I have so far:

我需要获得所有项目的列表,每个项目都有他们的后代总数。这是我到目前为止的查询:

SELECT items.id AS item_id, 
COUNT(children.id) AS children 
FROM items items 
LEFT JOIN items children ON (items.id IN (children.parents))

This just sends back one row, with a child count of 0. How do I do this properly?

这只会发回一行,子计数为0.如何正确执行此操作?

EDIT:

After fixing the query so it appears like this:

修复查询后,它显示如下:

SELECT 
  i.id AS item_id, 
  COUNT(*) AS children 
FROM 
  items i 
LEFT JOIN 
  items c 
  ON (i.id IN (c.parents))
GROUP BY i.id;

the results show the rows, but each has only one child. This does not reflect the data, presumably something is wrong with the IN statement (FIND_IN_SET does the same thing).

结果显示行,但每个只有一个子。这并不反映数据,可能是IN语句出错(FIND_IN_SET做同样的事情)。

EDIT2:

After changing the IN statement to the following

将IN语句更改为以下内容后

ON LOCATE(i.id, c.parents) > 0

item 1 has the correct number of children (3) but the remaining items all show up as having 1 child. Items 2 and 4 should have 0, and 3 should have 1.

第1项具有正确数量的子项(3),但其余项目都显示为有1个孩子。第2项和第4项应该有0,而3应该有1。

1 个解决方案

#1

You need to GROUP BY items.id for COUNT()to work as intended.

您需要GROUP BY items.id才能使COUNT()按预期工作。

With aliases changed to something less ambiguous:

将别名更改为不太模糊的内容:

SELECT 
  i.id AS item_id, 
  COUNT(*) AS children 
FROM 
  items i 
LEFT JOIN 
  items c 
  ON FIND_IN_SET(i.id, c.parents) > 0 
WHERE c.id <> i.id
GROUP BY i.id;

For more complex COUNT()/GROUP BY examples, see this question or MySQL documentation. For FIND_IN_SET(), nice example here.

有关更复杂的COUNT()/ GROUP BY示例,请参阅此问题或MySQL文档。对于FIND_IN_SET(),这里有一个很好的例子。

See sqlfiddle here

请参见这里的sqlfiddle

#1