SQL查询:需要按计数排序，大多数必须在上面，其余的在后面 [英] SQL Query: Need order by count, most must be on top, the rest follows

问题描述

桌子

JobCode Job1 Job2 Job3 zip
------- ---- ---- ---- ----------
F       F    S    NULL 90030
F       F    S    NULL 90031
F       F    S    NULL 90031
F       F    S    NULL 90034
F       F         NULL 90034
F       F    S    NULL 90034
F       F    S    NULL 90034
F       F         NULL 90034
F       F    S    NULL 90035
F       F         NULL 90035-4640

预期结果:

JobCode Job1 Job2 Job3 zip
------- ---- ---- ---- ----------
F       F    S    NULL 90034
F       F         NULL 90034
F       F    S    NULL 90034
F       F    S    NULL 90034
F       F         NULL 90034
F       F    S    NULL 90031
F       F    S    NULL 90031
F       F    S    NULL 90030
F       F    S    NULL 90035
F       F         NULL 90035-4640

那些带有 SAME Zip 的应该在上面，然后是其余的.ORDER BY Zip 不起作用，因为它按 ZIP 排序，而不是按出现次数

Those with the SAME Zip should be ON top, then the rest follows. ORDER BY Zip does not work because it DOES sort by ZIP, and NOT by number of occurence

使用 SQL Server 08

Using SQL Server 08

推荐答案

SQL Server 2008 using COUNT() OVER

SQL Server 2008 using COUNT() OVER

select *, c = count(1) over (partition by zip)
from tbl
order by c desc;

如果您不需要查看附加列，则可以将 COUNT() OVER 子句移动到 ORDER BY 子句中.

If you don't need to see the additional column, then you can move the COUNT() OVER clause into the ORDER BY clause.

select JobCode, Job1, Job2, Job3, zip
from tbl
order by count(1) over (partition by zip) desc;

这篇关于SQL查询:需要按计数排序，大多数必须在上面，其余的在后面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！