如何排序SQL查询(How to rank sql query)
我有一组我想要排名的结果：
我不知道要分区的是什么。
查询：
SELECT DISTINCT TFormSectionID AS FormSectionID, TFSSortOrder AS SectionSortOrder, TSectionItemID AS SectionItemID, TrendType FROM Report.TrendData WHERE (ProgramID = 1) AND (TrendType > 0) AND tformid = 34 AND TFormSectionID = 12结果：
FormSectionID SectionSortOrder SectionItemID TrendType 12 7 90 1 12 7 91 1 12 7 154 1 12 7 528 1 12 9 154 1 12 9 528 1我希望按部分排序顺序9的结果排名为2，部分排序顺序7排名为1
I have a set of results that I want to rank:
I am not sure what to partition by.
Query:
SELECT DISTINCT TFormSectionID AS FormSectionID, TFSSortOrder AS SectionSortOrder, TSectionItemID AS SectionItemID, TrendType FROM Report.TrendData WHERE (ProgramID = 1) AND (TrendType > 0) AND tformid = 34 AND TFormSectionID = 12Results:
FormSectionID SectionSortOrder SectionItemID TrendType 12 7 90 1 12 7 91 1 12 7 154 1 12 7 528 1 12 9 154 1 12 9 528 1I want the results with section sort order 9 to be ranked as 2 and the section sort order 7 to be ranked as 1
最满意答案
看起来你可以使用DENSE_RANK获得你想要的东西：
SELECT DISTINCT TFormSectionID AS FormSectionID, TFSSortOrder AS SectionSortOrder, TSectionItemID AS SectionItemID, TrendType, DENSE_RANK() OVER (ORDER BY SectionSortOrder) AS rn FROM Report.TrendData WHERE (ProgramID = 1) AND (TrendType > 0) AND tformid = 34 and TFormSectionID = 12It seems like you can get what you want using DENSE_RANK:
SELECT DISTINCT TFormSectionID AS FormSectionID, TFSSortOrder AS SectionSortOrder, TSectionItemID AS SectionItemID, TrendType, DENSE_RANK() OVER (ORDER BY SectionSortOrder) AS rn FROM Report.TrendData WHERE (ProgramID = 1) AND (TrendType > 0) AND tformid = 34 and TFormSectionID = 12如何排序SQL查询(How to rank sql query)我有一组我想要排名的结果：
我不知道要分区的是什么。
查询：
SELECT DISTINCT TFormSectionID AS FormSectionID, TFSSortOrder AS SectionSortOrder, TSectionItemID AS SectionItemID, TrendType FROM Report.TrendData WHERE (ProgramID = 1) AND (TrendType > 0) AND tformid = 34 AND TFormSectionID = 12结果：
FormSectionID SectionSortOrder SectionItemID TrendType 12 7 90 1 12 7 91 1 12 7 154 1 12 7 528 1 12 9 154 1 12 9 528 1我希望按部分排序顺序9的结果排名为2，部分排序顺序7排名为1
I have a set of results that I want to rank:
I am not sure what to partition by.
Query:
SELECT DISTINCT TFormSectionID AS FormSectionID, TFSSortOrder AS SectionSortOrder, TSectionItemID AS SectionItemID, TrendType FROM Report.TrendData WHERE (ProgramID = 1) AND (TrendType > 0) AND tformid = 34 AND TFormSectionID = 12Results:
FormSectionID SectionSortOrder SectionItemID TrendType 12 7 90 1 12 7 91 1 12 7 154 1 12 7 528 1 12 9 154 1 12 9 528 1I want the results with section sort order 9 to be ranked as 2 and the section sort order 7 to be ranked as 1
最满意答案
看起来你可以使用DENSE_RANK获得你想要的东西：
SELECT DISTINCT TFormSectionID AS FormSectionID, TFSSortOrder AS SectionSortOrder, TSectionItemID AS SectionItemID, TrendType, DENSE_RANK() OVER (ORDER BY SectionSortOrder) AS rn FROM Report.TrendData WHERE (ProgramID = 1) AND (TrendType > 0) AND tformid = 34 and TFormSectionID = 12It seems like you can get what you want using DENSE_RANK:
SELECT DISTINCT TFormSectionID AS FormSectionID, TFSSortOrder AS SectionSortOrder, TSectionItemID AS SectionItemID, TrendType, DENSE_RANK() OVER (ORDER BY SectionSortOrder) AS rn FROM Report.TrendData WHERE (ProgramID = 1) AND (TrendType > 0) AND tformid = 34 and TFormSectionID = 12
发布评论