ACCESS 交叉查詢 並按年份、積分遞減排序

d19951112

此为一个交叉查询我想让他按年份、积分递减排序可是在排积分时都会有
"ORDER BY 子句中不可有总和函数" 不知道有什么小技巧可以克服
或者是多做几个前置查询??? 可用SQL 或子查询但尽量不要用VB
请高手解惑

咱家是猫

比较方便的做法是在这个交叉查询的基础上再做一个查询

wuxiang_123

1. SELECT 球隊, Year([對賽日期]) AS 對賽日,
   
2. Sum(Left([賽果],InStr([賽果],"-")-1)) AS 總得分,
   
3. Sum(Replace(Right([賽果],Len([賽果])-InStr([賽果],"-")),"*","")) AS 總失分,
   
4. Sum(Left([賽果],InStr([賽果],"-")-1)-Replace(Right([賽果],Len([賽果])-InStr([賽果],"-")),"*","")) AS 差,
   
5. Sum(IIf([負/勝]="勝",3,IIf([負/勝]="和",1,0))) AS 積分,
   
6. SUM(IIF([負/勝]='勝',1)) AS 勝,
   
7. SUM(IIF([負/勝]='和',1)) AS 和,
   
8. SUM(IIF([負/勝]='負',1)) AS 負
   
9. FROM 對戰成績
   
10. GROUP BY 球隊, Year([對賽日期])
    
11. ORDER BY 6 DESC

复制代码