SQL Teradata 查询

Question

我有一个表 abc，它有很多记录，列 col1、col2、col3，

dept         | name |  marks |

science         abc      50
science         cvv      21
science         cvv      22  
maths           def      60
maths           abc      21
maths           def      62
maths           ddd      90

我需要按部门和姓名排序，排名为 ddd-1, cvv - 2, abc -3, else 4 然后需要找出个人的最大分数。预期结果是

dept         | name |  marks |

science         cvv      22
science         abc      50
maths           ddd      90
maths           abc      21
maths           def      62

.我该怎么做。？

Answer 1

SELECT
  dept,
  name,
  MAX(marks)   AS mark
FROM
  yourTable
GROUP BY
  dept,
  name
ORDER BY
  CASE WHEN name = 'ddd' THEN 1
            name = 'cvv' THEN 2
            name = 'abc' THEN 3
                         ELSE 4 END

或者，最好有另一个包含排序顺序的表格。

SELECT
  yourTable.dept,
  yourTable.name,
  MAX(yourTable.marks)   AS mark
FROM
  yourTable
INNER JOIN
  anotherTable
    ON yourTable.name = anotherTable.name
GROUP BY
  yourTable.dept,
  youtTable.name
ORDER BY
  anotherTable.sortingOrder