如何在SQL Server中添加group_id [英] How to add group_id in SQL server

问题描述

I want to create the group_id for all the records in the table in sql server. group_id should be created based on:
 1. if ssn is matched select all matched ssn in one group like ssn - 1234 has 10 rec so group it as g_1234 or ssn-8789 has 10 rec g_8789
 2. if name is matched select all matched name in one group as g_name
 3. if name+ dob is matched then selected all match case in one group as g_name_dob
 4. if two or more records have different ssn but name is matched with any grouped rec then it will go to the g_name group as name is matched
 5. likely if two or more records have different ssn but name and dob both matched with any grouped record then it will go the same g_name_dob group
 6. in case ssn is null or empty, but name is matched with any of the existing grouped record, then that group_id will be updated for this record.

please let me know how best I can achieve it. 

我的尝试：



使用while循环。不认为最好的做法

What I have tried:

using while loop. not think best approach to do

推荐答案

使用 GROUP BY [ ^ ]

CASE [ ^ ]也可能有用。

不要使用循环，解决方案是基于集合，而非程序性

Use GROUP BY[^]
CASE[^] might also be useful.
Don't use a loop, the solution is set - based, not procedural

这篇关于如何在SQL Server中添加group_id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！