时间戳

首页 > 解决方案 > 在 oracle 中取一个大集合的第一个唯一值

sql - 在 oracle 中取一个大集合的第一个唯一值

问题描述

我有一个很大的集合，但我只想获取特定列（c.id）的每组的第一个唯一值。

到目前为止，我已经有了这个，它可以工作.. 但是我需要为每个不同的 c.ID 获取 1 条记录，因为现在我得到了几个 c.ID，我只想忽略并且只有一个 UNIQUE 或 FIRST 的 c.ID 值

Select
    s.room,
    s.EXPRESSION,
    c.ID,
    c.COURSE_NAME,
    s.ROOM,
    t.FIRST_NAME,
    t.LAST_NAME,
    std.GRADE_LEVEL,
    CASE s.SCHOOLID
        WHEN 300 THEN 'MiddleSchool'
        WHEN 400 THEN 'UpperSchool'
        WHEN 200 THEN 'LowerSchool'
        WHEN 100 THEN 'ECC'
        ELSE 'miau'
        END AS "School"
  from   sections s
  INNER JOIN COURSES c ON s.COURSE_NUMBER=c.COURSE_NUMBER
  INNER JOIN TEACHERS t ON s.TEACHER=t.ID
  INNER JOIN CC co ON s.ID=co.SECTIONID
  INNER JOIN STUDENTS std ON std.ID = co.STUDENTID
  WHERE s.TERMID IN (2800, 2801, 2803, 2804)
  AND t.LAST_NAME='Dilworth'

谢谢。

标签： sqloraclegreatest-n-per-group

解决方案

with data as (
    Select s.room, s.EXPRESSION, c.ID, c.COURSE_NAME, /* s.ROOM why twice? */,
        t.FIRST_NAME, t.LAST_NAME, std.GRADE_LEVEL,
        CASE s.SCHOOLID
            WHEN 300 THEN 'MiddleSchool'
            WHEN 400 THEN 'UpperSchool'
            WHEN 200 THEN 'LowerSchool'
            WHEN 100 THEN 'ECC'
            ELSE 'miau'
        END AS "School",
        row_number() over (partition by c.ID order by ??) as rn /* which is first? */
    from sections s
        INNER JOIN COURSES c ON s.COURSE_NUMBER = c.COURSE_NUMBER
        INNER JOIN TEACHERS t ON s.TEACHER = t.ID
        INNER JOIN CC co ON s.ID = co.SECTIONID
        INNER JOIN STUDENTS std ON std.ID = co.STUDENTID
    WHERE s.TERMID IN (2800, 2801, 2803, 2804) AND t.LAST_NAME = 'Dilworth'
)
select * from data where rn = 1;

推荐阅读