gpt4 book ai didi

sql - 分组依据并获得最常出现的独特值

转载 作者:行者123 更新时间:2023-12-04 13:43:28 25 4
gpt4 key购买 nike

我想按 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

最佳答案

这应该可以解决问题(CTE 来救援!):

;with cte as (
select substring(ssn_number,1,8) as TAC, fiModel, ROW_NUMBER() OVER (PARTITION BY substring(ssn_number,1,8) ORDER BY count(*) desc) as row
from #data
group by substring(ssn_number,1,8),fiModel
)
select TAC, fiModel
from cte
where row = 1

作为子查询:
Select TAC,fiModel
from(
Select substring(ssn_number,1,8)as TAC, fiModel
,ROW_NUMBER() OVER (PARTITION BY substring(ssn_number,1,8) ORDER BY count(*) desc) as row
from #data
group by substring(ssn_number,1,8),fiModel
)as data
where row=1

关于sql - 分组依据并获得最常出现的独特值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8428468/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com