I have a table in which spouse and children are linked to main user.
我有一个表，其中的配偶和孩子被连接到主要用户。
+----------------+-------------------+----------+------------------------------+------------------+
| Id | User_ID | Rel_Type | Applno | RelationWith |
+----------------+-------------------+----------+------------------------------+------------------+
| 1234756 | aambu ghosha | self | 201708180921 | aambu ghosha |
| 1235146 | parvati ghosha | spouse | NULL | aambu ghosha |
| 1235147 | ananta ghosha | Children | 201708180921 | aambu ghosha |
| 500787 | anant01011975 | self | 20170811171403999L | anant01011975 |
| 501626 | chandu1988 | children | NULL | anant01011975 |
| 1706064 | atmaram sutar | self | 20170821094537517L | atmaram sutar |
| 1706494 | venu sutar | spouse | 20170821094537517L | atmaram sutar |
In the above example, the main applicant "aambu ghosha" is "self" (main applicant). The spouse and Children (parvati and ananta) needs to be considered as a single applicant.
在上面的例子中，主要申请人“aambu ghosha”是“self”(主要申请人)。配偶和子女(帕瓦蒂和阿纳塔)需要被视为单一申请人。
aambu ghosha 3
anant01011975 2
atmaram sutar 2
The count of main applicants should include their family members. The expected result is shown above. I guess this can achieved using self join, but I am not sure how many children are linked to main applicant. What is best approach to find the count?
主要申请人的人数应包括他们的家庭成员。预期结果如下所示。我想这可以通过self join来实现，但是我不确定有多少孩子和主要的申请者有联系。找出计数的最佳方法是什么?
http://sqlfiddle.com/#!9/30945c/2/0
http://sqlfiddle.com/ ! 9/30945c / 2/0
update:
更新:
How do I self - join and update the application number that is linked to main applicant? For e.g. second record NULL value should be changed to 201708180921.
如何自连接及更新与主要申请人有关的申请编号?例如，第二个记录空值应改为201708180921。
1 个解决方案
#1
4
Assuming you have just one level of children, this will work
假设你只有一个层次的孩子，这将会起作用
SELECT userid, count(*)
FROM tab p
JOIN tab ch ON p.user_id = ch.RelationWith
WHERE p.user_id = p.RelationWith
GROUP BY userid
Actually, even simpler query produce the result requested by you
实际上，甚至更简单的查询也生成您所请求的结果
SELECT RelationWith, count(*)
FROM tab
GROUP BY RelationWith
#1
4
Assuming you have just one level of children, this will work
假设你只有一个层次的孩子，这将会起作用
SELECT userid, count(*)
FROM tab p
JOIN tab ch ON p.user_id = ch.RelationWith
WHERE p.user_id = p.RelationWith
GROUP BY userid
Actually, even simpler query produce the result requested by you
实际上，甚至更简单的查询也生成您所请求的结果
SELECT RelationWith, count(*)
FROM tab
GROUP BY RelationWith