- 论坛徽章:
- 0
|
表DEPARSCORE
DEPAR | NAME | SCORE | A | A1 | 66 | A | A2 | 80 | A | A3 | 55 | B | B1 | 36 | B | B2 | 78 | B | B3 | 74 | C | C1 | 57 | C | C2 | 92 |
通过这个帖子https://www.cnblogs.com/yes-you-can/p/4801273.html,我写了一个SQL语句,查找每个部门第一名与第二名的分数之差
- SELECT cc.*, dd.SCORE2, cc.SCORE1-dd.SCORE2 as '分差' FROM
- (
- SELECT DEPAR, MAX(SCORE) AS SCORE1, NAME
- FROM DEPARSCORE
- GROUP BY DEPAR
- ) AS cc
- LEFT JOIN
- (
- SELECT MAX(SCORE) AS SCORE2,
- DEPAR
- FROM (
- SELECT aa.*,
- bb.DSECOND AS tempcolum
- FROM (
- SELECT SCORE,
- DEPAR
- FROM DEPARSCORE
- )
- AS aa
- LEFT JOIN
- (
- SELECT MAX(SCORE) AS DSECOND,
- DEPAR
- FROM DEPARSCORE
- GROUP BY depar
- )
- AS bb ON aa.SCORE = bb.DSECOND AND
- aa.DEPAR = bb.DEPAR
- )
- WHERE tempcolum IS NULL
- GROUP BY DEPAR
- ) AS dd ON cc.DEPAR = dd.DEPAR
复制代码
问题1:我写的这个是不是太啰嗦了?能怎么优化吗,结果倒是正确
问题2:我用的DBIx::Class模块,简单的没问题,遇到这种复杂的SQL查询,就不知道怎么转化成它需要的语句了。我看到文档里面有join, sub query之类的例子,跟这个都不太符合,求指点谢谢!
|
|