根据匹配的值分配唯一组ID

本文介绍了根据匹配的值分配唯一组ID的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 需要根据同一个表中多个列值的组合分配唯一的组ID。值有时是唯一的。 任何形式的SQL，SSIS，DQS都适用于以下请求。尝试DQS但未到达目的地。表有大约2百万行。 例如first_name，last_name和Email_address值匹配但PhoneNumber不同。一切都匹配，但customerId可能会有所不同。基本上比较一行与另一行&证明客户是相同的并且分配唯一ID。输出在最后一行。 谢谢 数据： customerid EmailAddress First_Name Middle_Name姓氏地址电话最终组ID 100 [email protected] JNE PIN 1 Wills PL Australia NJ 90210 1111111111 A123 206 JNE PIN 1 Wills PL Australia NJ 90210 A123 206 [email protected] JNE PIN 1 Wills PL Australia NJ 90210 1111111111 A123 206 [email protected] JNE PIN 1 Wills PLACE Australia NJ 90210 1111111111 A123 206 j @ A。 cooom JNE PIN 22 OX ST EW #A HURAT MA 30000 1111111111 A123 206 [email protected] JNE PIN 22 OX ST EW UNIT A HURAT MA 30000 1111111111 A123 206 [email protected] JNE PIN 22 OX ST EW UNIT A HURAT MA 30000 2222222222 A123 206 [email protected] JNE PIN C / OI最佳35个成功的背包CT 12345 1111111111 A123 206 [email protected] JNE PIN C / OI最好的35个最好的倒带CT 12345 2222222222 A123 712 [email protected]和PIN 1 MOUNT Everest Frace GA 54358 2222222222 A123 712 [email protected] JNE PIN 1 Wills PL Australia NJ 90210 1111111111 A123 791 [email protected] MH JSN 63 AL RD VALLEY NM 10001 8888888888 A124 791 [email protected] MAR JSN 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124 791 [email protected] MAR H JSN 63 AL RD VALLEY NM 10001 8888888888 A124 799 [email protected] MH JSN 63 AL RD VALLEY NM 10001 8888888888 A124 799 [email protected] MAR H JSN 63 AL RD VALLEY NM 10001 8888888888 A124 799 [email protected] SEA JSN 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124 805 [email protected] MH JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124 805 [email protected] MAR H JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124 805 [email protected] VPCH 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124 805 [email protected] VICTOR HKS 21 SUPER HIT CIR BOMBAY HI 11231 8888888888 A124 805 [email protected] VICTOR P CAS 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124 809 [email protected] MH JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124 809 [email protected] MAR STK 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124 809 [email protected] MAR H JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124 815 k @ Y. cooom MH JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124 815 [email protected] MAR H JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124 815 [email protected] MAV JSN 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124 815 [email protected] MAV JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124 解决方案 创建临时表第一列是identity（1,1）插入到该表中，您的select语句在您的select中使用distinct或将唯一列分组。 不确定我是否正确理解了这个问题但是有一个尝试类似 更新 TableName SET GroupId = c.GroupId FROM TableName INNER JOIN （ SELECT b.First_Name， b.Last_Name， b.Email_Address， ROW_NUMBER（） OVER （ ORDER BY b.First_Name， b.Last_Name， b.Email_Address） AS GroupId FROM （ SELECT DISTINCT a.First_Name， a.Last_Name， a.Email_Address FROM TableName a）b）c ON TableName.First_Name = c.First_Name AND TableName.Last_Name = c.Last_Name AND TableName.Email_Address = c.Email_Address; Need to assign a unique group Id based on the combination of multiple columns values from same table. Values are sometimes unique.Any form of SQL, SSIS, DQS will work for below request. Tried DQS but does not reach to destination. Table has about 2+ millions rows.For example first_name, last_name, and Email_address values match but PhoneNumber is different. Everything match but customerId might be different. Basically compare one row with another & justify that customer is same and assign Unique ID. Output is in last row.ThanksData:customerid EmailAddress First_Name Middle_Name LastName Address Phone Final Group ID100 [email protected] JNE PIN 1 Wills PL Australia NJ 90210 1111111111 A123206 JNE PIN 1 Wills PL Australia NJ 90210 A123206 [email protected] JNE PIN 1 Wills PL Australia NJ 90210 1111111111 A123206 [email protected] JNE PIN 1 Wills PLACE Australia NJ 90210 1111111111 A123206 [email protected] JNE PIN 22 OX ST EW #A HURAT MA 30000 1111111111 A123206 [email protected] JNE PIN 22 OX ST EW UNIT A HURAT MA 30000 1111111111 A123206 [email protected] JNE PIN 22 OX ST EW UNIT A HURAT MA 30000 2222222222 A123206 [email protected] JNE PIN C/O I AM THE BEST 35 FABULOUS OUTBACK CT 12345 1111111111 A123206 [email protected] JNE PIN C/O I AM THE BEST 35 FABULOUS OUTBACK CT 12345 2222222222 A123712 [email protected] And PIN 1 MOUNT Everest Frace GA 54358 2222222222 A123712 [email protected] JNE PIN 1 Wills PL Australia NJ 90210 1111111111 A123791 [email protected] M H JSN 63 AL RD VALLEY NM 10001 8888888888 A124791 [email protected] MAR JSN 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124791 [email protected] MAR H JSN 63 AL RD VALLEY NM 10001 8888888888 A124799 [email protected] M H JSN 63 AL RD VALLEY NM 10001 8888888888 A124799 [email protected] MAR H JSN 63 AL RD VALLEY NM 10001 8888888888 A124799 [email protected] SEA JSN 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124805 [email protected] M H JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124805 [email protected] MAR H JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124805 [email protected] V P C H 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124805 [email protected] VICTOR HKS 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124805 [email protected] VICTOR P CAS 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124809 [email protected] M H JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124809 [email protected] MAR STK 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124809 [email protected] MAR H JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124815 [email protected] M H JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124815 [email protected] MAR H JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124815 [email protected] MAV JSN 21 SUPERHIT CIR BOMBAY HI 11231 8888888888 A124815 [email protected] MAV JSN 27 W WOOD Mtn VALLEY NM 10000 8888888888 A124 解决方案 Create a temp table with the first column being identity(1,1) insert into that table with your select statement using distinct in your select or group the unique columns.Not sure if I understand the question correctly but have a try with something likeUPDATE TableNameSET GroupId = c.GroupIdFROM TableName INNER JOIN (SELECT b.First_Name, b.Last_Name, b.Email_Address, ROW_NUMBER() OVER (ORDER BY b.First_Name, b.Last_Name, b.Email_Address) AS GroupId FROM (SELECT DISTINCT a.First_Name, a.Last_Name, a.Email_Address FROM TableName a) b) c ON TableName.First_Name= c.First_Name AND TableName.Last_Name= c.Last_Name AND TableName.Email_Address= c.Email_Address; 这篇关于根据匹配的值分配唯一组ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！