抑制结果集中的重复值

需求：查询的结果集中，某一列存在多个重复的值，需要这个值只显示一次

案例：从表temp中，查部门和员工姓名，要求不通部门值只要一次，这时候可以使用lag可以轻松完成

SELECT decode(lag(t.department) over(ORDER BY department),
              department,
              NULL,
              department) department,
       t.worker_name
  FROM test2 t;

SELECT CASE
         WHEN lag(t.department) over(ORDER BY department,worker_name) = t.department THEN
          NULL
         ELSE
          department
       END department,
       t.worker_name
  FROM test2 t
	ORDER BY t.department,worker_name;

需要注意的是，在最后的order by字句中，department加了前缀，如果不加前缀，我们看下结果：

SELECT CASE
         WHEN lag(t.department) over(ORDER BY department,worker_name) = t.department THEN
          NULL
         ELSE
          department
       END department,
       t.worker_name
  FROM test2 t
	ORDER BY department,worker_name;