問題描述

我有一個包含注冊用戶的表，其中我將年份保存為 varchar 值，因為我只需要一年.我想創(chuàng)建帶有年齡的餅圖以顯示哪些用戶更有可能注冊.

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.

下面的查詢?yōu)槲姨峁┝嗽诒碇谐霈F(xiàn)超過 5 次的用戶年齡計數(shù)，以避免出現(xiàn)小結(jié)果.雖然這些小結(jié)果，在擁有 ount(userID)>5"下面我想以其他人的身份出現(xiàn).我應(yīng)該向這個查詢添加什么或可能重新設(shè)計它.我可以創(chuàng)建愚蠢的解決方案，比如獲取出現(xiàn)在初始查詢中的所有年份，然后選擇除那些年份之外的所有年份，但必須有更好、更有創(chuàng)意的方式來編寫此查詢.

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.

所以結(jié)果將是這樣的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

謝謝

推薦答案

這是一種方法(假設(shè) 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

這篇關(guān)于TSQL，幫助查詢用戶年齡的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！