mysql按id分组_MySQL生成按父ID分组的实时计数器

受养 H2>

ParentID | Type | First | Last | DOB

--------------------------------------------------

1 | Spouse | Jane | Doe | 1981-02-01

1 | Child | Mike | Doe | 1999-08-01

1 | Child | Zoe | Doe | 2002-04-01

2 | Spouse | Sarah | Mack | 1964-01-01

2 | Child | Andrew | Mack | 1997-05-01我想建立一个查询(理想情况下单个调用连接返回以下内容：

表结果 h2>

First | Last | Type | DOB | Address

----------------------------------------------------------------

John | Doe | Parent | 1980-01-01 | 123 Street

Jane | Doe | Spouse | 1981-02-01 | 123 Street

Mike | Doe | Child | 1999-08-01 | 123 street

Zoe | Doe | Child | 2002-04-01 | 123 Street

Ryan | Mack | Parent | 1974-12-12 | 444 Place

Sarah | Mack | Spouse | 1964-01-01 | 444 Place

Andrew | Mack | Child | 1997-05-01 | 444 Place我可以通过以下查询在另一个SO用户的帮助下构建上表：

SELECT t.First, t.Last, t.Type, t.DOB, t.Address

FROM (SELECT ID, First, Last, 'Parent' as Type, DOB, Address, 1 as SortKey

FROM Parent

UNION ALL

SELECT p.ID, d.First, d.Last, d.Type, d.DOB, p.Address,

CASE WHEN d.Type = 'Spouse' THEN 2 ELSE 3 END as SortKey

FROM Dependents d

INNER JOIN Parent p

ON d.ParentID = p.ID) t

ORDER BY t.ID, t.SortKey我试图解决一个(可能)更大的问题，因为我必须根据几个参数来计算特定成员ID的每条记录。

父级将永远是1，

配偶总是2(如果他们存在)

子元素将从3开始并继续，直到没有更多(如果他们存在)。

我的最终表格如下所示：

First | Last | Type | DOB | Address | Count

---------------------------------------------------------------------------

John | Doe | Parent | 1980-01-01 | 123 Street | 1

Jane | Doe | Spouse | 1981-02-01 | 123 Street | 2

Mike | Doe | Child | 1999-08-01 | 123 street | 3

Zoe | Doe | Child | 2002-04-01 | 123 Street | 4

Ryan | Mack | Parent | 1974-12-12 | 444 Place | 1

Sarah | Mack | Spouse | 1964-01-01 | 444 Place | 2

Andrew | Mack | Child | 1997-05-01 | 444 Place | 3我知道我可以在呼叫顶部设置一个用户变量，并说：

SET @counter：= 0;

然后

@counter：= @ counter + 1作为柜台，

但是这只会继续计数，而不是通过ID查询。

有任何想法吗？