如何对特定行执行分组

5m1hhzi4 于 2021-08-01 发布在 Java

关注(0)|答案(3)|浏览(276)

我正在运行以下查询以获取数据：

SELECT DISTINCT student_id, student_name from class_students where city = 'foobar' and student_id <> 0;

我得到的数据如下：

student_id |              student_name
------------+----------------------------------------
  41990     | James
  48220     | John
  00000     |
  00000     | lkjkj
  00000     | random name
  00000     | somethingelse

我更想找回这些数据：

student_id |              student_name
------------+----------------------------------------
  41990     | James
  48220     | John
  00000     | Name-doesnt-exist

也就是说把所有的 00000 一行中的名称 NA

sql postgresql

来源：https://stackoverflow.com/questions/62670126/how-to-do-a-group-by-on-specific-rows

3条答案

按热度按时间

9rnv2umw1#

SELECT student_id, case when student_id = '00000' then 'N-A' else max(student_name) end
from class_students 
where city = 'foobar' 
  and student_id <> 0
group by student_id

赞(0）回复(0）举报 2021-08-01

icomxhvb2#

如果存在多个名称，则标记它。

select student_id,
    case when count(distinct student_name) > 1 then 'N/A' else min(student_name) end as student_name
from class_students
where city = 'foobar' and student_id <> 0
group by student_id

您还可以从结果中完全消除这些组：

having count(distinct student_name) = 1

赞(0）回复(0）举报 2021-08-01

eoxn13cs3#

你可以试着用 row_number() ```
select student_id,coalesce(student_name,'Name-doesnt-exist') as name
from
(
SELECT student_id, student_name,row_number() over(partition by student_id order by case when student_name is null then 1 else 2 end) as rn
from class_students where city = 'foobar' and student_id <> 0
)A where rn=1

赞(0）回复(0）举报 2021-08-01

我来回答

如何对特定行执行分组

3条答案

相关问题

热门标签

最新问答