尝试返回父记录的子记录的所有列 [英] 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列

我的示例SQL小提琴

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演示

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

DBFiddle Demo2

这篇关于尝试返回父记录的子记录的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！