我希望能够按照varchar列进行分组,并查找出现最多的外键值。问题在于,可以将多个fiModel分配给相同的TAC(名为SSN_Number的15位值的前8位数字)。
以下是一个简化的模型和查询,包含示例数据:
以下是一个简化的模型和查询,包含示例数据:
create table #data(
SSN_Number varchar(15),
fiModel int
)
insert into #data
SELECT '351806038155151',451 UNION ALL SELECT '353797028764243',232 UNION ALL SELECT '353797028764243',438 UNION ALL SELECT '353797028764243',438 UNION ALL SELECT '353797028764243',447 UNION ALL SELECT '358372015611578',318 UNION ALL SELECT '352045039834626',279 UNION ALL SELECT '352045031234567',279 UNION ALL SELECT '351806035647381',451 UNION ALL SELECT '352045037654321',207
--- following query returns all records(10)
select * from #data Order By SSN_Number
--- following query gives the distinct TAC's+fiModel, but TACs can repeat (9)
select substring(ssn_number,1,8)as TAC,fiModel,count(*) from #data
group by substring(ssn_number,1,8),fiModel
Order By substring(ssn_number,1,8),fiModel
--- following query gives the correct(distinct) TAC's (4),
--- but i need the fiModel that occurs most often with the assigned TAC
--- if the number is the same, it doesn't matter what to take
select substring(ssn_number,1,8)as TAC,count(*) from #data
group by substring(ssn_number,1,8)
Order By substring(ssn_number,1,8)
drop table #data
所以这就是期望的结果:
TAC fiModel
35180603 451
35204503 279
35379702 438
35837201 318