向表中添加数字id
向表中添加数字id
提问于 2012-05-02 20:04:01
SELECT top 5
day_description,
count(numberofdays) as number
FROM mytable
where department = 'departmentname'
group by day_description我的select返回如下内容：
day description number
holiday 32
working day 212
training day 5
meeting day 26
admin day 66 有没有办法添加一个新的列，其中它表示1-5，1是最高值行，5是最低值行。看起来像这样：
day description number top5ID
holiday 32 3
working day 212 1
training day 5 5
meeting day 26 4
admin day 66 2回答 2
Stack Overflow用户
回答已采纳
发布于 2012-05-02 20:09:48
可能是这样的：
;WITH CTE
AS
(
SELECT top 5
day_description,
count(numberofdays) as number
FROM mytable
where department = 'departmentname'
group by day_description
)
SELECT
ROW_NUMBER() OVER(ORDER BY CTE.number DESC) AS Top5ID,
CTE.*
FROM
CTE或者不使用CTE：
SELECT
ROW_NUMBER() OVER(ORDER BY t.number DESC) AS Top5ID,
t.*
FROM
(
SELECT top 5
day_description,
count(numberofdays) as number
FROM mytable
where department = 'departmentname'
group by day_description
) AS tStack Overflow用户
发布于 2012-05-02 20:49:11
因此，您可以使用它：
SELECT TOP 5 day_description, COUNT(numberofdays) AS number,
ROW_NUMBER() OVER(ORDER BY COUNT(numberofdays) DESC) AS Top5ID
FROM mytable
WHERE department = 'departmentname'
GROUP BY day_description
ORDER BY COUNT(numberofdays) DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/10413362
复制相关文章
相似问题
腾讯云开发者
