尝试返回父记录的子记录的所有列 [英] Trying to return all columns of child records for a parent record
问题描述
我正在寻找一种解决方案,通过该解决方案我可以获取所有父记录的子记录. 我找到了可以满足我的需求的解决方案,如下所示
I was searching for a solution by which I could get all the child records for a parent record. I found a solution that meet my needs as shown here
唯一的问题是上述解决方案正在串联ID.
Only problem is that the above solution is concatenating the IDs.
当前结果集
ID列用逗号分隔,其值= 2,3,4
It is comma separated of ID column with values = 2,3,4
预期产量
ID Name ParentID
1 1st null
2 2nd 1
3 3rd 1
4 4th 2
我尝试了以下代码.
SELECT @pv:=
(SELECT * FROM tblreport WHERE ParentID IN (@pv)) AS lv FROM tblreport
JOIN
(SELECT @pv:=2)tmp
WHERE ParentID IN (@pv)
并收到错误消息:操作数应包含1列
My sample SQL Fiddle
推荐答案
现在,它们将以逗号分隔.但是,我希望返回特定行的所有列.
Now, they are coming comma separated. But, I am expecting to return all columns of a particular row.
您可以使用CSV
ids结果,例如:
You could use CSV
ids result like:
SELECT *
FROM tblreport
WHERE FIND_IN_SET(ID,(SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(ID SEPARATOR ',')
FROM tblreport WHERE ParentID IN (@pv)) AS lv FROM tblreport
JOIN (SELECT @pv:=1)tmp
WHERE ParentID IN (@pv)) a));
DBFiddle Demo
ID Name ParentID
2 2nd 1
3 3rd 1
4 4th 2
如果您还需要原始行,则可以使用UNION ALL
:
If you need also original row you could use UNION ALL
:
SET @var = 1;
SELECT *
FROM tblreport
WHERE FIND_IN_SET(ID,(SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(ID SEPARATOR ',')
FROM tblreport WHERE ParentID IN (@pv)) AS lv FROM tblreport
JOIN (SELECT @pv:=@var)tmp
WHERE ParentID IN (@pv)) a))
UNION ALL
SELECT *
FROM tblReport
WHERE ID = @var
ORDER BY ID;
DBFiddle Demo2
这篇关于尝试返回父记录的子记录的所有列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!