TSQL，帮助查询用户年龄

2021-09-10 00:00:00 sql tsql sql-server

我有一个包含注册用户的表，其中我将年份保存为 varchar 值，因为我只需要一年.我想创建带有年龄的饼图以显示哪些用户更有可能注册.

I have a table with registered users, in which i save year as varchar values simply because i take just an year. I want to create pie chart with ages to show which users are more likely to register.

下面的查询为我提供了在表中出现超过 5 次的用户年龄计数，以避免出现小结果.虽然这些小结果，在拥有 ount(userID)>5"下面我想以其他人的身份出现.我应该向这个查询添加什么或可能重新设计它.我可以创建愚蠢的解决方案，比如获取出现在初始查询中的所有年份，然后选择除那些年份之外的所有年份，但必须有更好、更有创意的方式来编写此查询.

Query below gives me count of user ages which appear more than 5 times in Table to avoid small results. While these small results, below "having ount(userID)>5" i want to appear as others. What should i add to this query or possibly to re-design it. I can create silly solutions like to take all years that appear in initial query and then select all besides those year but there must be better and more creative way of writing this query.

所以结果将是这样的1 10 19902 4 19803 10 其他

So result will be something like that 1 10 1990 2 4 1980 3 10 others

select count(userID) ageCount,userBirthYear from Users
group by userBirthYear
having count(userID)>5
order by count(userID) desc

谢谢

推荐答案

这是一种方法(假设 SQL2005 或更高版本).

Here's one way (assuming SQL2005 or later). 

With Ages As
(
select count(userID) ageCount,userBirthYear 
from Users
group by userBirthYear
)

SELECT ageCount,userBirthYear FROM Ages WHERE ageCount>5
UNION ALL
SELECT sum(ageCount) ,'others' As userBirthYear FROM Ages WHERE ageCount<=5

相关文章