基于server中年龄组的最高计数数
我有两张桌子:技术人员和成员。成员只能拥有一项技术(他/她最喜欢的技术项目)。
techs: techid,tname
members: memberid,age,techcode样本数据现在(用于技术人员)：
techid tname
1 Mobile
2 XBox成员：
memberid age techcode
1 8 1
2 18 1
3 11 2
4 42 1它还在继续……
现在，第一个令人感兴趣的报告/查询是获取每个年龄组每个技术项目的总受欢迎程度。因此，下面的查询做得很好。
select tname, '0-10' = count(case when age<=10 then 1 end)
,'11-20' = count(case when age BETWEEN 11 AND 20 then 1 end)
,'21-30' = count(case when age BETWEEN 21 AND 30 then 1 end)
,'31-40' = count(case when age BETWEEN 31 AND 40 then 1 end)
,'41-50' = count(case when age BETWEEN 41 AND 50 then 1 end)
,'51-60' = count(case when age BETWEEN 51 AND 60 then 1 end)
,'61-70' = count(case when age BETWEEN 61 AND 70 then 1 end)
,'71-80' = count(case when age BETWEEN 71 AND 80 then 1 end)
,'81-90' = count(case when age BETWEEN 81 AND 90 then 1 end)
,'91-100' = count(case when age BETWEEN 91 AND 100 then 1 end)
,'100+' = count(case when age >100 then 1 end)
from members,techs WHERE techcode=techid GROUP BY tname我想要的下一个报告是按年龄组列出最受欢迎的项目。例如，当前的输出是：
tname 0-10 11-20
Mobile 7 1
XBox 4 20我想要的下一个输出是移动最受欢迎的0-10，XBox在11-20年前很受欢迎。我现在想要的输出大致是：
Age Highest Tech
0-10 7 Mobile
11-20 20 Xbox我现在没有真正的疑问。我试图对每个年龄范围执行MAX()，但是失败了，因为聚合不能超过聚合。我有点不知所措如何绕过它，所以没有真正的查询尝试显示。
回答 3
Stack Overflow用户
发布于 2017-03-20 15:31:08
这是使用带有公共表表达式的row_number()的另一种方法，但也使用带有表值构造函数的公共表表达式而不是case表达式。
;with AgeGroup as (
select *
from (values
(0 ,10 ,'0-10')
, (11,20,'11-20')
, (21,30,'21-30')
, (31,40,'31-40')
, (41,50,'41-50')
, (51,60,'51-60')
, (61,70,'61-70')
, (71,80,'71-80')
, (81,90,'81-90')
, (91,100,'91-100')
, (100,null,'100+')
) as t(ageMin,ageMax,AgeGroup)
)
, cte as (
select
AgeGroup
, Highest = count(*)
, Tech = tname
, rn = row_number() over (
partition by ageGroup
order by count(*) desc
)
from AgeGroup ag
inner join members m
on m.age >= ag.ageMin
and m.age <= ag.ageMax
inner join techs t
on m.techcode = t.techid
group by AgeGroup, tname
)
select ageGroup, Highest, Tech
from cte
where rn = 1rextester演示：http://rextester.com/KMASZX6347
如果要显示没有数据的年龄组，可以将inner joins更改为left joins。
Stack Overflow用户
发布于 2017-03-20 15:13:51
您需要同时按两个维度(行而不是列)进行聚合。然后使用row_number()获取最常用的值：
select ta.*
from (select tname, agegrp, count(*) as cnt,
row_number() over (partition by agegrp order by count(*) desc) as seqnum
from members m join
techs t
on m.techcode = t.techid cross join
(values (case when age <= 10 then '0-10'
when age <= 20 then '11-20'
. . .
end)
) v(agegrp)
group by tname, agegrp
) ta
where seqnum = 1;备注：
- 不要在
FROM子句中使用逗号。始终使用正确、显式的JOIN语法。 cross apply为为表达式分配别名提供了一种方便的方法。- 您想要得到的值称为
mode。
Stack Overflow用户
发布于 2017-03-20 15:57:47
使用RANK() OVER和UNION可以获得您想要的结果：
SELECT TOP 1 Age, Total, RANK() OVER (ORDER BY Total DESC) AS RankByTotal, Tech
FROM(
SELECT '0-10' Age,
sum(A.[0-10]) Total
,Tech
FROM(
SELECT tname, Tech
,'0-10' = COUNT(case when age between 0 and 10 then 1 end)
FROM members m
LEFT JOIN techs t on techcode= techid
Group by tname, Tech) A
Group by Tech ) B
UNION ALL
SELECT TOP 1 Age, Total, RANK() OVER (ORDER BY Total DESC) AS RankByTotal, Tech
FROM(
SELECT '11-20' Age,
sum(A.[11-20]) Total
,Tech
FROM(
SELECT tname, Tech
,'11-20' = COUNT(case when age between 11 and 20 then 1 end)
FROM members m
LEFT JOIN techs t on techcode= techid
Group by tname, Tech) A
Group by Tech) B这种方法意味着每个年龄组都有一个UNION，这可能不是一个理想的方法。然而，它是有效的！
https://stackoverflow.com/questions/42906568
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号