mps777 发表于 2013-4-30 09:35
6、非子查询的各科排名
昨天香帅版主说过,各科排名可以用join来替代子查询得到排名;小麦想了一天,终于想 ...
各科排名:
- SELECT A.姓名,A.学科,A.成绩,count(B.成绩)+1 AS 排名 FROM
- (SELECT 姓名,机械 AS 成绩,'机械' AS 学科 FROM [期中$]
- UNION ALL
- SELECT 姓名,设计,'设计' FROM [期中$]
- UNION ALL
- SELECT 姓名,力学,'力学' FROM [期中$])A
- LEFT JOIN
- (SELECT 姓名,机械 AS 成绩,'机械' AS 学科 FROM [期中$]
- UNION ALL
- SELECT 姓名,设计,'设计' FROM [期中$]
- UNION ALL
- SELECT 姓名,力学,'力学' FROM [期中$])B
- ON A.学科=B.学科 AND A.成绩<B.成绩
- GROUP BY A.姓名,A.学科,A.成绩
复制代码 如需转置,可以这样:
- TRANSFORM MIN(排名)
- SELECT 姓名,学科,成绩 FROM
- (SELECT A.姓名,A.学科,A.成绩,count(B.成绩)+1 AS 排名 FROM
- (SELECT 姓名,机械 AS 成绩,'机械' AS 学科 FROM [期中$]
- UNION ALL
- SELECT 姓名,设计,'设计' FROM [期中$]
- UNION ALL
- SELECT 姓名,力学,'力学' FROM [期中$])A
- LEFT JOIN
- (SELECT 姓名,机械 AS 成绩,'机械' AS 学科 FROM [期中$]
- UNION ALL
- SELECT 姓名,设计,'设计' FROM [期中$]
- UNION ALL
- SELECT 姓名,力学,'力学' FROM [期中$])B
- ON A.学科=B.学科 AND A.成绩<B.成绩
- GROUP BY A.姓名,A.学科,A.成绩)
- GROUP BY 姓名,学科,成绩
- PIVOT 学科
复制代码
LEFT JOIN返回的是左表所有记录,和右表符合关联条件的记录。于是,在学科相同的条件下,根据A.成绩<B.成绩条件对成绩进行比较,那么,A表的最大值不可能在B表中存在比他大的值(因为两表的数据源是相同的),故其对应B表的记录为NULL,然后,使用COUNT(字段)进行统计,得出是0,然后用0+1,得出最大值的排名为1,也就是从1开始排名了。
如果A.成绩<B.成绩改为A.成绩<=B.成绩,那么A表的最大值在B表中必然存在,若是最大值有重复(比如同一学科存在两个100分或者更多),那么,使用COUNT进行计数时,其值必然>1,也就是说在最大值出现重复时,排名不是从1开始。
也可以这样:
- SELECT 姓名,学科,成绩,SUM(机械) AS 机械排名,SUM(设计) AS 设计排名,SUM(力学) AS 力学排名 FROM
- (SELECT A.姓名,'机械' AS 学科,A.机械 AS 成绩,COUNT(B.机械)+1 AS 机械,0 AS 设计,0 AS 力学 FROM [期中$]A LEFT JOIN [期中$]B ON A.机械<B.机械 GROUP BY A.姓名,A.机械
- UNION ALL
- SELECT A.姓名,'设计',A.设计,0,COUNT(B.设计)+1,0 FROM [期中$]A LEFT JOIN [期中$]B ON A.设计<B.设计 GROUP BY A.姓名,A.设计
- UNION ALL
- SELECT A.姓名,'力学',A.力学,0,0,COUNT(B.力学)+1 FROM [期中$]A LEFT JOIN [期中$]B ON A.力学<B.力学 GROUP BY A.姓名,A.力学)
- GROUP BY 姓名,学科,成绩
复制代码
|