我有一个表,可能有几十万行。每一行代表一个给定的申请人在一个人口普查区域内申请资助。如果我只有几百行,我可以使用以下命令为每个应用程序分配一个排名:
SELECT art1.CFA_Plus, art1.Census_Block_ID,
(SELECT count(*) + 1
FROM AppReferenceTable art2
WHERE art2.State_Cost_Per_Unit < art1.State_Cost_Per_Unit AND
art2.Census_Block_ID = art1.Census_Block_ID) AS Rank_In_Block
FROM AppReferenceTable AS art1;
这可以很好地按该普查区域内的单位成本对每个应用程序进行排名。但它在我的测试表上阻塞了,它有大约6万行。有没有更好的办法?或者,我做错了什么?
谢谢!
我明白了——把表和它自己连接起来就可以了。
SELECT art1.Census_Block_ID, art1.CFA_Plus, art1.State_Cost_Per_unit, COUNT(*) As Rank
FROM appreferencetable as art1
LEFT JOIN appreferencetable as art2
ON art1.Census_Block_ID = art2.Census_Block_ID AND art2.State_Cost_Per_Unit <= art1.State_Cost_Per_unit
GROUP BY art1.Census_Block_ID, art1.CFA_Plus, art1.State_cost_per_unit
听起来你明白了,但为了防止其他人遇到这种情况,我做了一个排名系统,在必要时指定平局决胜者。我还做了一个视频来教你如何从头开始:
Microsoft Access -如何在查询中创建数据排序字段(以及制作平局打破系统)
SELECT sub.employeeID, sub.dob, sub.rank
FROM (SELECT e1.dob, e1.ID, e1.employeeID,
(SELECT COUNT(*)
FROM employees AS e2
WHERE (e2.dob < e1.dob) OR
(e2.dob = e1.dob AND e2.ID < e1.ID)
) + 1 AS rank
FROM employees AS e1
) AS sub
ORDER BY sub.rank;